Oracle - Pass Parameters from shell script to stored procedures

Posted on 2007-07-26
Last Modified: 2013-12-19
I am producing two parameters from a webpage (jsp,java) and am sending them to a shell script.

I want to call them in a stored procedure from the shell script as below:
                  sqlplus $USERNAME @/opt/sql/removehols_sql $1 $2    <---- calling params correctly?

The "removehols_sql" then calls the actual procedure that utilises the parameters:
                 call full_removehols();             <---------- how do I pass the parameters from "removehols_sql"?

If I hardcode in parameters it works fine
                 call full_removehols('TYPE1','13-07-2007');

can somebody please tell me how I can pass the parameters through corectly as this is quite urgent.
Question by:davystocks
    LVL 8

    Accepted Solution

    Within your sql script you should have:
    call full_removehols('&1', '&2');

    and then call
    sqlplus $USERNAME @/opt/sql/removehols_sql TYPE1 13-07-2007

    Note that you should include &1 within single quotes. That is needed if $1 equals TYPE1. If $1equals 'TYPE1' (ie quotes are included) then you should remove them from the script.
    LVL 4

    Author Comment

    Perfect. Thanks for your prompt and accurate response YANN0S

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
    This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
    This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now