CODEWITHSUNDEEP
sqlplsqloracle11gsqlplus
David
David

Reputation: 141

How to create a table by appending a string to a command line variable in PL/SQL?

Currently I'm using

    def PARAM1=&1
    def PARAM2=&2
    def PARAM3=&3
    DECLARE &PARAM4 VARCHAR(20);
    BEGIN
        PARAM4 := &PARAM1 || '2';
        CREATE TABLE &param4 AS SELECT * FROM &param1 Order By &param2,&param3;
    END;
    /
    exit;

I'm trying to run this but its giving me ar error as invalid SQL Statement. I would call procedure from shell using SQLPlus.

Upvotes: 0

Views: 179

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

You don't need the anonymous PL/SQL block; within that you'd have to use execute immediate because you can't do DDL as a native command.

This is the equivalent of what you have I think:

def PARAM1=&1
def PARAM2=&2
def PARAM3=&3
def PARAM4=&PARAM1.2
CREATE TABLE &param4 AS SELECT * FROM &param1 Order By &param2,&param3;
exit;

The period between &PARAM1 and the fixed value of 2 you're appending marks the end of the variable name. Without it, &PARAM12 would be interpreted as a new parameter. I'm guessing that might have been what was causing you a problem.

If I run that with parameters t4, id, name I get (with set verify on):

old:def PARAM1=&1
new:def PARAM1=t4
old:def PARAM2=&2
new:def PARAM2=id
old:def PARAM3=&3
new:def PARAM3=name
old:def PARAM4=&PARAM1.2
new:def PARAM4=t42
old:CREATE TABLE &param4 AS SELECT * FROM &param1 Order By &param2,&param3
new:CREATE TABLE t42 AS SELECT * FROM t4 Order By id,name

So the final statement it executes is:

CREATE TABLE t42 AS SELECT * FROM t4 Order By id,name

Or you can do the same thing without the explicit define commands:

CREATE TABLE &&1.2 AS SELECT * FROM &&1 Order By &&2,&&3;
exit;

The order by clause doesn't make much sense in a CTAS statement, since the data in the new table has no inherent order, but it's valid syntax.

If you really wanted to do it in an anonymous block, you'd have to just wrap that inside an execute immediate, but there would be no point unless you were planning some other logic around it:

BEGIN
    execute immediate 'CREATE TABLE &param1.2 AS SELECT * FROM '
      || ' &param1 Order By &param2,&param3';
END;
/

Upvotes: 3

Related Questions