PL/SQL error when parsing shell variables

[Thierry Lethuillier]

Does somebody know how to use Shell variables in a PL/SQL embeded script ?

This is my simplified code;

# /usr/bin/ksh
MY_TABLE="ING"
SITE_NUM=99

...
$ORACLE_HOME/bin/sqlplus -s dummy/dummy <DECLARE
Tablesp all_all_tables.tablespace_name%TYPE;
BEGIN

SELECT unique tablespace_name INTO Tablesp FROM all_all_tables
WHERE table_name=${MY_TABLE}${SITE_NUM} ;

...
RENAME ${MY_TABLE}${SITE_NUM} TO tmp_${MY_TABLE}${SITE_NUM} ;

...
END;
/
EXIT
EOF
...

The PL/SQL parser fails parsing the RENAME instruction;

RENAME ING99 TO tmp_ING99 ;
*
ERROR line 8:
ORA-06550: Line 8, column 8:
PLS-00103: Found "ING99" but expected one of the following: := . ( @ % ;


Thanks in advance,
Thier

[A. Clay Stephenson]

Your best option is to use echo to build the command 'on the fly' and output to a temp file. You then use the temp file as input to sqlplus.

e.g.
TDIR=${TMPDIR:-/var/tmp}
S1=${TDIR}/X${$}_1.sql

echo "select unique tablespace_name into Tablesp FROM " > ${S1}
echo "all_all_tables WHERE table_name=${MY_TABLE}${SITE_NUM}"

.....

You need to do a rm -f ${S1} when finished to remove the temp file. If you need to echo special characters like $ then you will need to escape them with backslashes.

[A. Clay Stephenson]

Your best option is to use echo to build the command 'on the fly' and output to a temp file. You then use the temp file as input to sqlplus.

e.g.
TDIR=${TMPDIR:-/var/tmp}
S1=${TDIR}/X${$}_1.sql

echo "select unique tablespace_name into Tablesp FROM " > ${S1}
echo "all_all_tables WHERE table_name=${MY_TABLE}${SITE_NUM};" >> ${S1}

.....

You need to do a rm -f ${S1} when finished to remove the temp file. If you need to echo special characters like $ then you will need to escape them with backslashes.

[Brian Crabtree]

You will need to parse the SQL statment on the fly as you go. In Oracle 8i, you can do this with the following:
----------------
In your declaration area:
sqlstatement varchar2(400);

In your code:
sqlstatement:='rename ${MY_TABLE}${SITE_NUM} TO tmp_${MY_TABLE}${SITE_NUM}';
execute immediate sqlstatement;
----------------

You might want to consider either creating a procedure for this, or passing the variables in at the command line and using a script, rather than the passing the script and variables through stdin.

Hope this helps,

Brian

[Thierry Lethuillier]

Unfortunately I use Oracle 8.0.5.

Is there another way to use Shell variables under PL/SQL ?

[Christian Gebhardt · Accepted solution]

Hi Thierry

It is not a problem with parsing shell-variables, there are some errors in your script:

- First: in the select-statement you have to youse single quotations for the where-clause
- Second: In PL/SQL you cannot use DDL-Commands directly, "Rename ..." is a DDL Command.
You can use a PL-SQL Package named DBMS_SQL

This script works:

# /usr/bin/ksh
MY_TABLE="ING"
SITE_NUM=99

$ORACLE_HOME/bin/sqlplus -s dummy/dummy@c <DECLARE

stmnt VARCHAR2(2000);
cur INTEGER;
ret INTEGER;
Tablesp all_all_tables.tablespace_name%TYPE;

BEGIN

SELECT unique tablespace_name INTO Tablesp FROM all_all_tables
WHERE table_name='${MY_TABLE}${SITE_NUM}' ;

stmnt:='RENAME ${MY_TABLE}${SITE_NUM} TO tmp_${MY_TABLE}${SITE_NUM}' ;
cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cur, stmnt, dbms_sql.v7);
ret := DBMS_SQL.EXECUTE(cur);
DBMS_SQL.CLOSE_CURSOR(cur);

END;
/
EXIT
EOF

Chris

[Christian Gebhardt]

Another hint:
When using
sqlplus dummy/dummy <
EOF

everyone can see your password with command "ps -efa"

Better way:
sqlplus <dummy/dummy

EOF

Chris

[Christian Gebhardt]

Hi
Excuse me, you have to delete "@c" in the message above, it's my private database.

Chris

[Thierry Lethuillier]

Thank you for your hints Christian !

Thierry