How to pass a shell variables to sql or pl/sql block?

Posted on 2011-10-18
Last Modified: 2012-05-12
In a shell script, I like to pass a shell variable (date parameter) to a sql block that will be used to update a table in a database. Please advise how I can pass a shell variable to a sql block.
Question by:IT_ETL
    LVL 37

    Accepted Solution

    Hi IT_ETL, you can use variables, like this:

    sqlplus -S ${user}/${pass} <<EOF
     SELECT * FROM tableName WHERE username=${var};

    Open in new window

    The <<EOF construction is know as a 'here' document.

    You can substitute your date variable (passed as a string I presume) with a to_date function.
    LVL 16

    Assisted Solution

    by:Swadhin Ray
    Check another good example on :


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

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

    Introduction A previously published article on Experts Exchange ("Joins in Oracle", makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
    Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

    759 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

    11 Experts available now in Live!

    Get 1:1 Help Now