Home » SQL & PL/SQL » SQL & PL/SQL » procedure not working properly when run through sys (oracle 10g)
procedure not working properly when run through sys [message #405322] Wed, 27 May 2009 06:34 Go to next message
navneet_sharma
Messages: 70
Registered: September 2008
Location: New Delhi, India
Member
folks,
I am facing a strage problem.I am trying to find the dependency of a table .I have written a proc for the same.when I run this through schema user it is working fine and table is getting populated.but when the schema user is 'SYSTEM' the process runs but table doesn't get populated. don't know the reason.
following is code i m using

CREATE OR REPLACE PROCEDURE table_scan_new (
     v_table_name             IN       VARCHAR2)
AS
     sel_count                     NUMBER := 0;
     ins_count                     NUMBER := 0;
     upd_count                     NUMBER := 0;
     del_count                     NUMBER := 0;
     l_name                        VARCHAR2 (50);
     l_text                        VARCHAR2 (2000);
     v_sql                         LONG;
BEGIN
     EXECUTE IMMEDIATE ' TRUNCATE TABLE TABLE_SCAN_1';

     INSERT INTO TABLE_SCAN_1 (SELECT *
            FROM   ALL_DEPENDENCIES
            WHERE  trim (UPPER(referenced_name)) = trim(UPPER (v_table_name))
            AND    TYPE IN ('PROCEDURE', 'PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'TRIGGER')
          AND    owner IN
                     ('ABC'
                     ,'BBC'));
  

     DBMS_OUTPUT.put_line ('COUNT=' || SQL%ROWCOUNT);
     COMMIT;
EXCEPTION
     WHEN OTHERS THEN
          DBMS_OUTPUT.put_line (SQLCODE || '  ' || SQLERRM);
END;


regards,
Navneet
Re: procedure not working properly when run through sys [message #405323 is a reply to message #405322] Wed, 27 May 2009 06:37 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
1) Do not run code as SYS/SYSTEM
They are not normal accounts.

2) Your WHEN OTHERS block serves only to hide the errors that you get - get rid of it and you'll see what the error you're getting back is.
Re: procedure not working properly when run through sys [message #405343 is a reply to message #405323] Wed, 27 May 2009 07:17 Go to previous messageGo to next message
navneet_sharma
Messages: 70
Registered: September 2008
Location: New Delhi, India
Member
Hi,
The proc is not giving any error.its running fine for both the users normal schema user and through SYSTEM also but while running through SYSTEM the table is not getting populated.

I don't know why this is behaving strangly.there is nothing complex in the code itself.

regards,
Navneet
Re: procedure not working properly when run through sys [message #405350 is a reply to message #405343] Wed, 27 May 2009 07:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
2) Your WHEN OTHERS block serves only to hide the errors that you get - get rid of it and you'll see what the error you're getting back is.

You have to FIRST do this (and copy and paste your session) BEFORE coming back.

Regards
Michel
Re: procedure not working properly when run through sys [message #405351 is a reply to message #405322] Wed, 27 May 2009 07:24 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well you're going to potentially have permerissions and scope issues.
System sees different contents in all_dependencies to other users as well.

But bottom line, as JRowbottom said - you shouldn't be running this as system. Leave system alone - it's for oracle stuff.
If you want a DBA account to run this create a new one.
Re: procedure not working properly when run through sys [message #405356 is a reply to message #405343] Wed, 27 May 2009 07:29 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The proc is not giving any error.

Mine neither, yet...
SQL> select 1/0 from dual;
select 1/0 from dual
        *
ERROR at line 1:
ORA-01476: divisor is equal to zero

SQL> declare i number;
  2  begin
  3    select 1/0 into i from dual;
  4  exception when others then DBMS_OUTPUT.put_line (SQLCODE || '  ' || SQLERRM);
  5  end;
  6  /

PL/SQL procedure successfully completed.

Regards
Michel
Previous Topic: How to pass a value from one procedure to another block by calling it
Next Topic: Select * taking lot of time
Goto Forum:
  


Current Time: Sat Dec 03 18:05:56 CST 2016

Total time taken to generate the page: 0.18237 seconds