Toad World® Forums

sqlplus script exit

Toad for Oracle
#1

Hello,

I want to start a script in SQLPLUS. If the login of HOST_NAME <> ‘IM333’, the script may continue.

But I do not know how I define a query and the script in the <> case is terminated.

So I ask for help. Many Thanks.

André

REM sqlplus USER/PASSWORD@DATABASE

COL HOSTNAME NEW_VALUE HNAME

COL HOSTNAME_WERT NEW_VALUE HNAMEW

SELECT HOST_NAME AS HOSTNAME,

   DECODE( HOST_NAME, 'IM333', 0, 1 ) AS HOSTNAME_WERT

FROM V$INSTANCE;

??

#2

I don’t think you can do it in the script.

Secondly, do you not want user 'IM333 not to be able to execute the script

or

do you not want host_name ‘IM133’ show on the script.

Please explain what you are trying to do

Gene

On Fri, Aug 11, 2017 at 8:44 AM, rust bounce-rust@toadworld.com wrote:

sqlplus script exit

Thread created by rust
Hello,

I want to start a script in SQLPLUS. If the login of HOST_NAME <> ‘IM333’, the script may continue.

But I do not know how I define a query and the script in the <> case is terminated.

So I ask for help. Many Thanks.

André

REM sqlplus USER/PASSWORD@DATABASE

COL HOSTNAME NEW_VALUE HNAME

COL HOSTNAME_WERT NEW_VALUE HNAMEW

SELECT HOST_NAME AS HOSTNAME,

   DECODE( HOST_NAME, 'IM333', 0, 1 ) AS HOSTNAME_WERT

FROM V$INSTANCE;

??

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle Forum notifications altogether.

Toad for Oracle - Discussion Forum

Flag this post as spam/abuse.

–
Gene L. Bradley Jr.

Systems Analyst

Office of Information Technology

Jackson State University

1400 J R Lynch Street

P.O. Box 17750

Jackson, MS 39217

ph 601.979.1042

fax 601.371.9146

email gbradley@jsums.edu

*In God we trust; all others bring data. * ~W.E. Deming

CONFIDENTIALITY STATEMENT

This electronic transmission is intended for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by telephone (601) 979-1042. Thank you.

#3

If I understand correctly something like this may work.

WHENEVER SQLERROR EXIT FAILURE

DECLARE
v V$INSTANCE.HOST_NAME%TYPE;
BEGIN
SELECT host_name
INTO v
FROM V$INSTANCE
WHERE host_name <> ‘IM333’;
END;
/

– Rest of your script

#4

Hi Gene,

Thanks for answer. The script doesn’t execute for host_name ‘IM133’.

In the script there is some code and this is not for IM133.

André

#5

Hi Michael,

Thanks for answer. In the script there is some code and this is not for IM133.

I get the host_name but I can’t create a query to stop the script. Pls, I need the query.

André

#6

Thanks we are all trying to help here… Perhaps we need more information.

  • How will script be executed? Is someone running it in Toad,SqlPlus* etc.
  • Is script executed automatically via some automated process?
  • If you have multiple instances and you are wanting to prevent a hostname from execiuting the script (actually any user from said hostname) it seems you could restrict this with security vs code in a script.
    Please be clearer, provide more info… if possible provide more of script…

The moderator may move this as it is not really a Toad question but an Oraclie question.

PRODUCT_USER_PROFILE Table

https://docs.oracle.com/cd/B19306_01/server.102/b14357/ch9.htm

On Mon, Aug 14, 2017 at 12:30 AM, rust bounce-rust@toadworld.com wrote:

RE: sqlplus script exit

Reply by rust
Hi Gene,

Thanks for answer. The script doesn’t execute for host_name ‘IM133’.

In the script there is some code and this is not for IM133.

André

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle Forum notifications altogether.

Toad for Oracle - Discussion Forum

Flag this post as spam/abuse.

–
Gene L. Bradley Jr.

Systems Analyst

Office of Information Technology

Jackson State University

1400 J R Lynch Street

