exec Oracle Procedure

I have a procedure I am executing in Oracle database 11g rel 2.
How can I generate log file line by line and know what it does in the back ground ?

SQL>exec INS_CRT_PROC
ERROR:

PL/SQL procedure successfully completed.

SQL>
OranewAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
sdstuberConnect With a Mentor Commented:
use profiler or hierarchical profiler.

you will need to contact your dba for assistance either way.

to use the profiler, after your dba installs the objects

BEGIN
    Dbms_Profiler.start_profiler('oranew');
    ins_crt_proc;
    dbms_profiler.stop_profiler;
END;

then, query the profiler tables.

if you use the hierarchical profiler, your dba will have to retrieve the generated file for you.
0
 
sdstuberCommented:
another option, use an instrumentation tool and add logging lines inside the code.

This is ideal because you get to control the logging and provide business context, not just generic oracle messages.

for the most basic logging, try dbms_output.put_line(x)  where X is some text

if you use dbms_output, then before you run your procedure in sql*plus, turn on output capture

SQL> set serveroutput on

your output will then dump to the screen, or to a file if you spool it
0
 
OranewAuthor Commented:
I did but I am getting errors from the exec procedure but when I run the same sql manually it get to success:-
ALso, The serveroutput does not tell me what is at line 77. ( I am having the dba role)

SQL>set serveroutput on
SQL>exec INS_CRT_PROC
ERROR:create public synonym ASYNCCRVIEW for ASYNCCRVIEW
BEGIN INS_CRT_PROC; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SQL", line 1199
ORA-06512: at "SYSADM.INS_CRT_PROC", line 77
ORA-06512: at line 1


SQL>create public synonym ASYNCCRVIEW for ASYNCCRVIEW;

Synonym created.

SQL>exec INS_CRT_PROC
ERROR:create public synonym REP_TBL for REP_TBL
BEGIN INS_CRT_PROC; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SQL", line 1199
ORA-06512: at "SYSADM.INS_CRT_PROC", line 77
ORA-06512: at line 1


SQL>create public synonym REP_TBL for REP_TBL;

Synonym created.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
>>I did but I am getting errors from the exec procedure

This is really a different question that the one asked.

>> I am having the dba role

When executing stored procedures granted privileges must be explicitly granted to the owner of the procedure.  The privileges CANNOT be granted through a ROLE.

You need to grant create synonym to the owner of the procedure.  This goes for any other DDL statements you might have in the procedure.
0
 
OranewAuthor Commented:
Hi Slightwv,
The user I logged in with can create synonyms manually and the owner of the procedure is also the same user.
There is something else going on not that the privileges is missing. I granted "create any synonyms" but the issue still remains the same.
That is what my original question was.. If I can see what is under line 77 but the server output is not sufficient to drill down to the actual root cause.
0
 
slightwv (䄆 Netminder) Commented:
>>The user I logged in with can create synonyms manually

Again, this doesn't matter.

>> If I can see what is under line 77

This isn't 100% accurate but will get you in the vicinity of where the error is coming from.  In other words, it may not be exactly line 77 in the output below but the error will be coming from close by.

select line, text from user_source where name='INS_CRT_PROC' order by line;
0
 
sdstuberConnect With a Mentor Commented:
>> I granted "create any synonyms" but the issue still remains the same.

did you grant that to the owner of the procedure   (probably correct)
or to a role  (not correct)
or to a user running the procedure  (probably not correct)

I say "probably" on two of those options because the default AUTHID of pl/sql objects is DEFINER,  but it is possible to be CURRENT USER instead.

The former means grant the privilege to the owner, the latter means grant it to the person running it
0
 
OranewAuthor Commented:
Hi Slightwv,
Thanks for your help.
select line, text from user_source where name='INS_CRT_PROC' order by line;
I don't need to see the source of the procedure as I can see it from toad but I need to see what is the error after running the procedure. Anyway ; I have the error messages now but not an answer to it.( Trying to find out)

Hi sdstuber,
Thank you so much for looking into it but this is not a permission issue. The user who is running it is SYSADM. the owner of the procedure is also SYSADM. The SYSADM has the DBA ROLE. and also has the create any table, create any synonym, update any table role with unlimited quota. It looks like the error is something to do with the DBMS_SQL . If my question is not relevant please let me know so that I will close this now and ask a new question

ORA-06512: at "SYS.DBMS_SQL", line 1199
0
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
>>but I need to see what is the error after running the procedure.

You asked "If I can see what is under line 77".  If you didn't want to see line 77, then what did you mean by that?

>>but this is not a permission issue.

The error you posted above "ORA-01031: insufficient privileges" means that it is a permission issue.

>> It looks like the error is something to do with the DBMS_SQL .

No.  The error "ORA-06512: at "SYS.DBMS_SQL", line 1199" means that dbms_sql is the first reporter of the "ORA-01031: insufficient privileges"  error.

The DBMS_SQL call was from "ORA-06512: at "SYSADM.INS_CRT_PROC", line 77".

You sort of read the stack trace in reverse.

The problem is here:
SYSADM.INS_CRT_PROC", line 77

And it is a permission problem:
ORA-01031: insufficient privileges
0
 
slightwv (䄆 Netminder) Commented:
>>"If I can see what is under line 77"

I think I finally understand what you meant by this.

You wanted to trace not only your procedure but every line of every other procedure it called.  So you wanted to trace what was in DBMS_SQL as well to see line 1199 and what you think is causing the error.

Sorry, you cannot do this.

Again, the error is in the DDL statement being executed in INS_CRT_PROC on or near line 77.  It is not in DBMS_SQL.

Trust us on that.
0
 
OranewAuthor Commented:
Yes; You were right and I appreciate your strong confidence on replying to my question and staying on top of it.
The issue is with the permissions:-

Grant create public synonym to sysadm;

It is now resolved.
0
 
OranewAuthor Commented:
Excellent ! A+++
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.