Running a Stored Procedure from shell script.. | Unix Linux Forums | Shell Programming and Scripting

  Go Back    


Shell Programming and Scripting Post questions about KSH, CSH, SH, BASH, PERL, PHP, SED, AWK and OTHER shell scripts and shell scripting languages here.

Running a Stored Procedure from shell script..

Shell Programming and Scripting


Closed Thread    
 
Thread Tools Search this Thread Display Modes
    #1  
Old 12-13-2010
msrahman msrahman is offline
Registered User
 
Join Date: Oct 2010
Last Activity: 3 May 2011, 4:28 PM EDT
Location: Fargo, ND
Posts: 54
Thanks: 10
Thanked 0 Times in 0 Posts
Running a Stored Procedure from shell script..

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 :

Code:
#!/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"


Last edited by Franklin52; 12-13-2010 at 01:04 PM.. Reason: Please use code tags
Sponsored Links
    #2  
Old 12-13-2010
DGPickett DGPickett is offline Forum Advisor  
Registered User
 
Join Date: Oct 2010
Last Activity: 25 September 2014, 5:44 PM EDT
Location: Southern NJ, USA (Nord)
Posts: 4,422
Thanks: 8
Thanked 541 Times in 519 Posts
I thought it was just:

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

once the SP is installed.

PS: Put code in code tags (above left of the php icon) or get dinged by the moderator!
Sponsored Links
    #3  
Old 12-13-2010
msrahman msrahman is offline
Registered User
 
Join Date: Oct 2010
Last Activity: 3 May 2011, 4:28 PM EDT
Location: Fargo, ND
Posts: 54
Thanks: 10
Thanked 0 Times in 0 Posts
Shell script to execute a Stored Procedure

Can you please provide me with a Shell script, which executes a "Stored Procedure".
    #4  
Old 12-13-2010
purdym purdym is offline
Registered User
 
Join Date: Nov 2010
Last Activity: 1 November 2011, 11:16 AM EDT
Posts: 173
Thanks: 0
Thanked 19 Times in 19 Posts
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.




Code:
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.

Sponsored Links
    #5  
Old 12-15-2010
DGPickett DGPickett is offline Forum Advisor  
Registered User
 
Join Date: Oct 2010
Last Activity: 25 September 2014, 5:44 PM EDT
Location: Southern NJ, USA (Nord)
Posts: 4,422
Thanks: 8
Thanked 541 Times in 519 Posts
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.
Sponsored Links
Closed Thread

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

More UNIX and Linux Forum Topics You Might Find Helpful
Thread Thread Starter Forum Replies Last Post
How to execute the stored procedure from shell script dineshmurs Shell Programming and Scripting 2 04-18-2010 01:53 AM
Passing a value to stored procedure from unix shell script shirdi Shell Programming and Scripting 1 03-12-2010 04:03 AM
passing parameter 4m shell script to a DB stored procedure hema2026 Shell Programming and Scripting 0 11-16-2007 05:55 AM
Calling stored procedure from shell script dorisw Shell Programming and Scripting 3 06-12-2007 05:03 PM
calling stored procedure from shell script. priyamurthy2005 Shell Programming and Scripting 2 04-21-2005 07:10 PM



All times are GMT -4. The time now is 12:16 PM.