Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> recreate database script not work

recreate database script not work

From: Hai <perfect77063_at_gmail.com>
Date: 30 Mar 2007 13:33:41 -0700
Message-ID: <1175286821.354196.73470@r56g2000hsd.googlegroups.com> 





Hi


I got a script which is supposed to regenerate database systax.
However I have tested several time, the output file 'init.sql' is
empty. I tried to debug, and found out there is no date in DBASE_TEMP
table. This script 'gen_dbse_9_re-create_a_database.sql' is associated
with another script 'gen_dbse_9_re-create_a_tablespace.sql' to
regenerating create tablespace script.



Could you take a look and see what is wrong?




REM gen_dbse_9.sql


REM This script can be used to generate the commands to re-create an
existing database


REM This script is applicable only in Oracle9i onwards



REM set echo off term on verify off feedback off pagesize 0 trimspool
on



select 'Creating database build script...' from DUAL;



accept oracle_sid prompt "Enter the SID of the database: "



accept init_loc prompt "Enter the location for the init.ora file: "



set term off



drop table DBASE_TEMP;



create table DBASE_TEMP (

        Lineno NUMBER,
        Text VARCHAR2(100));





declare


   cursor PARAM_CURSOR is

         select Name,
                Value,
                Description
           from V$PARAMETER
          where Isdefault = 'FALSE'
          order by Name;

   cursor LOGFILE_CURSOR (LF_Group_Num IN VARCHAR2) is
         select A.Group#,
                A.Bytes,
                A.Members,
                B.Member
           from V$LOG A,
                V$LOGFILE B
          where A.Group# = B.Group#
            and A.Group# = LF_Group_Num
          order by A.Group#;

   cursor DFILE_CURSOR is
         select A.Maxextend,
                A.Inc,
                B.File_Name,
                B.Bytes
           from SYS.FILEXT$ A,
                DBA_DATA_FILES B
          where B.File_ID = A.File#(+)
            and Tablespace_Name = 'SYSTEM'
       order by File_ID;

   cursor TSPACE_CURSOR is
         select Text
           from TSPACE_TEMP
          order by Tspace_Name, Lineno;

   cursor TEMP_SPACE_CURSOR is
         select Username,
                Default_Tablespace,
                Temporary_Tablespace
           from DBA_USERS
          where Username in ('SYS','SYSTEM');

   cursor ROLLBACK_SEGS_CURSOR is
         select A.Segment_Name,
                A.Owner,
                A.Tablespace_Name,
                A.Initial_Extent,
                A.Next_Extent,
                A.Min_Extents,
                A.Max_Extents,
                A.Pct_Increase,
                A.Status,
                B.Optsize
           from DBA_ROLLBACK_SEGS A,
                V$ROLLSTAT        B
          where A.Segment_Id = B.USN(+)
            and A.Segment_id > 0
            and A.Segment_Name != 'RB_TEMP'
          order by A.Owner, A.Segment_Name;

   Lv_PC_Name                 V$PARAMETER.Name%TYPE;
   Lv_PC_Value                V$PARAMETER.Value%TYPE;
   Lv_PC_Description          V$PARAMETER.Description%TYPE;
   Lv_LF_Group_Num            V$LOG.Group#%TYPE;
   Lv_LF_Bytes                V$LOG.Bytes%TYPE;
   Lv_LF_Members              V$LOG.Members%TYPE;
   Lv_LF_Member               V$LOGFILE.Member%TYPE;
   Lv_DF_MaxExtend            SYS.FILEXT$.MaxExtend%TYPE;
   Lv_DF_Inc                  SYS.FILEXT$.Inc%TYPE;
   Lv_DF_File_Name            DBA_DATA_FILES.File_Name%TYPE;
   Lv_DF_Bytes                DBA_DATA_FILES.Bytes%TYPE;
   Lv_TS_Text                 TSPACE_TEMP.Text%TYPE;
   Lv_TE_Temp_Tspace          DBA_USERS.Temporary_Tablespace%TYPE;
   Lv_TE_Deflt_Tspace         DBA_USERS.Default_Tablespace%TYPE;
   Lv_TE_Username             DBA_USERS.Username%TYPE;
   Lv_RB_Segment_Name         DBA_ROLLBACK_SEGS.Segment_Name%TYPE;
   Lv_RB_Owner                DBA_ROLLBACK_SEGS.Owner%TYPE;
   Lv_RB_Tablespace_Name      DBA_ROLLBACK_SEGS.Tablespace_Name%TYPE;
   Lv_RB_Initial_Extent       DBA_ROLLBACK_SEGS.Initial_Extent%TYPE;
   Lv_RB_Next_Extent          DBA_ROLLBACK_SEGS.Next_Extent%TYPE;
   Lv_RB_Min_Extents          DBA_ROLLBACK_SEGS.Min_Extents%TYPE;
   Lv_RB_Max_Extents          DBA_ROLLBACK_SEGS.Max_Extents%TYPE;
   Lv_RB_Pct_Increase         DBA_ROLLBACK_SEGS.Pct_Increase%TYPE;
   Lv_RB_Status               DBA_ROLLBACK_SEGS.Status%TYPE;
   Lv_RB_Optsize              V$ROLLSTAT.Optsize%TYPE;

   Lv_FileHandle              UTL_FILE.FILE_TYPE;

   Lv_String                  VARCHAR2(2000);
   Lv_Lineno                  NUMBER := 0;
   Lv_First_Time              BOOLEAN;
   Lv_Size                    NUMBER;
   Lv_No_Of_Groups            INTEGER;
   Lv_InitOra_Location        VARCHAR2(40);
   Lv_InitOra_Name            VARCHAR2(30);
   Lv_Database_Name           VARCHAR2(8);
   Lv_MaxLogFiles             INTEGER;
   Lv_MaxLogMembers           INTEGER;
   Lv_LogMode                 VARCHAR2(15);
   Lv_CharacterSet            VARCHAR2(64);

   Lv_Nchar_CharacterSet      VARCHAR2(64);
   Lv_Dbtimezone              VARCHAR2(64);


   Lv_Default_Temp_Tablespace VARCHAR2(64);


   Lv_Group                   INTEGER;
   Lv_Members                 INTEGER;
   Lv_Block_Size              NUMBER;
   Lv_DF_Count                NUMBER  := 0;
   Lv_Platform_Slash          VARCHAR2(1);





   procedure WRITE_OUT(P_Line INTEGER, P_String VARCHAR2) is
   begin

      insert into dbase_TEMP (Lineno, Text)
             values (P_Line,P_String);




    end;



