Sponsored Content
Top Forums Shell Programming and Scripting Actual SQL instead of using a file from within a shell script Post 302839797 by durden_tyler on Friday 2nd of August 2013 11:04:06 PM
Old 08-03-2013
Ok, I tested the original mysql idea by blackrageous and it appears that something similar could be done for Oracle's sqlplus as well.
Using the "printf" built-in of Bash or print/printf of Ksh, you could do combine all commands together and pipe them to sqlplus.

In the following testcase, I have an Oracle procedure called "sp_test" in the "test" schema, that simply prints its input parameter value.

Code:
$ 
$ # In Bash
$ printf "set serveroutput on\n exec sp_test(p_yr_qtr => 4)" | sqlplus -s test/test
From procedure sp_test: input p_yr_qtr = 4

PL/SQL procedure successfully completed.

$ 
$

This could be incorporated in a shell script like so -

Code:
$ 
$ # display the content of the shell script
$ cat -n call_ora_proc.sh
     1    #!/usr/bin/bash
     2    export ORA_DBCU=test
     3    export ORA_DBCP=test
     4    export YR_QTR=4
     5    export spool_file=call_ora_proc.log
     6    printf "clear columns breaks computes
     7    set recsep off wrap off heading off pagesize 0 space 0 newpage 0 serveroutput on
     8    spool ${spool_file}
     9    exec sp_test(${YR_QTR});
    10    spool off
    11    exit" | $ORACLE_HOME/bin/sqlplus -s $ORA_DBCU/$ORA_DBCP 1>/dev/null
$ 
$ # run the shell script
$ . call_ora_proc.sh
$ 
$ # and check the log file created by it
$ cat call_ora_proc.log
From procedure sp_test: input p_yr_qtr = 4                                      

PL/SQL procedure successfully completed.

$ 
$

thereby avoiding a here-document.
This User Gave Thanks to durden_tyler For This Post:
 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

How to execute a .sql file with shell script

hi everybody... can anyone help me in executing the .sql file with shell scripting.... thanx in advance (2 Replies)
Discussion started by: abuanas
2 Replies

2. Shell Programming and Scripting

calling sql file from shell script

Hello everybody I need help calling sql file from shell script. Can anyone help me creating a small shell script which calls an sql file . The .sql file should contain some select statements like select emp_no from emp_table; select emp_id from emp_table; And the results should be... (6 Replies)
Discussion started by: dummy_needhelp
6 Replies

3. UNIX for Advanced & Expert Users

Calling sql file from shell script

Hi I have a shell script that call a sql file. The sql file will create a spool file. My requirement is, when ever i get an OS error like file not found. I have to log it in a log file. Could some who worked in a like scenario help me by giving the code sample. Many Thanks.. (1 Reply)
Discussion started by: chintapalli001
1 Replies

4. Shell Programming and Scripting

(Urgent):Creating flat file using sql script and sqlplus from UNIX Shell Script

Hi, I need help urgently for following issue. Pls help me to resolve this issue. I am calling sql script file(file1.sql) from UNIX Shell Script(script1.ksh) using sql plus and trying to create flat file that contains all records returned from SQL query in SQL script(file1.sql) I given... (6 Replies)
Discussion started by: praka
6 Replies

5. Shell Programming and Scripting

How to use sql data file in unix csv file as input to an sql query from shell

Hi , I used the below script to get the sql data into csv file using unix scripting. I m getting the output into an output file but the output file is not displayed in a separe columns . #!/bin/ksh export FILE_PATH=/maav/home/xyz/abc/ rm $FILE_PATH/sample.csv sqlplus -s... (2 Replies)
Discussion started by: Nareshp
2 Replies

6. Shell Programming and Scripting

calling a sql file in my shell script

