[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 458
  • Last Modified:

script

If we want to run a script...just copy and paste in sql prompt will take care of it not..
0
tomvv
Asked:
tomvv
1 Solution
 
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
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now