5 Replies Latest reply on Dec 21, 2015 4:49 PM by thatJeffSmith-Oracle

    Running a package using sqldeveloper's run/debug/plan feature leads to a long error on undeclared components

    cunnings

      Hi,

       

      I have a package built within my schema, when I try to run it using the little green triangle, It returns a long list of errors indicating mostly that

       

      component DBMS_SQL must be declared

      component DBMS_LOB must be declared

      statement ignored


      I have searched the net and some said it was due to lack of permissions. The problem, though, is that it works fine if I copy/paste the sql block shown in the run window into a sql worksheet and run the worksheet using the the user related to the schema. This means the user does not lack any permission to run the procedure itself.


      On the other hand, using a connection with dba role and all privileges, running the same package is working fine using the same green run triangle. I also confirmed that DBMS_SQL and DBMS_LOB public synonyms exist and public has execute privileges on these two synonyms. The user/schema has create debug session and execute and debug privileges on the package. It seems that either there is a messy code around internal sql execution of sqldeveloper, sqldeveloper uses a different connection or a user/schema needs additional privileges to work with these features on sqldeveloper.


      Thanks

        • 1. Re: Running a package using sqldeveloper's run/debug/plan feature leads to a long error on undeclared components
          cunnings

          Hello there again,

           

          Our DBA kindly provided me with a trace of what is happening with sqldeveloper:

           

           

           

          TKPROF: Release 11.2.0.4.0 - Development on Sun Dec 20 17:58:47 2015

           

           

          Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

           

           

          Trace file: /path/to/trace/file.trc

          Sort options: default

           

           

          ********************************************************************************

          count    = number of times OCI procedure was executed

          cpu      = cpu time in seconds executing

          elapsed  = elapsed time in seconds executing

          disk     = number of physical reads of buffers from disk

          query    = number of buffers gotten for consistent read

          current  = number of buffers gotten in current mode (usually for update)

          rows     = number of rows processed by the fetch or execute call

          ********************************************************************************

           

           

          DECLARE

          remain integer;

            counter integer;

            offset integer;

            input_txt clob;

            line nvarchar2(256);

            idx integer;

            txt_table SYS.DBMS_SQL.varchar2s;

           

            cur integer;

           

            i integer;

          begin

            input_txt := :SRC;

            remain := sys.dbms_lob.getlength(input_txt);

            counter := 1;

            idx := 0;

           

            while remain > 0 loop

              idx := idx + 1;

              if remain > 256 then

                line := sys.dbms_lob.substr( input_txt, 256, counter );

                if ( lengthb( line ) > 256 ) then

                  line := substrb( line, 1, 256 );

                  offset := length( line );

                else

                  offset := 256;

                end if;

                txt_table(idx) := line;

                counter := counter + offset;

                remain := remain - offset;

              else

                line := sys.dbms_lob.substr( input_txt, remain, counter );

                if ( lengthb( line ) > 256 ) then

                  line := substrb( line, 1, 256 );

                  offset := length( line );

                else

                  offset := remain;

                end if;

                txt_table(idx) := line;

                counter := counter + offset;

                remain := remain - offset;

              end if;

            end loop;

           

            cur := DBMS_SQL.OPEN_CURSOR;

           

            for i in 1..idx loop

              line := txt_table(i);

            end loop;

            

            DBMS_SQL.PARSE( cur, txt_table, 1, idx, FALSE, DBMS_SQL.NATIVE );

            DBMS_SQL.CLOSE_CURSOR( cur );

          EXCEPTION

          WHEN OTHERS THEN

            DBMS_SQL.CLOSE_CURSOR( cur );

            RAISE;

          end;      

           

           

          call     count       cpu    elapsed       disk      query    current        rows

          ------- ------  -------- ---------- ---------- ---------- ----------  ----------

          Parse        1      0.00       0.00          0          0          0           0

          Execute      0      0.00       0.00          0          0          0           0

          Fetch        0      0.00       0.00          0          0          0           0

          ------- ------  -------- ---------- ---------- ---------- ----------  ----------

          total        1      0.00       0.00          0          0          0           0

           

           

          Misses in library cache during parse: 1

          Optimizer mode: ALL_ROWS

          Parsing user id: 164

           

           

          Elapsed times include waiting on following events:

            Event waited on                             Times   Max. Wait  Total Waited

            ----------------------------------------   Waited  ----------  ------------

            SQL*Net break/reset to client                   2        0.00          0.00

            SQL*Net message to client                       1        0.00          0.00

            SQL*Net message from client                     1        0.00          0.00

           

           

           

           

           

           

          ********************************************************************************

           

           

          OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

           

           

          call     count       cpu    elapsed       disk      query    current        rows

          ------- ------  -------- ---------- ---------- ---------- ----------  ----------

          Parse        1      0.00       0.00          0          0          0           0

          Execute      0      0.00       0.00          0          0          0           0

          Fetch        0      0.00       0.00          0          0          0           0

          ------- ------  -------- ---------- ---------- ---------- ----------  ----------

          total        1      0.00       0.00          0          0          0           0

           

           

          Misses in library cache during parse: 1

           

           

          Elapsed times include waiting on following events:

            Event waited on                             Times   Max. Wait  Total Waited

            ----------------------------------------   Waited  ----------  ------------

            SQL*Net message from client                     5      246.99        247.00

            SQL*Net message to client                       5        0.00          0.00

            direct path write temp                          1        0.00          0.00

            SQL*Net break/reset to client                   2        0.00          0.00

           

           

           

           

          OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

           

           

          call     count       cpu    elapsed       disk      query    current        rows

          ------- ------  -------- ---------- ---------- ---------- ----------  ----------

          Parse        0      0.00       0.00          0          0          0           0

          Execute      0      0.00       0.00          0          0          0           0

          Fetch        0      0.00       0.00          0          0          0           0

          ------- ------  -------- ---------- ---------- ---------- ----------  ----------

          total        0      0.00       0.00          0          0          0           0

           

           

          Misses in library cache during parse: 0

           

           

              1  user  SQL statements in session.

              0  internal SQL statements in session.

              1  SQL statements in session.

          ********************************************************************************

          Trace file: /path/to/trace/file.trc

          Trace file compatibility: 11.1.0.7

          Sort options: default

           

           

                 1  session in tracefile.

                 1  user  SQL statements in trace file.

                 0  internal SQL statements in trace file.

                 1  SQL statements in trace file.

                 1  unique SQL statements in trace file.

               121  lines in trace file.

                 0  elapsed seconds in trace file.


          I found that no matter what, the only way that this happens with this user is running anything using anonymous block in run window, below is a screenshot:


          Capture2.PNG

          • 3. Re: Running a package using sqldeveloper's run/debug/plan feature leads to a long error on undeclared components
            thatJeffSmith-Oracle

            The trace is good, but you can also just open View > Log, and activate the Statements page. It will show you all the code from SQLDev being sent to the database.

            • 4. Re: Running a package using sqldeveloper's run/debug/plan feature leads to a long error on undeclared components
              cunnings

              I was logged in as the owner of the schema that the package belonged to.

              • 5. Re: Running a package using sqldeveloper's run/debug/plan feature leads to a long error on undeclared components
                thatJeffSmith-Oracle

                can you show us where you've ran DBMS_SQL and DBMS_LOB successfully on the same db as the same user?