3 Replies Latest reply on Mar 24, 2011 4:48 PM by sybrand_b

    UNABLE TO CATCH THE ERROR

    newbie
      HI,
      PLS CHECK THE BELOW CODE

      declare
      v_tab varchar2(10000);
      v_ind varchar2(10000);
      v_syn varchar2(10000);
      begin

      DBMS_OUTPUT.ENABLE(1000000);

      for rec in (select TABLE_NAME from all_tables where owner = 'JC_804' AND ROWNUM =1)
      loop

      execute immediate 'SELECT DBMS_METADATA.GET_DDL( '|| '''TABLE'',' ||''''||rec.table_name||''''||',''JC_804'') FROM DUAL' into v_syn;

      dbms_output.put_line(v_tab);
      dbms_output.put_line(v_ind);
      dbms_output.put_line(v_syn);
      end loop;
      end;

      if type is table successfully compiled
      but if iam taking index and synonym it is showing that
      ORA-31603: object "TF_BE_TAB" of type INDEX not found in schema "JC_804"
      ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
      ORA-06512: at "SYS.DBMS_METADATA", line 3912
      ORA-06512: at "SYS.DBMS_METADATA", line 5678
      ORA-06512: at line 1
      ORA-06512: at line 12


      object "TF_BE_TAB" of type SYNONYM not found in schema "JC_804"
      ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
      ORA-06512: at "SYS.DBMS_METADATA", line 3912
      ORA-06512: at "SYS.DBMS_METADATA", line 5678
      ORA-06512: at line 1
      ORA-06512: at line 12

      Pls provide me solution
        • 1. Re: UNABLE TO CATCH THE ERROR
          Sven W.
          There is no reason to call dbms_metadata dynamically.
          Could you explain why you do that?

          Instead of the execute I would try something like this:
          untested
          declare
             v_tab varchar2(10000);
             v_ind varchar2(10000);
             v_syn clob;
          begin
          ...
          v_syn := DBMS_METADATA.GET_DDL(object_type=> 'TABLE', name=>rec.table_name,schema=>'JC_804')
          Your problem of cause comes from the fact that you search for a table name but want the index or synonym statement. So instead of selecting from ALL_TABLES you could go to ALL_OBJECTS and find out the correct index and synoynm names.

          It might be that you need to use "authid current_user" to call dbms_metadata in your procedure or function.

          Edited by: Sven W. on Mar 24, 2011 5:44 PM
          • 2. Re: UNABLE TO CATCH THE ERROR
            Peter Gjelstrup
            Hi,

            For starters you could help yourself by not using dynamic SQL:
            SQL> declare
               v_tab varchar2(10000);
               v_ind varchar2(10000);
               v_syn varchar2(10000);
            begin
            
               DBMS_OUTPUT.ENABLE(1000000);
            
               for rec in (select TABLE_NAME from all_tables where owner = 'JC_804' AND ROWNUM =1)
               loop
            
                  v_syn := DBMS_METADATA.GET_DDL('TABLE',rec.table_name,'JC_804');
            
                  dbms_output.put_line(v_tab);
                  dbms_output.put_line(v_ind);
                  dbms_output.put_line(v_syn);
               end loop;
            end;
            /
            PL/SQL procedure successfully completed.
            As to your problem, consider this:
            SQL> select dbms_metadata.get_ddl('TABLE', 'DUAL','SYS') from dual;
            
            DBMS_METADATA.GET_DDL('TABLE','DUAL','SYS')                                     
            --------------------------------------------------------------------------------
                                                                                            
              CREATE TABLE "SYS"."DUAL"                                                     
               (     "DUMMY" VARCHAR2(1)                                                        
               ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING           
              STORAGE(INITIAL 16384 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645         
              PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
            FAULT CELL_FLASH_CACHE DEFAULT)                                                 
              TABLESPACE "SYSTEM"                                                           
                                                                                            
            1 row selected.
            
            SQL> select dbms_metadata.get_ddl('INDEX', 'DUAL','SYS') from dual;
            select dbms_metadata.get_ddl('INDEX', 'DUAL','SYS') from dual
            *
            Error at line 0
            ORA-31603: object "DUAL" of type INDEX not found in schema "SYS"
            ORA-06512: at "SYS.DBMS_METADATA", line 4018
            ORA-06512: at "SYS.DBMS_METADATA", line 5843
            ORA-06512: at line 1
            This just tells me that there is no index in sys schema called dual.

            Pls provide me solution
            To what? - We have no idea on what you are trying to do

            Regards
            Peter
            • 3. Re: UNABLE TO CATCH THE ERROR
              declare
              v_tab varchar2(10000);
              v_ind varchar2(10000);
              v_syn varchar2(10000);
              begin
              DBMS_OUTPUT.ENABLE(1000000);
              for rec in (select owner,TABLE_NAME from all_tables where owner = 'JC_804' AND ROWNUM =1)
              loop
              SELECT DBMS_METADATA.GET_DDL('TABLE',rec.table_name,rec.owner)
              into v_tab
              FROM DUAL;
              SELECT DBMS_METADATA.GET_DEPENDENT_DDL('INDEX',rec.table_name,rec.owner)
              into v_ind
              FROM DUAL;
              dbms_output.put_line(v_tab);
              dbms_output.put_line(v_ind);
              --dbms_output.put_line(v_syn);
              end loop;
              end;
              /


              Please apologize for
              - not being able to consult the documentation, 2 times
              - SHOUTING

              And yes, I had to consult the documentation, and I have no problem in doing so.
              I don't usually do this on behalf of lazy newbies, so this will be the LAST time I do this for you. Next time, I will mark a similar post of you directly for abuse of this forum.

              ----------------
              Sybrand Bakker
              Senior Oracle DBA