Re: Calling St. Procedure in UNIX script, How ?

On Thu, 29 Nov 2001 22:16:49 GMT, "jane" <janeyiu_at_optonline.net> wrote:
>Right, of course one'll do that. possibly the most straight-forward way.
>Do you have a sample snippet to provide me ?
>i.e. if you have a UNIX variable $my_table (= dept)
>how do you invoke sql*plus and pass $my_table to a pl/sql procedure
>which you'd otherwise manually run in sql*plus as sql> exec
>check_table('dept')

my_table=dept

sqlplus -s $user/$passwd << SQLEND
exec check_table('$dept')
exit
SQLEND The << SQLEND is a 'here document' construct, it means take input from standard input until the string 'SQLEND' is found ('SQLEND' can be any string you like). So the bit between the two SQLENDs is the bit that gets passed to sqlplus

As another example, here is a shell script that takes a table name parameter and creates an empty comments script for that table:

#!/bin/ksh

user=scott
passwd=tiger

columns=`sqlplus -s $user/$passwd << SQLEND set head off feed off pages 0
SELECT column_name
FROM user_tab_columns
WHERE table_name = '$1';
exit
SQLEND` echo "COMMENT ON TABLE $table IS ' ';"

for column in $columns; do

    echo "COMMENT ON COLUMN $column IS ' ';" done Received on Thu Nov 29 2001 - 21:31:13 CST