2 Replies Latest reply on Aug 11, 2020 4:40 AM by Veera_V

    Using Dictionary tables ( DBA_* and ALL_*) inside cursor for loop is not working

    Veera_V

      HI,

       

      Using Dictionary tables ( DBA_* and ALL_*) inside cursor for loop is not working.

      When I run the SQL manually it displays all the DBA_* and ALL_* records.


      User has "GRANT DBA" access.

       

      When I use DBA_* tables I am getting Table doesn't exist error and When I use ALL_* tables No results in coming out from cursor.

       

      SQL>
      SQL> create or replace PROCEDURE test_tab (lob_part OUT VARCHAR2) IS
        2  BEGIN
        3      FOR c IN (select table_name as segment_name, tablespace_name
        4           from all_tables where owner = 'UATPE1'
        5           AND table_name = 'USR_AUDIT'  )
        6        LOOP
        7        lob_part := lob_part || ' lob (' || c.segment_name ||') store as (tablespace ' || c.tablespace_name || ')';
        8        DBMS_OUTPUT.PUT_LINE('IN STRING IS:'||lob_part);
        9        END LOOP;
      10        DBMS_OUTPUT.PUT_LINE('STRING IS:'||lob_part);
      11  END test_tab;
      12  /
      Procedure created

       

       

      SQL>

      SQL> SET SERVEROUTPUT ON
      SQL> DECLARE
        2    lob_part VARCHAR2(3000) :='';
        3  begin
        4    test_tab(lob_part);
        5  END;
        6  /
      STRING IS:
      PL/SQL procedure successfully completed

       

      Below is working ( with out DBA* and ALL_*)

       

      create or replace PROCEDURE test_tab (lob_part OUT VARCHAR2) IS
      BEGIN
          FOR c IN ( SELECT 'seg1' segment_name, 'tbs1' tablespace_name
               FROM dual
               union
               SELECT 'seg2' segment_name, 'tbs2' tablespace_name
               FROM dual )
            LOOP
            lob_part := lob_part || ' lob (' || c.segment_name ||') store as (tablespace ' || c.tablespace_name || ')';
            DBMS_OUTPUT.PUT_LINE('IN STRING IS:'||lob_part);
            END LOOP;
            DBMS_OUTPUT.PUT_LINE('STRING IS:'||lob_part);
      END test_tab;

       

       

      SQL> SET SERVEROUTPUT ON
      SQL> DECLARE
        2    lob_part VARCHAR2(3000) :='';
        3  begin
        4    test_tab(lob_part);
        5  END;
        6  /
      IN STRING IS: lob (seg1) store as (tablespace tbs1)
      IN STRING IS: lob (seg1) store as (tablespace tbs1) lob (seg2) store as (tablespace tbs2)
      STRING IS: lob (seg1) store as (tablespace tbs1) lob (seg2) store as (tablespace tbs2)
      PL/SQL procedure successfully completed

       

       

      SQL> SET SERVEROUTPUT ON
      SQL> DECLARE
        2    lob_part VARCHAR2(3000) :='';
        3  begin
        4    test_tab(lob_part);
        5  END;
        6  /
      IN STRING IS: lob (seg1) store as (tablespace tbs1)
      IN STRING IS: lob (seg1) store as (tablespace tbs1) lob (seg2) store as (tablespace tbs2)
      STRING IS: lob (seg1) store as (tablespace tbs1) lob (seg2) store as (tablespace tbs2)
      PL/SQL procedure successfully completed

       

      Using DBA_* getting tables doesn't exist error ( Although I can query using the owner of the Procedure)

       

       

      SQL> create or replace PROCEDURE test_tab (lob_part OUT VARCHAR2) IS
        2  BEGIN
        3      FOR c IN (SELECT segment_name, tablespace_name
        4           FROM dba_lobs
        5           WHERE owner = 'UATPE1'
        6           AND table_name = 'USR_AUDIT'  )
        7        LOOP
        8        lob_part := lob_part || ' lob (' || c.segment_name ||') store as (tablespace ' || c.tablespace_name || ')';
        9        DBMS_OUTPUT.PUT_LINE('IN STRING IS:'||lob_part);
      10        END LOOP;
      11        DBMS_OUTPUT.PUT_LINE('STRING IS:'||lob_part);
      12  END test_tab;
      13  /
      Warning: Procedure created with compilation errors

      SQL> SHOW ERRORS
      Errors for PROCEDURE TEST_TAB:
      LINE/COL ERROR
      -------- -------------------------------------------------
      4/15     PL/SQL: ORA-00942: table or view does not exist
      3/15     PL/SQL: SQL Statement ignored
      8/47     PLS-00364: loop index variable 'C' use is invalid
      8/7      PL/SQL: Statement ignored

       

      Regards,

      Veera