Posted on 2012-09-15
Last Modified: 2012-09-19
If we want to run a script...just copy and paste in sql prompt will take care of it not..
Question by:tomvv
    LVL 37

    Expert Comment

    by:Gerwin Jansen
    If you mean that you want to run an sql query from a shell script simply put the sql in a file and call it myquery.sql for example.

    Then create a shell script like this:

    sqlplus user/password@database @myquery.sql

    and the script will run and your query will run.

    Is this what you mean?
    LVL 16

    Expert Comment

    by:Swadhin Ray
    You can also use something like below :

    Save this below code as and give the grants (Read , Execute )to the user or  group from where you want to run this script.
    # Export your Oracle details as below 
    export ORACLE_BASE=/opt/app/oracle
    export ORACLE_SID=<You Oracle SID>
    # Here I am using Oracle 11.1.o version you can change it as per your database version
    export ORACLE_HOME=$ORACLE_BASE/product/11.1.0  
    export PATH=$ORACLE_HOME/bin:$PATH
    # Running a sql query on linux
    # Here I trying to get the SYSDATE from Oracle you can change the query as per your need
    l_var=$(sqlplus -S USER@$ORACLE_SID/password <<EOF
    SELECT sysdate FROM dual;
    # check if no records found or not  
    if [ -z "$l_var" ]; 
      echo "No rows returned from database"
      exit 0
      echo $l_var

    Open in new window

    The method is also correct as per gerwinjansen but it is useful if you are using a PLSQL block and if you are having any DBMS Output then you should set the serveroutput on on the SQL script which you are trying to call.
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    >>...just copy and paste in sql prompt will take care of it not..

    Copy and paste will place the commands in the command buffer and execute them as they are terminated with the ';' or a '/'.

    This does have some limitations.  Mainly the text buffer.  Your script might have more text than can be stored in the buffer.

    Executing the script like mentioned above is the correct way.
    LVL 16

    Accepted Solution

    The mentioned scripts are to be run on Linux OS.

    The script is a shell script which will internally call SQL Plus and execute the statement what ever you want.

    You just need to copy and save it as and then provide the grant to execute it. Then you can execute it by :


    Open in new window

    Then you get the result.
    LVL 37

    Expert Comment

    by:Gerwin Jansen
    chmod +x ./

    Open in new window

    (to make executable)

    Author Closing Comment

    both the commets were helpful
    LVL 37

    Expert Comment

    by:Gerwin Jansen
    When  you say 'both' were helpful, I would think you'd split points between 2 comments. Or are the 2 comments you refer to in the comment you awarded? If you want to split, just use the Request Attention button on top. Thanks.

    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

    Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
    Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
    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 videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    746 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

    14 Experts available now in Live!

    Get 1:1 Help Now