4 Replies Latest reply on Jul 26, 2017 1:08 PM by thatJeffSmith-Oracle

    Cannot see procedure code 12C

    user7925917

      Hi,

       

      I recently migrated a project from 11 2 to 12c, later connected using developer and noticed I could not procedure bodies, just the 'create or replace' statement nothing further?  The connections bar could at least identify the parameters but I cannot find any way to see the procedure contents.

       

      I upgraded to developer 17.2 and same issue.

       

      I connected with TOAD and all was well.

       

      So this a bug in SQL Developer or am I getting a little old for this lark?

       

      Cheers
      Dan

        • 1. Re: Cannot see procedure code 12C
          thatJeffSmith-Oracle

          We run a series of queries to get your code into the editor.

           

          View > Log > Statements.

           

          Go to the Code node in your connection tree, load up your pl/sql list of objects.

           

          Clear the log.

           

          Double-click/open your PL/SQL object. Observe the log - here's an example of what we run to open my stored procedure. Confirm these queries work for you a ran by the same user in a sql worksheet.

           

          No doubt toad has a different set of features and accompanying queries to show you your code.

           

          64HR40 WITH src AS (SELECT ROWNUM,LINE,TEXT,origin_con_id FROM SYS.Dba_SOURCE WHERE TYPE = :TYPE AND OWNER = :OWNER AND NAME = :NAME) SELECT text FROM src, (SELECT max(origin_con_id) max_orig FROM src) WHERE origin_con_id = max_orig ORDER BY LINE "OWNER"="HR", "TYPE"="PROCEDURE", "NAME"="NULLS"
          63HR62SELECT /*OracleDictionaryQueries.ALL_ORACLE_SINGLE_OBJECT_QUERY*/ O.OBJECT_NAME, O.OBJECT_TYPE, O.OBJECT_ID, O.LAST_DDL_TIME FROM SYS.DBA_OBJECTS O WHERE O.OWNER = ? AND O.OBJECT_NAME = ? AND O.OBJECT_TYPE = ? 1="HR", 2="NULLS", 3="PROCEDURE"
          62HR48SELECT LINE,POSITION,TEXT,ATTRIBUTE FROM USER_ERRORS WHERE TYPE=? AND NAME=? 1="PROCEDURE", 2="NULLS"
          61HR9select 1 from sys.obj$ where 1=0null
          60HR17SELECT 1 FROM system_privilege_map WHERE name = 'CREATE PROCEDURE' OR name = 'ALTER PROCEDURE'null
          59HR294 WITH src AS (SELECT ROWNUM,LINE,TEXT,origin_con_id FROM SYS.Dba_SOURCE WHERE TYPE = :TYPE AND OWNER = :OWNER AND NAME = :NAME) SELECT text FROM src, (SELECT max(origin_con_id) max_orig FROM src) WHERE origin_con_id = max_orig ORDER BY LINE "OWNER"="HR", "TYPE"="PROCEDURE", "NAME"="NULLS"
          • 2. Re: Cannot see procedure code 12C
            user7925917

            Hi Jeff,

             

            Thanks for your response, this has highlighted the issue.

             

            The user I am connecting as does not have access to select from the SYS.Dba_SOURCE object.

             

            If I connect as the SYS user, open schema browser, switch to the user/procedures I am able to see the procedure bodies.

             

            My thinking is that a user with permissions to create procedures should be able to see their own.  I was able to work around this by granting the user the SELECT_CATALOG_ROLE.

             

            Regards
            Dan

            • 3. Re: Cannot see procedure code 12C
              thatJeffSmith-Oracle

              DBA views aren't required. We only use them if we can because they're faster. We check for access to DBA views at connection time.

               

              Use the statements log to see what's happening in your session.

              • 4. Re: Cannot see procedure code 12C
                thatJeffSmith-Oracle

                this user ONLY has CONNECT and RESOURCE

                 

                queries dba_source - ORA-00942

                creates stored procedure, opens stored procedure - no problem

                 

                low-privs.png