P.O. Box 17750

Jackson, MS 39217

ph 601.979.1042

fax 601.371.9146

email gbradley@jsums.edu

*In God we trust; all others bring data. * ~W.E. Deming

CONFIDENTIALITY STATEMENT

This electronic transmission is intended for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by telephone (601) 979-1042. Thank you.

#7

Still not sure if i understand, but would this work?

REVOKE select (column_name)
ON schemaName.table_name
TO IM333;

On Fri, Aug 11, 2017 at 8:44 AM, rust bounce-rust@toadworld.com wrote:

sqlplus script exit

Thread created by rust
Hello,

I want to start a script in SQLPLUS. If the login of HOST_NAME <> ‘IM333’, the script may continue.

But I do not know how I define a query and the script in the <> case is terminated.

So I ask for help. Many Thanks.

André

REM sqlplus USER/PASSWORD@DATABASE

COL HOSTNAME NEW_VALUE HNAME

COL HOSTNAME_WERT NEW_VALUE HNAMEW

SELECT HOST_NAME AS HOSTNAME,

   DECODE( HOST_NAME, 'IM333', 0, 1 ) AS HOSTNAME_WERT

FROM V$INSTANCE;

??

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle Forum notifications altogether.

Toad for Oracle - Discussion Forum

Flag this post as spam/abuse.

–
Gene L. Bradley Jr.

Systems Analyst

Office of Information Technology

Jackson State University

1400 J R Lynch Street

P.O. Box 17750

Jackson, MS 39217

ph 601.979.1042

fax 601.371.9146

email gbradley@jsums.edu

*In God we trust; all others bring data. * ~W.E. Deming

CONFIDENTIALITY STATEMENT

This electronic transmission is intended for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by telephone (601) 979-1042. Thank you.

#8

Hi Gene,

the sctipt will be execute by Toad via SQL*Plus not witch script-runner.

the script will execute sometimes vie automated but normaly manuel.

The script is to abort when the host name is IM133.

I think if host name IM133 I will exit the script. But I doen’t know the correct syntax

André

#9

Hi Gene,

I think revoke is not the right syntax

André

#10

Andre, did you run my script? That script should abort when host_name matches what you wish to exclude. Place some code following my exclusion code and verify that it does or does not execute the code.

#11

Hi Michael,

yes I did but I don’t understand the syntax from this.

That what I search is a compare the host_name from the enviroment whitch the constante ‘IM133’ and break then script if is equal.

…

SELECT HOST_NAME AS HOSTNAME
FROM V$INSTANCE;

…

column_name = host_name ?

schemaname.table_name = V$INSTANCE ?

I’m not confirm in some syntax.

REVOKE select (column_name)

ON schemaName.table_name
TO IM133;

If I start the script an error occours. Sorry.

André

#12

I’m just not following you then, I’m sorry.

For starters, I have a logic error in my script. I have your host name comparison wrong. If you ran what I sent verbatim try it once more correcting the host name to be IM133.

That script I sent selects host name from v$instance only when the host name does not equal the host name to be excluded. When no records are found a no data found exception is raised. The first line of the script, WHENEVER SQLERROR…, instructs SQL*Plus to stop the script when the exception is raised. That script should only proceed beyond the anonymous block when host name does not equal IM133. To me this sounds exactly as you are requesting, but I’m missing something.

Here is the script with the corrected host name.

WHENEVER SQLERROR EXIT FAILURE

DECLARE
v V$INSTANCE.HOST_NAME%TYPE;
BEGIN
SELECT host_name
INTO v
FROM V$INSTANCE
WHERE host_name <> ‘IM133’;
END;
/

– Rest of your script here, this code will only execute when host name does not equal IM133

Disclaimer: I can barely select my own username from dual so this may be the absolute wrong way to do it, but it works on my machine.

#13

Hi Michael

thx for the PL/SQL code.

But the script doesn’t stop after ‘error’. Pls tell me why?

André

Here is the Script:

SET SQLPLUSCOMPATIBILITY 11.2.0

