Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

exec Oracle Procedure

Posted on 2015-01-08
12
215 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 74

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 74

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
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 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
 
LVL 74

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

839 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