| how to run sqlplus command for stored proc? [message #374860] |
Sat, 07 July 2001 16:22  |
Patrick Hatcher
Messages: 1
Registered: July 2001
|
Junior Member |
|
|
Howdy,
I'm fairly new to Unix and Oracle, so forgive me if this a basic question:
I've figured out how create a shell file to logon to and run a .sql file. How do I run a previously stored Proc?
So far I have this:
cat display
echo "run the SQL Plus command to update the tables
sqlplus user/pwd
TIA
Patrick
|
|
|
|
| Re: how to run sqlplus command for stored proc? [message #374872 is a reply to message #374860] |
Mon, 09 July 2001 07:59  |
Cindy
Messages: 88
Registered: November 1999
|
Member |
|
|
Try this:
#!/bin/sh -
ORACLE_HOME=/u01/app/oracle/product/8.1.7
PATH=/usr/bin:/bin:/usr/local/bin:/etc:/usr/local:$ORACLE_HOME/bin:.
SQL_ID=Your_database_lonin/Your_database_password
ORACLE_SID=fodb
export ORACLE_HOME
export PATH
export ORACLE_SID
export SQL_ID
/bin/cat > /tmp/your_file_name.sql.$$ << BLOCK_NAMESQL
SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 450
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET TERM OFF
spool Your_data_filename.dat
SELECT column1, column2, column3, so on and so on
FROM Table_name
WHERE condition1
AND condition2
AND condition3;
SPOOL OFF
EOF
BLOCK_NAMESQL
/bin/echo "Start extracting data: `date`." > Log_filename.log
sqlplus -s $SQL_ID<<-EOF>> Log_filename.log 2>&1
@ /tmp/your_file_name.sql.$$
EOF
/bin/echo "Finish extracting data: `date`. "
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
