Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

PL/SQL error when parsing shell variables

SOLVED
Go to solution
Thierry Lethuillier
Occasional Visitor

PL/SQL error when parsing shell variables

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
8 REPLIES
A. Clay Stephenson
Acclaimed Contributor

Re: PL/SQL error when parsing shell variables

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.

If it ain't broke, I can fix that.
A. Clay Stephenson
Acclaimed Contributor

Re: PL/SQL error when parsing shell variables

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.

If it ain't broke, I can fix that.
Brian Crabtree
Honored Contributor

Re: PL/SQL error when parsing shell variables

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
Occasional Visitor

Re: PL/SQL error when parsing shell variables

Unfortunately I use Oracle 8.0.5.

Is there another way to use Shell variables under PL/SQL ?
Christian Gebhardt
Honored Contributor
Solution

Re: PL/SQL error when parsing shell variables

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
Honored Contributor

Re: PL/SQL error when parsing shell variables

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
Honored Contributor

Re: PL/SQL error when parsing shell variables

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

Chris
Thierry Lethuillier
Occasional Visitor

Re: PL/SQL error when parsing shell variables

Thank you for your hints Christian !

Thierry