Results 1 to 5 of 5
  1. #1
    Join Date
    May 2011
    Posts
    1

    Question Unanswered: Error creating table

    This is pretty basic question to most of you, but for some reason i couldn't figure it out.


    I am trying to create a table in Oracle SQL Developer using store procedure. the procedure is as below

    CREATE OR REPLACE PROCEDURE CREATE_TABLE AS
    BEGIN
    CREATE table test(lname varchar(10));
    END CREATE_TABLE;

    However, it gives me compilation error as below

    Error(4,19): PLS-00103: Encountered the symbol "CREATE" when expecting one of the following: ( - + case mod new not null <an identifier> continue avg count current exists max min prior sql stddev sum variance execute forall merge time timestamp interval date pipe <an alternatively-quoted string literal with character set specification> <an alternat

    What could be the issue here. the same procedure works fine in Microsoft SQL mangament studio.


    Thanks & Regards

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Maybe you should stick with Windoze unless you are willing to learn the top of the line database (Oracle).
    You could begin by reading the fine Oracle manuals.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >What could be the issue here.
    DDL can not be directly executed inside PL/SQL procedures.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Oracle requires dynamic SQL in such cases (when DDL is to be executed from a stored procedure). Here's an example. Although it can be done with a few lines less, I thought that it might be interesting for you because statements you are about to run can be rather complex. If you simply EXECUTE IMMEDIATE it, many times it simply fails and you have no idea why. If you, on the other hand, store the whole statement into a variable, you can display it (tool I used is SQL*Plus, DBMS_OUTPUT.PUT_LINE displays the variable) and verify whether it is OK or not.

    Furthermore, if you pay attention to my L_VAR contents, you'll notice that the command does NOT end with a semi-colon. If you, however, put it there, you'll get the "invalid character" error.

    Note that user has to have the CREATE TABLE privilege granted (otherwise, you'd get the "insufficient privileges" error).
    Code:
    SQL> create or replace procedure create_table as
      2    l_var varchar2(200);
      3  begin
      4    l_var := 'create table test ' ||
      5             '  (lname varchar2(10))' ;
      6    dbms_output.put_line(l_var);
      7    execute immediate (l_var);
      8  end;
      9  /
    
    Procedure created.
    
    SQL> exec create_table;
    create table test   (lname varchar2(10))
    
    PL/SQL procedure successfully completed.
    
    SQL> desc test
     Name                          Null?    Type
     ----------------------------- -------- --------------------
     LNAME                                  VARCHAR2(10)
    
    SQL>

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Now the next question is why would you create a table in a procedure. Unlike t-sql, oracle almost never needs temporary tables
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •