3 Replies Latest reply on Jun 8, 2009 4:39 PM by 705355

    error creating simple procedure

    705355
      I don't get why the below sp complains of this:

      Errors for PROCEDURE LOADTABLE:

      LINE/COL ERROR
      -------- -----------------------------------------------------------------
      3/1 PLS-00103: Encountered the symbol "DECLARE" when expecting one of
      the following:
      begin function pragma procedure subtype type <an identifier>
      <a double-quoted delimited-identifier> current cursor delete
      exists prior external language
      The symbol "begin" was substituted for "DECLARE" to continue.

      17/4 PLS-00103: Encountered the symbol "end-of-file" when expecting
      one of the following:
      ( begin case declare end exception exit for goto if loop mod
      null pragma raise return select update while with

      LINE/COL ERROR
      -------- -----------------------------------------------------------------
      <an identifier> <a double-quoted delimited-identifier>
      <a bind variable> << continue close current delete fetch lock
      insert open rollback savepoint set sql execute commit forall
      merge pipe purge



      CREATE OR REPLACE PROCEDURE loadtable AS

      DECLARE
      i pls_integer ;
      sql_stmt VARCHAR2(4000);
      all_names VARCHAR2(4000);
      all_names2 VARCHAR2(4000);
      TYPE typ_coltab IS TABLE OF dba_tab_columns%ROWTYPE INDEX BY pls_integer;
      coltab_table typ_coltab ;
      coltab_table2 typ_coltab ;
      coltab_rec dba_tab_columns%ROWTYPE ;
      CURSOR cur is SELECT *
      FROM dba_tab_columns where owner='MYSCHEMA' and table_name='CONFIG' ;

      BEGIN
      NULL;
      END;
      /


      Thanks,
      floyd
        • 1. Re: error creating simple procedure
          SeánMacGC
          Hello, you don't need the DECLARE there:
          CREATE OR REPLACE PROCEDURE loadtable AS
          
          i pls_integer ;
          sql_stmt VARCHAR2(4000);
          all_names VARCHAR2(4000);
          all_names2 VARCHAR2(4000);
          TYPE typ_coltab IS TABLE OF dba_tab_columns%ROWTYPE INDEX BY pls_integer;
          coltab_table typ_coltab ;
          coltab_table2 typ_coltab ;
          coltab_rec dba_tab_columns%ROWTYPE ;
          CURSOR cur is SELECT *
          FROM dba_tab_columns where owner='MYSCHEMA' and table_name='CONFIG' ;
          
          BEGIN
          NULL;
          END;
          /
          
          
          Procedure created.
          Edit : DECLARE is not needed within procedures (or functions or packages) but may be need in anonymous PL/SQL blocks - oracle expects the declarations within procedures, but needs to be explicitly told about them within anonymous blocks.
          • 2. Re: error creating simple procedure
            SanjayRs
            {color:blue}Spot the the Difference, and you will find your answer{color}
            SQL> ed
            Wrote file buffer.sql
            
              1  --CREATE OR REPLACE PROCEDURE loadtable AS
              2  DECLARE
              3  i pls_integer ;
              4  sql_stmt VARCHAR2(4000);
              5  all_names VARCHAR2(4000);
              6  all_names2 VARCHAR2(4000);
              7  TYPE typ_coltab IS TABLE OF dba_tab_columns%ROWTYPE INDEX BY pls_integer;
              8  coltab_table typ_coltab ;
              9  coltab_table2 typ_coltab ;
             10  coltab_rec dba_tab_columns%ROWTYPE ;
             11  CURSOR cur is SELECT *
             12  FROM dba_tab_columns where owner='MYSCHEMA' and table_name='CONFIG' ;
             13  BEGIN
             14  NULL;
             15* END;
             16  /
            
            PL/SQL procedure successfully completed.
            
            Elapsed: 00:00:00.39
            SQL> ed
            Wrote file buffer.sql
            
              1  CREATE OR REPLACE PROCEDURE loadtable AS
              2  --DECLARE
              3  i pls_integer ;
              4  sql_stmt VARCHAR2(4000);
              5  all_names VARCHAR2(4000);
              6  all_names2 VARCHAR2(4000);
              7  TYPE typ_coltab IS TABLE OF dba_tab_columns%ROWTYPE INDEX BY pls_integer;
              8  coltab_table typ_coltab ;
              9  coltab_table2 typ_coltab ;
             10  coltab_rec dba_tab_columns%ROWTYPE ;
             11  CURSOR cur is SELECT *
             12  FROM dba_tab_columns where owner='MYSCHEMA' and table_name='CONFIG' ;
             13  BEGIN
             14  NULL;
             15* END;
            SQL> /
            
            Warning: Procedure created with compilation errors.
            SS
            1 person found this helpful
            • 3. Re: error creating simple procedure
              705355
              Thank you all.
              I thought that was the case, but then when I took the DECLARE out, I got a DECLARATION type error. It turns out my IS TABLE OF declaration had an issue also.

              Much obliged once again.

              floyd