script

If we want to run a script...just copy and paste in sql prompt will take care of it not..
tomvvAsked:
Who is Participating?
 
Swadhin RaySenior Technical Engineer Commented:
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 filename.sh and then provide the grant to execute it. Then you can execute it by :

./filename.sh 

Open in new window


Then you get the result.
0
 
Gerwin Jansen, EE MVETopic Advisor Commented:
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?
0
 
Swadhin RaySenior Technical Engineer Commented:
You can also use something like below :

Save this below code as test.sh and give the grants (Read , Execute )to the user or  group from where you want to run this script.
#!/bin/sh
#---------------------------------------------------
# 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 LD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_PATH=$ORACLE_HOME/bin
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;

exit;
EOF)
# check if no records found or not  
if [ -z "$l_var" ]; 
 then
  echo "No rows returned from database"
  exit 0
else
  echo $l_var
fi

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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
slightwv (䄆 Netminder) Commented:
>>...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.
0
 
Gerwin Jansen, EE MVETopic Advisor Commented:
chmod +x ./filename.sh

Open in new window

(to make filename.sh executable)
0
 
tomvvAuthor Commented:
both the commets were helpful
0
 
Gerwin Jansen, EE MVETopic Advisor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.