Hi, I want to call a sql file in my shell script. see the below code:- if ] then ( isql -U${S_USER} -S${S_SERV} -w100 -b -h0 <<ENDSQL | sed -e "s/Password://" ${S_PWD} set nocount on go use ${S_DB} go // need to call a file name... (16 Replies)
Discussion started by: dazdseg
16 Replies

7. Shell Programming and Scripting

Calling sql file from shell script with parameters.

Hi, I am calling a sql file script.sql from shell script and passing few parameters also as shown below: sqlplus -S id/password @script.sql $param1 $param2 Now,In sql file I have to create a extract text file after querying oracle tables based on the parameters passed(param1,param2) as... (7 Replies)
Discussion started by: anil029
7 Replies

8. Shell Programming and Scripting

Shell script that will compare two config files and produce 2 outputs 1)actual config file 2)report

Hi I am new to shell scripting. There is a requirement to write a shell script to meet follwing needs.Prompt reply shall be highly appreciated. script that will compare two config files and produce 2 outputs - actual config file and a report indicating changes made. OS :Susi linux ver 10.3. ... (4 Replies)
Discussion started by: muraliinfy04
4 Replies

9. Shell Programming and Scripting

Unable to pass value from .Shell script to .SQL file

Hi All, I am new to shell script. I am trying to pass value from .sh file to .sql file . But I am able to run the .sql file from .sh file with values in sql file. But I am unable to pass the values from .sh file. can some one please help to resolve this. here is my .sh file s1.sh ... (4 Replies)
Discussion started by: reddy298599
4 Replies

10. Shell Programming and Scripting

Shell script to call sql file

hi , the below script contains sql query and after executed it sends the output of the query (output.txt) to an email body with conditional subject line based on the output of all_counts_match.txt. i want to make this script generic so that it can accept the sql file as parameter and can... (5 Replies)
Discussion started by: itzkashi
5 Replies
comb(1) 						      General Commands Manual							   comb(1)

NAME
comb - Combines Source Code Control System (SCCS) deltas SYNOPSIS
comb [-clist] [-os] [-pSID] file... The comb command writes to standard output a shell procedure for combining the specified deltas (SIDs) of an SCCS file and creating a new version of the file with fewer deltas. OPTIONS
Each option or group of options applies independently to each named file. Specifies a list of deltas (SIDs) that the shell procedure will preserve (see get -ilist). The procedure will combine all other deltas. Accesses the reconstructed file at the release of the delta to be created for each get -e generated; otherwise, accesses the reconstructed file at the most recent ancestor. Using the -o option may decrease the size of the reconstructed SCCS file. It may also alter the shape of the delta tree of the original file. Specifies the SID of the oldest delta for the resulting procedure to preserve. All older deltas are combined in the reconstructed file. Causes comb to gen- erate a shell procedure that, instead of combining the deltas, produces a report for each file, which lists the file name, size (in blocks) after combining, original size (also in blocks), and percentage change computed by the following formula: 100 * (original - combined) / original Run comb using this option before actually combining SCCS deltas in order to judge how much space will actually be saved by the com- bining process. DESCRIPTION
By piping the output of the comb command to a shell, or redirecting the output to a file and running that file as a shell procedure, you can reduce the size of your SCCS file. You can first see how much the file will be reduced by running the comb command with the -s option. If you specify a directory in place of file, the comb command performs the requested actions on all SCCS files (that is, those with file names with the s. prefix). If you specify a - (dash) in place of file, the comb command reads standard input and interprets each line as the name of an SCCS file. The comb command continues to take input until it reads End-of-File. If you do not specify any options, the comb command preserves only leaf deltas and the minimal number of ancestors needed to preserve the tree (see the delta command). The comb command may rearrange the shape of the tree deltas. The comb command may not save any space; in fact, it is possible for the reconstructed file to actually be larger than the original. EXAMPLES
To generate a report on how much space will be saved by combing all deltas older than SID 1.4 for SCCS file s.test.c, enter: comb -p1.4 -s s.test.c | sh To actually combine the deltas, enter: comb -p1.4 s.test.c | sh FILES
Temporary files. SEE ALSO
Commands: admin(1), cdc(1), delta(1), get(1), prs(1), rmdel(1), sact(1), sccs(1), sccsdiff(1), sccshelp(1), unget(1), val(1), what(1) Files: sccsfile(4) Programming Support Tools comb(1)
All times are GMT -4. The time now is 08:31 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy