1

Does anyone knows if there is an equivalent to SQLERRM when no error occurred?

I know I can count the affected rows like here, but can I automatically get the message I would get from SQLPLUS e.g. role granted. after an execute immediate in PL/SQL?

I am trying to do 

begin
  execute immediate 'grant select on s1.tbl1 to user1';
  dbms_output.put_line(<some function>);
end;

and get

Grant succeeded.

Thanks, J

J. Chomel
  • 8,193
  • 15
  • 41
  • 69
  • 1
    a) `` - THere is no such function, that will provide you with the `Grant succeeded` message. b) `sql%rowcount` will not help you here. In this case, it'll be 0 anyway. c) You can look at `sqlcode`, which if execution went smoothly would provide you with a success code `0 (ORA-0000 - normal, successful completion)`. But the latter one won't help you much either. So just use `dbms_output.put_line('Grant succeeded');` and let any exception should it occur be propagated or logged. – Nick Krasnov Oct 04 '17 at 15:54

2 Answers2

2

Feedback like "Grant succeeded." is the feature of sqlplus.

If you enable SQL*Net tracing (details http://docs.oracle.com/cd/E11882_01/java.112/e16548/apxtblsh.htm#JJDBC28987 or http://www.juliandyke.com/Diagnostics/Trace/NetTrace.php) you will see that string "Grant succeeded" is NOT something client application sqlplus receives from Oracle.

So how does it know what to display? Because sqlplus has privimitive mechanism of checking command type before sending it to server. If you type non-existend command then it will not be even sent to server (you can check that also in SQL*NET trace).

Moreover you can check list of commands if you open binary of sqlplus as text and search for string "grant", for example (bottom left corner on screenshot).

enter image description here

In below case there will be no exchange with server because command is intentionally wrong.

SQL> some_command

SP2-0734: unknown command beginning "some_comma..." - rest of line ignored.

So Oracle knows command type before sending SQL text to server and for some commands it receives additional information after execution. Like number of rows affected for DML. This information is enough to display "feedback".

Back to your task, the easiest way would be to implement primitive parsing of first 1-3 words of the command and display correponding message if no errors occured.

Hardcore option is to enable SQL tracing and derive command type from trace file.

=====================
PARSING IN CURSOR #3 len=31 dep=0 uid=91 oct=17 lid=91 tim=2178486867995 hv=3483936374 ad='b967c6a4' sqlid='10y8y7m7uj9mq'
grant execute on <...> to <...>
END OF STMT
PARSE #3:c=0,e=614,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=2178486867994
=====================

oct means Oracle Command Type

SQL> select command_name from v$sqlcommand where command_type = 17;

COMMAND_NAME
----------------------------------------------------------------
GRANT OBJECT

Another option is to enable Audit but my preference would be to keep it simple.

Dr Y Wit
  • 2,000
  • 9
  • 16
0

If it is only the output for success message is what we are concerned, then simply using exceptions would help.

SET SERVEROUTPUT ON
BEGIN
  execute immediate 'grant select on s1.tbl1 to user1' ;
  dbms_output.put_line('Grant succeeded');
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('Grant Failed');
    procedure_to_log_error(SQLERRM);
END;
Nick Krasnov
  • 26,886
  • 6
  • 61
  • 78
Kaushik Nayak
  • 30,772
  • 5
  • 32
  • 45
  • E.g. means "for example". I would like the message to be contextual after the command passed as"immediate". This doesn't answer correctly. Grant was only an example. Thank you anyway. I understand your rep-...ring behavior. I was so once but realized I came too late to SO... – J. Chomel Oct 04 '17 at 17:51
  • What does " rep..ring behaviour" mean? – Kaushik Nayak Oct 04 '17 at 17:57
  • 1
    It means you are in desperate need for reputation. But you worth better than just that, system is complete unfair. See sometimes how poor questions get so much reputation!! Like here: https://stackoverflow.com/questions/861983/number-of-rows-affected-by-an-update-in-pl-sql . Anyway, sorry, bad evening last night. Still not an answer. – J. Chomel Oct 05 '17 at 05:39