SET PAGESIZE 999
SET TRIMSPOOL ON
SET UNDERLINE OFF
SET LINESIZE 1024

COL HOSTNAME NEW_VALUE HNAME

SELECT HOST_NAME AS HOSTNAME FROM V$INSTANCE;

PROMPT HOST NAME - &HNAME

DECLARE
V V$INSTANCE.HOST_NAME%TYPE;
BEGIN
SELECT HOST_NAME
INTO V
FROM V$INSTANCE
WHERE HOST_NAME <> ‘IM257’;
EXCEPTION
WHEN OTHERS THEN
RETURN;
END;
/

WHENEVER SQLERROR EXIT SQL.SQLCODE;
WHENEVER SQLERROR EXIT FAILURE;

PROMPT CONTINUE CODE
PROMPT HOST NAME - &HNAME

SPOOL OFF;

PAUSE
EXIT

#14

Hi,

For use in restricted env, where user cannot access “v$instance”, I would recommend to call sys_context, which is available for most situations without restrictions.
So for your case to retrieve host name, you can use

SELECT sys_context(‘USERENV’, ‘HOST’) FROM DUAL;

Hope this helps.
Brg

Damir

#15

Hi Damir,

my problem is not the select. I use this

COL HOSTNAME NEW_VALUE HNAME

SELECT HOST_NAME AS HOSTNAME FROM V$INSTANCE;

and I get the host_name.

my problem is to stop the SQLPLUS script if the host_name is ‘IM133’

André

#16

Ok.

This is working for me:

set echo on;

set serveroutput on size unlimited;

whenever sqlerror EXIT rollback;
declare
ahost varchar2(32);
begin
SELECT sys_context(‘USERENV’, ‘HOST’) into ahost FROM DUAL;
if ahost=‘XXXXX’ then <-change your value here for real host name
dbms_output.put_line(‘Exiting-host is wrong’);
RAISE_APPLICATION_ERROR(-20000, ‘wrong host!’);
end if;
dbms_output.put_line(‘host is ok’);
– your code goes here
end;
/
or place aditional part of script as continue from previous part:

whenever sqlerror continue;
…your code here

and place code here so in a case of error script will not exit in other part of script.

#17

Hi Damir,

this script works now with your code. Thx.

…

…

DECLARE
AHOST VARCHAR2( 64 );
BEGIN
SELECT HOST_NAME INTO AHOST FROM V$INSTANCE;

IF LOWER( AHOST ) = ‘IM133’ THEN
DBMS_OUTPUT.PUT_LINE( ‘Exiting-host is wrong’ );
RAISE_APPLICATION_ERROR( -20000, ‘wrong host!’ ); – not enough
END IF;

DBMS_OUTPUT.PUT_LINE( ‘host is ok’ );
END;
/

WHENEVER SQLERROR CONTINUE;

…

…

André

#18

hm…your if will allways be false…

IF LOWER( AHOST ) = ‘IM133’ THEN

should be:

IF UPPER( AHOST ) = ‘IM133’ THEN

???

#19

Hi Damir,

I have done this. Thx.

André

#20

Wow, I must say, this was not even my question and I was not able to help much but his has been a very interesting thread!!! Thanks to all who participated in getting this rabbit skinned.

On Wed, Aug 16, 2017 at 6:39 AM, rust bounce-rust@toadworld.com wrote:

RE: sqlplus script exit

Reply by rust
Hi Damir,

I have done this. Thx.

André

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle Forum notifications altogether.

Toad for Oracle - Discussion Forum

Flag this post as spam/abuse.

–
Gene L. Bradley Jr.

Systems Analyst

Office of Information Technology

Jackson State University

1400 J R Lynch Street

P.O. Box 17750

Jackson, MS 39217

ph 601.979.1042

fax 601.371.9146

email gbradley@jsums.edu

*In God we trust; all others bring data. * ~W.E. Deming

CONFIDENTIALITY STATEMENT

This electronic transmission is intended for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by telephone (601) 979-1042. Thank you.