begin


   Lv_Lineno := 1;
   Lv_InitOra_Location := '&&init_loc';
   Lv_InitOra_Name     := 'init'            ||
                          '&&oracle_sid'||
                          '.ora';

   Lv_FileHandle :=
             UTL_FILE.FOPEN(Lv_InitOra_Location,Lv_InitOra_Name,'W');





   open PARAM_CURSOR;


   loop

      fetch PARAM_CURSOR
      into  Lv_PC_Name,
            Lv_PC_Value,
            Lv_PC_Description;
      exit when PARAM_CURSOR%NOTFOUND;

      if (UPPER(Lv_PC_Name) = 'DB_NAME')
      then
          Lv_Database_Name := Lv_PC_Value;
      end if;

      Lv_String := '# ' || Lv_PC_Description;
      UTL_FILE.PUTF(Lv_FileHandle,'%s\n',Lv_String);

      Lv_String := RPAD(Lv_PC_Name,45) || ' = ' || Lv_PC_Value;
      UTL_FILE.PUTF(Lv_FileHandle,'%s\n\n',Lv_String);


   end loop;



   close PARAM_CURSOR;


   UTL_FILE.FCLOSE(Lv_FileHandle);



   Lv_Lineno := 1;



   Lv_String := 'CREATE DATABASE ' || Lv_Database_Name;
   WRITE_OUT(Lv_Lineno,Lv_String);



   Lv_Lineno := Lv_Lineno + 1;


   Lv_String := LPAD(' ',5) || 'CONTROLFILE REUSE';
   WRITE_OUT(Lv_Lineno,Lv_String);



   select MAX(Group#)

     into Lv_No_Of_Groups
     from V$LOG;





   Lv_Lineno := Lv_Lineno + 1;


   WRITE_OUT(Lv_Lineno,LPAD(' ',5) ||'LOGFILE');
   Lv_First_Time := TRUE;



   for Lv_Index IN  1 .. Lv_No_Of_Groups
   loop

      open  LOGFILE_CURSOR (Lv_Index);
      loop
          fetch LOGFILE_CURSOR
          into  Lv_LF_Group_Num,
                Lv_LF_Bytes,
                Lv_LF_Members,
                Lv_LF_Member;
          exit when LOGFILE_CURSOR%NOTFOUND;

          Lv_Size := Lv_LF_Bytes / 1024;

          if (Lv_First_Time = TRUE)
          then
             Lv_String     := '     ';
             Lv_First_Time := FALSE;
          else
             Lv_String     := '    ,';
          end if;

          if (Lv_LF_Members = 1)
          then
             Lv_String := Lv_String               ||
                         'GROUP '                 ||
                          LPAD(Lv_LF_Group_Num,3) ||
                          ' '''                   ||
                          Lv_LF_Member            ||
                          ''''                    ||
                          ' SIZE '                ||
                          Lv_Size                 ||
                          'K REUSE';

             Lv_Lineno := Lv_Lineno + 1;
             WRITE_OUT(Lv_Lineno,Lv_String);
         else
             if  (LOGFILE_CURSOR%ROWCOUNT = 1)
             then
                 Lv_String := Lv_String               ||
                             'GROUP '                 ||
                              LPAD(Lv_LF_Group_Num,3) ||
                             ' ('''                   ||
                             Lv_LF_Member             ||
                             '''' ;
                 Lv_Lineno := Lv_Lineno + 1;
                 WRITE_OUT(Lv_Lineno,Lv_String);
             else
                 Lv_String := LPAD(' ',15)   ||
                              ','''          ||
                              Lv_LF_Member   ||
                              '''';
                 if (LOGFILE_CURSOR%ROWCOUNT = Lv_LF_Members)
                 then
                     Lv_String := Lv_String  ||
                                  ') SIZE '  ||
                                  Lv_Size    ||
                                  'K REUSE';
                 end if;
                 Lv_Lineno := Lv_Lineno + 1;
                 WRITE_OUT(Lv_Lineno, Lv_String);
             end if;

         end if;
       end loop;
       CLOSE LOGFILE_CURSOR;




   end loop;



   select Value

     into Lv_Block_Size
     from SYS.V_$PARAMETER




    where UPPER(Name) = 'DB_BLOCK_SIZE';



   open DFILE_CURSOR;



   loop

         fetch DFILE_CURSOR into    Lv_DF_MaxExtend,
                                    Lv_DF_Inc,
                                    Lv_DF_File_Name,
                                    Lv_DF_Bytes;
         exit when DFILE_CURSOR%NOTFOUND;
         Lv_DF_Count := Lv_DF_Count + 1;

         if (Lv_DF_Count > 1)
         then
              Lv_String := '        ,''';
         else
              Lv_String := 'DATAFILE ''';
         end if;

         Lv_String := LPAD(' ',5)        ||
                      Lv_String          ||
                      Lv_DF_File_Name    ||
                      ''' SIZE '         ||
                      (Lv_DF_Bytes)/1024 ||
                      'K REUSE' ;

         WRITE_OUT(Lv_Lineno, Lv_String);
         Lv_Lineno := Lv_Lineno + 1;

         if (Lv_DF_MaxExtend IS NOT NULL)
         then
              Lv_String := LPAD(' ',12)
||
                           'AUTOEXTEND ON NEXT '
||
                           (Lv_DF_Inc * Lv_Block_Size) / 1024
||
                           'K MAXSIZE '
||
                           (Lv_DF_MaxExtend * Lv_Block_Size) / 1024
||
                           'K';

             WRITE_OUT(Lv_Lineno, Lv_String);
             Lv_Lineno := Lv_Lineno + 1;
         end if;




   end loop;


   close DFILE_CURSOR;



   select MAX(Group#),

          MAX(Members)
     into Lv_Group,
          Lv_Members
     from SYS.V_$LOG;





   Lv_MaxLogFiles   := Lv_Group   * Lv_Members * 4;
   Lv_MaxLogMembers := Lv_Members * 2;



   Lv_Lineno := Lv_Lineno + 1;


   Lv_String := LPAD(' ',5) || 'MAXLOGFILES ' || Lv_MaxLogFiles;
   WRITE_OUT(Lv_Lineno,Lv_String);



   Lv_Lineno := Lv_Lineno + 1;


   Lv_String := LPAD(' ',5) || 'MAXLOGMEMBERS ' || Lv_MaxLogMembers;
   WRITE_OUT(Lv_Lineno,Lv_String);



   Lv_Lineno := Lv_Lineno + 1;


   WRITE_OUT(Lv_Lineno,LPAD(' ',5) || 'MAXLOGHISTORY 160');



   Lv_Lineno := Lv_Lineno +1;


   WRITE_OUT(Lv_Lineno,LPAD(' ',5) || 'MAXDATAFILES 255');



   Lv_Lineno := Lv_Lineno + 1;


   WRITE_OUT(Lv_Lineno,LPAD(' ',5) || 'MAXINSTANCES 1');



   select log_Mode

     into Lv_LogMode
     from SYS.V_$DATABASE;





   Lv_Lineno := Lv_Lineno + 1;


   WRITE_OUT(Lv_Lineno,LPAD(' ',5) || Lv_LogMode);



   select Value

     into Lv_Nchar_CharacterSet
     from V$NLS_PARAMETERS




    where UPPER(parameter) = 'NLS_NCHAR_CHARACTERSET';



   Lv_Lineno := Lv_Lineno + 1;


   Lv_String := LPAD(' ',5) || 'NATIONAL CHARACTER SET ' ||
Lv_Nchar_CharacterSet;


   WRITE_OUT(Lv_Lineno,Lv_String);



   select Property_Value

     into Lv_Dbtimezone
     from DATABASE_PROPERTIES




    where UPPER(property_name) = 'DBTIMEZONE';



   Lv_Lineno := Lv_Lineno + 1;


   Lv_String := LPAD(' ',5) || 'SET TIME_ZONE = ''' || Lv_Dbtimezone
|| '''';


   WRITE_OUT(Lv_Lineno,Lv_String);




   select Value

     into Lv_CharacterSet
     from V$NLS_PARAMETERS




    where UPPER(parameter) = 'NLS_CHARACTERSET';




   Lv_Lineno := Lv_Lineno + 1;

   Lv_String :=  LPAD(' ',5)     ||
                'CHARACTER SET ' ||
                 Lv_CharacterSet ||
                 ';';




   WRITE_OUT(Lv_Lineno,Lv_String);

   Lv_Lineno := Lv_Lineno + 1;
   WRITE_OUT(Lv_Lineno, '  ');
   Lv_Lineno := Lv_Lineno + 1;
   WRITE_OUT(Lv_Lineno, 'Rem -----------------------------------');


   Lv_Lineno := Lv_Lineno + 1;


   WRITE_OUT(Lv_Lineno, 'Rem Create a Temporary Rollback Segment');
   Lv_Lineno := Lv_Lineno + 1;

   WRITE_OUT(Lv_Lineno, 'Rem -----------------------------------');


   Lv_Lineno := Lv_Lineno + 1;


   WRITE_OUT(Lv_Lineno, '  ');




   Lv_Lineno := Lv_Lineno + 1;


   WRITE_OUT(Lv_Lineno, 'Create Rollback Segment rb_temp;');
   Lv_Lineno := Lv_Lineno + 1;


   WRITE_OUT(Lv_Lineno, 'Alter  Rollback Segment rb_temp Online;');
   Lv_Lineno := Lv_Lineno + 1;


   WRITE_OUT(Lv_Lineno, 'commit;');



   if (INSTR(Lv_DF_File_Name,'/',1) > 0)
   then


       Lv_Platform_Slash := '/';


   else


       Lv_Platform_Slash := '\';


   end if;


   Lv_Lineno := Lv_Lineno + 1;
   WRITE_OUT(Lv_Lineno, '  ');
   Lv_Lineno := Lv_Lineno + 1;
   WRITE_OUT(Lv_Lineno, 'Rem -----------------------------------');


   Lv_Lineno := Lv_Lineno + 1;


   WRITE_OUT(Lv_Lineno, 'Rem Start catalog.sql script');
   Lv_Lineno := Lv_Lineno + 1;

   WRITE_OUT(Lv_Lineno, 'Rem -----------------------------------');


   Lv_Lineno := Lv_Lineno + 1;


   WRITE_OUT(Lv_Lineno, '  ');


   Lv_String := '@$ORACLE_HOME'    ||
                Lv_Platform_Slash ||
                'rdbms'           ||
                Lv_Platform_Slash ||
                'admin'           ||
                Lv_Platform_Slash ||
                'catalog.sql;';




   Lv_Lineno := Lv_Lineno + 1;


   WRITE_OUT(Lv_Lineno, Lv_String);


   Lv_Lineno := Lv_Lineno + 1;


   WRITE_OUT(Lv_Lineno, 'commit;');


   WRITE_OUT(Lv_Lineno, 'Rem -----------------------------------');


   Lv_Lineno := Lv_Lineno + 1;


   WRITE_OUT(Lv_Lineno, 'Rem Start catproc.sql script');
   Lv_Lineno := Lv_Lineno + 1;

   WRITE_OUT(Lv_Lineno, 'Rem -----------------------------------');


   Lv_Lineno := Lv_Lineno + 1;


   WRITE_OUT(Lv_Lineno, '  ');


   Lv_String := '@$ORACLE_HOME'    ||
                Lv_Platform_Slash ||
                'rdbms'           ||
                Lv_Platform_Slash ||
                'admin'           ||
                Lv_Platform_Slash ||
                'catproc.sql;';




   Lv_Lineno := Lv_Lineno + 1;


   WRITE_OUT(Lv_Lineno, Lv_String);


   Lv_Lineno := Lv_Lineno + 1;


   WRITE_OUT(Lv_Lineno, 'commit;');


   Lv_Lineno := Lv_Lineno + 1;
   WRITE_OUT(Lv_Lineno, '  ');
   Lv_Lineno := Lv_Lineno + 1;
   WRITE_OUT(Lv_Lineno, 'Rem -----------------------------------');


   Lv_Lineno := Lv_Lineno + 1;


   WRITE_OUT(Lv_Lineno, 'Rem Create Tablespaces');
   Lv_Lineno := Lv_Lineno + 1;

   WRITE_OUT(Lv_Lineno, 'Rem -----------------------------------');


   Lv_Lineno := Lv_Lineno + 1;


   WRITE_OUT(Lv_Lineno, '  ');



   open TSPACE_CURSOR;


   loop

      fetch TSPACE_CURSOR
      into  Lv_TS_Text;
      exit when TSPACE_CURSOR%NOTFOUND;

      Lv_Lineno := Lv_Lineno + 1;
      WRITE_OUT(Lv_Lineno,Lv_TS_Text);




  end loop;


  close TSPACE_CURSOR;



  select Property_Value

     into Lv_Default_Temp_Tablespace
     from DATABASE_PROPERTIES




    where UPPER(property_name) = 'DEFAULT_TEMP_TABLESPACE';



  Lv_Lineno := Lv_Lineno + 1;


  Lv_String := ' ALTER DATABASE DEFAULT TEMPORARY TABLESPACE ' ||
Lv_Default_Temp_Tablespace || ' ;';


  WRITE_OUT(Lv_Lineno,Lv_String);



   Lv_Lineno := Lv_Lineno + 1;
   WRITE_OUT(Lv_Lineno, '  ');
   Lv_Lineno := Lv_Lineno + 1;
   WRITE_OUT(Lv_Lineno, 'Rem -----------------------------------');


   Lv_Lineno := Lv_Lineno + 1;


   WRITE_OUT(Lv_Lineno, 'Rem Alter sys and system ');
   Lv_Lineno := Lv_Lineno + 1;

   WRITE_OUT(Lv_Lineno, 'Rem -----------------------------------');


   Lv_Lineno := Lv_Lineno + 1;


   WRITE_OUT(Lv_Lineno, '  ');



   open TEMP_SPACE_CURSOR;


   loop

      fetch TEMP_SPACE_CURSOR
      into  Lv_TE_Username,
            Lv_TE_Deflt_Tspace,
            Lv_TE_Temp_Tspace;
      exit when TEMP_SPACE_CURSOR%NOTFOUND;

      Lv_String := 'Alter user '          ||
                   Lv_TE_Username         ||
                 ' default tablespace '   ||
                   Lv_TE_Deflt_Tspace     ||
                 ' temporary tablespace ' ||
                   Lv_TE_Temp_Tspace      ||
                   ';';
      Lv_Lineno := Lv_Lineno + 1;
      WRITE_OUT(Lv_Lineno,Lv_String);




  end loop;


  close TEMP_SPACE_CURSOR;



   Lv_Lineno := Lv_Lineno + 1;



   WRITE_OUT(Lv_Lineno, '  ');


   Lv_Lineno := Lv_Lineno + 1;

   WRITE_OUT(Lv_Lineno, 'Rem -----------------------------------');


   Lv_Lineno := Lv_Lineno + 1;


   WRITE_OUT(Lv_Lineno, 'Rem Create Rollback segments');
   Lv_Lineno := Lv_Lineno + 1;

   WRITE_OUT(Lv_Lineno, 'Rem -----------------------------------');


   Lv_Lineno := Lv_Lineno + 1;


   WRITE_OUT(Lv_Lineno, '  ');



   open ROLLBACK_SEGS_CURSOR;


   loop

      fetch ROLLBACK_SEGS_CURSOR
      into  Lv_RB_Segment_Name,
            Lv_RB_Owner,
            Lv_RB_Tablespace_Name,
            Lv_RB_Initial_Extent,
            Lv_RB_Next_Extent,
            Lv_RB_Min_Extents,
            Lv_RB_Max_Extents,
            Lv_RB_Pct_Increase,
            Lv_RB_Status,
            Lv_RB_Optsize;
      exit when ROLLBACK_SEGS_CURSOR%NOTFOUND;

      if (UPPER(Lv_RB_Owner) = 'PUBLIC')
      then
         Lv_String := 'CREATE PUBLIC ROLLBACK SEGMENT ';
      else
         Lv_String := 'CREATE ROLLBACK SEGMENT ';
      end if;

      Lv_String := Lv_String             ||
                   Lv_RB_Segment_Name;
      Lv_Lineno := Lv_Lineno + 1;
      WRITE_OUT(Lv_Lineno,Lv_String);

      Lv_String := '       '             ||
                   'TABLESPACE '         ||
                   Lv_RB_Tablespace_Name ;
      Lv_Lineno := Lv_Lineno + 1;
      WRITE_OUT(Lv_Lineno,Lv_String);

      Lv_Lineno := Lv_Lineno + 1;
      WRITE_OUT(Lv_Lineno,'           STORAGE (');

      Lv_String := '                 '            ||
                    'Initial       '              ||
                    (Lv_RB_Initial_Extent) / 1024 ||
                    ' K' ;
      Lv_Lineno := Lv_Lineno + 1;
      WRITE_OUT(Lv_Lineno,Lv_String);

      Lv_String := '                 '            ||
                    'Next          '              ||
                    (Lv_RB_Next_Extent) / 1024    ||
                    ' K' ;
      Lv_Lineno := Lv_Lineno + 1;
      WRITE_OUT(Lv_Lineno,Lv_String);

      Lv_String := '                 '            ||
                    'Minextents    '              ||
                    Lv_RB_Min_Extents;
      Lv_Lineno := Lv_Lineno + 1;
      WRITE_OUT(Lv_Lineno,Lv_String);

      Lv_String := '                 '            ||
                    'Maxextents    '              ||
                    Lv_RB_Max_Extents;
      Lv_Lineno := Lv_Lineno + 1;
      WRITE_OUT(Lv_Lineno,Lv_String);

      if ( Lv_RB_Optsize is NOT NULL)
      then
         Lv_String := '                  '           ||
                       'Optimal       '              ||
                       (Lv_RB_Optsize);
         Lv_Lineno := Lv_Lineno + 1;
         WRITE_OUT(Lv_Lineno,Lv_String);
      end if;

      Lv_Lineno := Lv_Lineno + 1;
      WRITE_OUT(Lv_Lineno,'          );');

      Lv_String := 'Alter Rollback Segment '         ||
                    Lv_RB_Segment_Name               ||
                    ' '                              ||
                    Lv_RB_STATUS                     ||
                    ';';
      Lv_Lineno := Lv_Lineno + 1;
      WRITE_OUT(Lv_Lineno,Lv_String);






  end loop;


  close ROLLBACK_SEGS_CURSOR;



exception


    WHEN NO_DATA_FOUND



    THEN



        DBMS_OUTPUT.PUT_LINE ('Reached end of file');



    WHEN UTL_FILE.INTERNAL_ERROR



    THEN



        DBMS_OUTPUT.PUT_LINE ('Internal File Error');



    WHEN UTL_FILE.INVALID_FILEHANDLE



    THEN



        DBMS_OUTPUT.PUT_LINE ('Invalid File Handle');



    WHEN UTL_FILE.INVALID_MODE



    THEN



        DBMS_OUTPUT.PUT_LINE ('Invalid file mode specified');



    WHEN UTL_FILE.INVALID_OPERATION



    THEN



        DBMS_OUTPUT.PUT_LINE ('Invalid file operation specified');



    WHEN UTL_FILE.INVALID_PATH



    THEN



        DBMS_OUTPUT.PUT_LINE ('Invalid file path specified');



    WHEN UTL_FILE.WRITE_ERROR



    THEN



        DBMS_OUTPUT.PUT_LINE ('A Write error occurred');



    WHEN UTL_FILE.READ_ERROR



    THEN



        DBMS_OUTPUT.PUT_LINE ('A file read error occurred');



    WHEN OTHERS



    THEN



        DBMS_OUTPUT.PUT_LINE ('An unknown exception occurred');
end;


/


set trimspool on


spool init.sql


select text


  from DBASE_TEMP


 order by Lineno


/


spool off
Received on Fri Mar 30 2007 - 15:33:41 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US