how to resolve:- PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: begin end function pragma procedure

Need to alter the table by adding one column how can we write correct syntax for altering the table inside the package? it can be with procedure or function?

I tried with procedure and getting this error

CREATE OR REPLACE PACKAGE BODY temp IS

  PROCEDURE prc_proc_add_column(
                                 table_name  IN user_tab_columns.TABLE_NAME%TYPE,
                                 p_return    OUT NUMBER,
                                 p_message   OUT VARCHAR2
                               ) IS   
  BEGIN
      EXECUTE IMMEDIATE 'ALTER TABLE table_name ADD col_name NUMBER';
     p_return  := 0;
     p_message := 'Succesful';
    EXCEPTION WHEN OTHERS THEN p_return  := SQLCODE;
                               p_message := SQLERRM;        
  END prc_proc_add_column;

then getting this error:-

PLS-00103: Encountered the symbol "end-of-file" when expecting one of      
the following: begin end function pragma procedure

>Solution :

Barbaros already commented what is wrong with code you posted, as far as syntax is concerned.

Sure, you can do it, but – generally speaking – it isn’t the best idea to modify data model dynamically.

When you’re here, why wouldn’t you modify that code so that you could provide not only table name, but also column name and its datatype?

Note that – when using dynamic SQL on DDL statements – you have to concatenate names into the statement; you can’t use bind variables (not that you did …), and – again generally speaking – it wouldn’t hurt if you take care about possible SQL injection.

Here’s an example.

Package specification:

SQL> CREATE OR REPLACE PACKAGE temp
  2  IS
  3     PROCEDURE prc_proc_add_column (
  4        p_table_name   IN     user_tab_columns.table_name%TYPE,
  5        p_column_name  IN     user_tab_columns.column_name%TYPE,
  6        p_datatype     IN     user_tab_columns.data_type%TYPE,
  7        p_return          OUT NUMBER,
  8        p_message         OUT VARCHAR2);
  9  END;
 10  /

Package created.

Package body:

SQL> CREATE OR REPLACE PACKAGE BODY temp
  2  IS
  3     PROCEDURE prc_proc_add_column (
  4        p_table_name   IN     user_tab_columns.table_name%TYPE,
  5        p_column_name  IN     user_tab_columns.column_name%TYPE,
  6        p_datatype     IN     user_tab_columns.data_type%TYPE,
  7        p_return          OUT NUMBER,
  8        p_message         OUT VARCHAR2)
  9     IS
 10        l_str  VARCHAR2 (200);
 11     BEGIN
 12        l_str :=
 13              'ALTER TABLE '
 14           || DBMS_ASSERT.sql_object_name (p_table_name)
 15           || ' ADD '
 16           || DBMS_ASSERT.simple_sql_name (p_column_name)
 17           || ' '
 18           || DBMS_ASSERT.simple_sql_name (p_datatype);
 19
 20        EXECUTE IMMEDIATE l_str;
 21
 22        p_return := 0;
 23        p_message := 'Succesful';
 24     EXCEPTION
 25        WHEN OTHERS
 26        THEN
 27           p_return := SQLCODE;
 28           p_message := SQLERRM;
 29     END prc_proc_add_column;
 30  END;
 31  /

Package body created.

Testing:

SQL> CREATE TABLE test
  2  (
  3     id   NUMBER
  4  );

Table created.

SQL> DECLARE
  2     l_ret  VARCHAR2 (200);
  3     l_msg  VARCHAR2 (200);
  4  BEGIN
  5     temp.prc_proc_add_column ('test',
  6                               'salary',
  7                               'number',
  8                               l_ret,
  9                               l_msg);
 10     DBMS_OUTPUT.put_line (l_ret || ': ' || l_msg);
 11  END;
 12  /

PL/SQL procedure successfully completed.

SQL> desc test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 SALARY                                             NUMBER

SQL>

Leave a Reply