I have created a "Stored Procedure" called "pdac_update_hicn.sql", and i want to call this SP in an shell script, and execute it.

Please let me know as to what command should i use so that this Stored Procedure (SP) executes.

I used this shell script and it ran ok. But it did not execute the procedure. It just created the procedure. Am i wrong somewhere here?
The shell script is :

#!/usr/bin/ksh
export ORACLE_BASE=/oracle
export ORACLE_HOME=/oracle/product/11.2.0
export LIBPATH=$ORACLE_HOME/lib32:$ORACLE_HOME/lib:/usr/lib
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib
export TNS_ADMIN=$ORACLE_HOME/network/admin
export PATH=$PATH:$ORACLE_HOME/bin
export userName=`cat /PDAC/loaduser`
export ORACLE_SID=`cat /PDAC/loadsid`
/usr/local/pl/perlencrypt.pl -k /kda1/pdacloaduser -d /sdr1/pdacloaduser | sqlplus $userName @/PDAC/scripts/pdac_update_hicn
echo "The Procedure completed successfully"

I thought it was just:

 
echo "exec pdac_update_hicn( args );" | sqlplus . . . .

Can you please provide me with a Shell script, which executes a "Stored Procedure".

It is certainly possible to write a script to execute all of your Oracle scripts, stored procedures, etc. I developed one, mainly to run scripts non-interactively, ie: from cron.

I'd provide it, but it has grown to a couple thousand lines. But, perhaps I could provide assistance in some key areas.

Features of the script I wrote:

• will run a file, directory containing files, or file containing filenames to run
• accepts many options and parameters on the command line
• creates log files
• emails the output
• provides the output in plain text, HTML, or HTML tables

I also wrote a 'global' version, to run a script on every Oracle instance I have.

my_ora
   Usage:
         my_ora -I instance {-D | -f | -n}
         my_ora [-U user] [-P passfile] -I instance -f file_containing_sql_statements.sql [oracle positional paramters]
         my_ora [-U user] [-P passfile] -I instance -D directory_of_files_containing_sql_statements
         my_ora [-U user] [-P passfile] -I instance -n file_containing_list_of_files_apply

   Required Parameters:
         -I
         one of: D, f or n

   Optional Parameters:
         [-U user] [-P passfile] [-s schema] [-e email_address{,email_address}] [-O]
         [[-d] [-v] | [-q]] [-S] [-T]
         [-H ORACLE_HOME] [-L NLS_LANG] [-m] [-o]

   Where:
         -a
            Run the scripts "as sysdba"
         -e email_address
            address to email the output to
         -F
            Disable fkeys before and enable fkeys after.
         -H ORACLE_HOME
            Overrides or sets ORACLE_HOME.
         -I instance
            Required. The Oracle instance name
         -L NLS_Lang
            Override NLS_LANG setting. Default: american_america.we8mswin1252
         -l log sub dir or release number
            Specify a token, a sub-directory will be made to contain the logs. Use the release number. ie. ER002.
         -m
            Send email only if an error occured.
         -O
            Override instance / host requirement. Normally you may only run a script against an instance that is on your current UNIX server.
         -o output_format
            Default: plain_text
         -P passfile
            A file containing the user's password. Default is ~/.db_pass_USER_INSTANCE
            Must be one of: csv, fancy, html
         -U user
            Oracle user. ie. purdym. Default is your unix ID.
         -s schema
            Alters session to set current_schema.

      Input Options:

         -D directory of release to apply
            The fully qualified name of a directory containing the release to be applied. SQL files must be in a sub dir called sql.
         -f file_containing_sql_statements.sql
            A text file with sql commands to be executed inside oracle.
         -n file
            A file containing file names, one per line, in order, of sql files to be applied.

   Options that affect only how the script runs:
         -d
            Enable debug mode. Variable contents will be printed. May be specified more than once.
         -h
            Prints this help screen.
         -q
            quiet. Limits output to the essentials.
            Removes: row[s]* selected
                     SQLUSER@INSTANCE>
         -S
            Runs sqlplus in silent mode.
         -T
            Testing mode. NO actual work will be done.
         -v
            Verbose mode. May be specified more than once.

   Notes:
         One, and only one of: -f, -D or -n is required.
         -f must be the last command line paramter. Everything after -f will be ignored by this script.
         Pay special attention to NLS_LANG.

If you get peeved at sql*plus, which almost seems to be written to stifle use in shell applications, being slow and cantankerous with fiddly configurations, you can go to jisql and JDBC.

Jisql - a Java based interactive SQL application

Oracle JARs are free. I also use SQuirreL + JDBC as my GUI everywhere.