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;
??
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:
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.
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
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é
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é
Thanks we are all trying to help here... Perhaps we need more information.
The moderator may move this as it is not really a Toad question but an Oraclie question.
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:
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.
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:
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.
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é
Hi Gene,
I think revoke is not the right syntax
André
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.
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é
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.
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
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
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é
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.
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é
hm…your if will allways be false…
IF LOWER( AHOST ) = ‘IM133’ THEN
should be:
IF UPPER( AHOST ) = ‘IM133’ THEN
???
Hi Damir,
I have done this. Thx.
André
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:
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.