1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Execute Immediate- Missing or Invalid option error

Discussion in 'SQL PL/SQL' started by sharo, Jul 16, 2012.

  1. sharo

    sharo Active Member

    Messages:
    6
    Likes Received:
    1
    Trophy Points:
    65
    Hello,

    I am trying to use Execute immediate to create a table in a procedure, though the procedure compiles successfully it is throwing me an error "Missing or invalid option " when i try to run it.

    The syntax that i am using is :

    EXECUTE IMMEDIATE 'create table emp as'
    ||'select id,name from emp@'
    ||p_sourcedb
    ||'where id = '
    ||p_Id;

    p_sourcedb and p_id are the IN parameters that are passed in the procedure.

    Please let me know what is wrong with the code.

    Thanks
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Much is wrong here; first your statement construction is incorrect as you have placed your concatenation operators in the wrong location thus Oracle never sees the remainder of the statement and errors as 'create table emp as' is incomplete. Second you should build a string in a variable, rather than simply building the text in the execute immediate statement -- you can build the statement without a variable but then you cannot verify the statement that was constructed to troubleshoot any problems you may experience. The example below shows what you see after your execute immediate statement attempt and what I have done to ensure the statement is complete and processed:

    Code (SQL):
    SQL> CREATE OR REPLACE PROCEDURE exe_imm_mess (p_id IN NUMBER) IS
      2  BEGIN
      3  EXECUTE IMMEDIATE 'create table emp2 as'
      4  ||'select empno,ename from emp '
      5  ||'where empno = '
      6  ||p_Id;
      7
      8  END;
      9  /
     
    PROCEDURE created.
     
    SQL>
    SQL> EXEC exe_imm_mess(7900);
    BEGIN exe_imm_mess(7900); END;
    *
    ERROR at line 1:
    ORA-00922: missing OR invalid OPTION
    ORA-06512: at "BING.EXE_IMM_MESS", line 3
    ORA-06512: at line 1

    SQL>
    SQL> DESC emp2
    ERROR:
    ORA-04043: object emp2 does NOT exist

    SQL>
    SQL> CREATE OR REPLACE PROCEDURE exe_imm_mess (p_id IN NUMBER) IS
      2          sqlstmt varchar2(400);
      3  BEGIN
      4          sqlstmt:='create table emp2 as select empno, ename from emp where empno = '||p_id;
      5
      6          EXECUTE immediate sqlstmt;
      7  END;
      8  /
     
    PROCEDURE created.
     
    SQL>
    SQL> EXEC exe_imm_mess(7900);
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> DESC emp2

     Name                                      NULL?    TYPE
     ----------------------------------------- -------- ----------------------------
     EMPNO                                     NOT NULL NUMBER(4)
     ENAME                                              VARCHAR2(10)
     
    SQL>