- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- PL/SQL error when parsing shell variables
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-28-2002 07:07 AM
тАО10-28-2002 07:07 AM
This is my simplified code;
# /usr/bin/ksh
MY_TABLE="ING"
SITE_NUM=99
...
$ORACLE_HOME/bin/sqlplus -s dummy/dummy <
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
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-28-2002 07:32 AM
тАО10-28-2002 07:32 AM
Re: PL/SQL error when parsing shell variables
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-28-2002 07:32 AM
тАО10-28-2002 07:32 AM
Re: PL/SQL error when parsing shell variables
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-28-2002 03:44 PM
тАО10-28-2002 03:44 PM
Re: PL/SQL error when parsing shell variables
----------------
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-29-2002 05:22 AM
тАО10-29-2002 05:22 AM
Re: PL/SQL error when parsing shell variables
Is there another way to use Shell variables under PL/SQL ?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-29-2002 05:50 AM
тАО10-29-2002 05:50 AM
SolutionIt 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 <
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-29-2002 05:53 AM
тАО10-29-2002 05:53 AM
Re: PL/SQL error when parsing shell variables
When using
sqlplus dummy/dummy <
EOF
everyone can see your password with command "ps -efa"
Better way:
sqlplus <
EOF
Chris
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-29-2002 05:56 AM
тАО10-29-2002 05:56 AM
Re: PL/SQL error when parsing shell variables
Excuse me, you have to delete "@c" in the message above, it's my private database.
Chris
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-29-2002 07:08 AM
тАО10-29-2002 07:08 AM
Re: PL/SQL error when parsing shell variables
Thierry