Solved

exec Oracle Procedure

Posted on 2015-01-08
12
204 Views
Last Modified: 2015-01-09
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>
0
Comment
Question by:Oranew
  • 5
  • 4
  • 3
12 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 250 total points
ID: 40538272
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 40538277
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
 

Author Comment

by:Oranew
ID: 40538688
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
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 total points
ID: 40538698
>>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
 

Author Comment

by:Oranew
ID: 40538807
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40538821
>>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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 250 total points
ID: 40538858
>> 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
 

Author Comment

by:Oranew
ID: 40538921
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
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 total points
ID: 40539035
>>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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40539207
>>"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
 

Author Comment

by:Oranew
ID: 40539229
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
 

Author Comment

by:Oranew
ID: 40539231
Excellent ! A+++
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now