SQL> create or replace function myproc( unix_par in varchar2) return varchar2
as
l_return varchar2(20);
begin
select unix_par into l_return from dual;
return l_return;
end;
 /
Function created.

DECLARE
  RetVal varchar2(200);
  unix_parm varchar2(200);
BEGIN
  unix_parm:='&1';
  RetVal := MYPROC (unix_parm);
  DBMS_OUTPUT.Put_Line('RetVal = ' || RetVal);
END;
/

i=`echo $ORACLE_SID`
sqlplus user/psw @myscript.sql $i

SQL*Plus: Release 9.2.0.6.0 - Production on Fri Feb 23 09:54:00 2007

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

old   5:   unix_parm:='&1';
new   5:   unix_parm:='utv3';

PL/SQL procedure successfully completed.

i=`echo $ORACLE_SID`
sqlplus user/psw <<-EOF
set serveroutput on
DECLARE
  RetVal varchar2(200);
BEGIN
  RetVal := MYPROC('${i}');
  DBMS_OUTPUT.Put_Line('RetVal = ' || RetVal);
END;
/
EOF

i=`echo $ORACLE_SID`
sqlplus user/psw <<-EOF
set serveroutput on
var  RetVal char(20);
exec  :RetVal := MYPROC('${i}');
print RetVal
exit
EOF

create the shell script with input from a user

echo '################################################################ '
echo ' Enter the runtime parameter'
read par1
echo ' Enter the runtime parameter no2'
read par2
echo '################################################################ '
sqlplus user/psw @myscript.sql $par1 $par2

DECLARE
  unix_par1 varchar2(20);
  unix_par2 varchar2(20);
BEGIN
  unix_par1:='&1';
  unix_par2:='&2';
  MYPROCEDURE(unix_par1,unix_par2);
END;
/

SQL>  create or replace procedure myprocedure(unix_par1 varchar2,unix_par2 varchar2)
  2  is
  3  begin
  4   dbms_output.put_line('My First Parameter:'||unix_par1);
  5  dbms_output.put_line(' My Second Parameter:'||unix_par2);
  6  end;
  7  /

Procedure created.

 
$ORACLE_HOME/bin/sqlplus -s /@myscript.sql $data
exit
EOF

$ORACLE_HOME/bin/sqlplus -s /@myscript.sql $data <<-EOF
exit
EOF

set serveroutput on size 1000000
$ORACLE_HOME/bin/sqlplus -s /nolog << EOF
$ORACLE_HOME/bin/sqlplus -s /@myscript.sql $data << EOF
connect $ORACLE_USER/$ORACLE_PASSWORD@$DB_NAME
exit
EOF

$ORACLE_HOME/bin/sqlplus -s /nolog <<-EOF
            exit
            EOF

export CONNSTRING=$ORACLE_USER/$ORACLE_PASSWORD@$DB_NAME

$ORACLE_HOME/bin/sqlplus -s $CONNSTRING /@myscript.sql $data <<-EOF
set serveroutput on size 1000000
exit
EOF

"Accid_Handle.ksh" 40 lines, 1472 characters
echo `date` : Begin Process Accid Handle[ Accid_Handle.ksh ]
echo '################################################################ '
echo ' Enter the WTN'
read data
echo '################################################################ '
export ORACLE_USER=Swb
export ORACLE_PASSWORD=swbt3st
export DB_NAME=yteprod
export LOG_DIR=/var/log/pbiace
export PATH=/usr/local/pbiace/current/bin

if [ ! $ORACLE_USER ] ; then
echo `date` : ERROR: ORACLE_USER environment variable WAS NOT established
echo `date` : -------------------------------- END $filename ---------------------------------------
exit 2
fi

if [ ! $ORACLE_PASSWORD ] ; then
echo `date` : ERROR: ORACLE_PASSWORD environment variable WAS NOT established
echo `date` : -------------------------------- END $filename ---------------------------------------
exit 3
fi

if [ ! $DB_NAME ] ; then
echo `date` : ERROR: DB_NAME environment variable WAS NOT established
echo `date` : -------------------------------- END $filename ---------------------------------------
exit 4
fi

if [ ! $ORACLE_HOME ] ; then
echo `date` : ERROR: ORACLE_HOME environment variable WAS NOT established
echo `date` : -------------------------------- END $filename ---------------------------------------
exit 5
fi

$ORACLE_HOME/bin/sqlplus -s /nolog <<-EOF
export CONNSTRING=$ORACLE_USER/$ORACLE_PASSWORD@$DB_NAME
$ORACLE_HOME/bin/sqlplus -s $CONNSTRING /@myscript.sql $data <<-EOF
set serveroutput on size 1000000
exit
EOF

"Accid_Handle.ksh" 40 lines, 1472 characters
echo `date` : Begin Process Accid Handle[ Accid_Handle.ksh ]
echo '################################################################ '
echo ' Enter the WTN'
read data
echo '################################################################ '
export ORACLE_USER=Swb
export ORACLE_PASSWORD=swbt3st
export DB_NAME=yteprod
export LOG_DIR=/var/log/pbiace
export PATH=/usr/local/pbiace/current/bin

export CONNSTRING=$ORACLE_USER/$ORACLE_PASSWORD@$DB_NAME

if [ ! $ORACLE_USER ] ; then
echo `date` : ERROR: ORACLE_USER environment variable WAS NOT established
echo `date` : -------------------------------- END $filename ---------------------------------------
exit 2
fi

if [ ! $ORACLE_PASSWORD ] ; then
echo `date` : ERROR: ORACLE_PASSWORD environment variable WAS NOT established
echo `date` : -------------------------------- END $filename ---------------------------------------
exit 3
fi

if [ ! $DB_NAME ] ; then
echo `date` : ERROR: DB_NAME environment variable WAS NOT established
echo `date` : -------------------------------- END $filename ---------------------------------------
exit 4
fi

if [ ! $ORACLE_HOME ] ; then
echo `date` : ERROR: ORACLE_HOME environment variable WAS NOT established
echo `date` : -------------------------------- END $filename ---------------------------------------
exit 5
fi

$ORACLE_HOME/bin/sqlplus -s $CONNSTRING /@myscript.sql $data <<-EOF
set serveroutput on size 1000000
exit
EOF

$ORACLE_HOME/bin/sqlplus -s $CONNSTRING /@myscript.sql $data <<-EOF
set serveroutput on size 1000000
exit
EOF

sqlplus -s $CONNSTRING @myscript.sql

set serveroutput on
DECLARE
data varchar2(20);
BEGIN
data:='&1';
ACCID_HANDLE(data);
END;
/

$ORACLE_HOME/bin/sqlplus -s $CONNSTRING <<-EOF
set serveroutput on size 1000000
exec ACCID_HANDLE('${data}')
exit
EOF