Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 289
  • Last Modified:

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>
0
Oranew
Asked:
Oranew
  • 5
  • 4
  • 3
4 Solutions
 
sdstuberCommented:
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
slightwv (䄆 Netminder) 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
 
sdstuberCommented:
>> 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) 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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 5
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now