Passing filename dynamically in SPOOL of SQL*PLUS in shell script

Login or Register for Dates, Times and to Reply

Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Passing filename dynamically in SPOOL of SQL*PLUS in shell script
# 1  
Java Passing filename dynamically in SPOOL of SQL*PLUS in shell script

Hi all,
I am executing shell script in which I am using SQLLDR
In this SQLLDR I am passing text file having PL/SQL script. This script will produce some formated output, this output I have to spool in another text file. Currently I have given this in script file as following

Spool folder/filename.txt
<PL/SQL block>

But now I dont want this filename.txt to be hardcoded, this should be dynamic. I mean it should be something like this

Spool folder/&1.txt
<PL/SQL block>

At runtime &1 should be substituted with the given string. i.e. through the input in shell script

Does anyone have idea how to implement this.

# 2  
Generate the script in a shell script, getting the file name from $1, and pipe it to sqlldr/sqlplus in the script.
# 3  
Try this...

Script :

$cat myscript.ksh


sqlplus -s  scott/tiger@oracle<<EOF
spool $file_name;
spool off;

You may call the script with the filename as a parameter , this should do the trick

$sh myscript.ksh  "/home/user/data/file.txt"

Login or Register for Dates, Times and to Reply

Previous Thread | Next Thread
Thread Tools Search this Thread
Search this Thread:
Advanced Search

Test Your Knowledge in Computers #217
Difficulty: Medium
Network operating systems can be embedded in a router or hardware firewall that operates the functions in the physical layer (layer 1).
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Append date to sql*plus spool (log) file in shell script

SQL*Plus version : OS : Oracle Linux 6.5 SQL*Plus is a client application to connect to oracle database. The log file for this tool is generated via spool command as shown below. I am trying to append date ( $dateString ) to spool file as shown below. $ cat #!/bin/bash... (4 Replies)
Discussion started by: kraljic
4 Replies

2. Shell Programming and Scripting

Passing the result of an anonymous pl/sql block to a shell script

Hello, Here is the code i have written to get the count of a plsql query back to the unix. function checkforCOIDs { countcheck=`sqlplus -s $1/$2@$3 whenever oserror exit sql.oscode rollback whenever sqlerror exit sql.sqlcode rollback set serverout on size 2000; set head off feedback off... (2 Replies)
Discussion started by: santosh2eee
2 Replies

3. Programming

create a spool file based on values passed from korn shell to sql script

this is my issue. 4 parameters are passed from korn shell to sql script. parameter_1= varchar2 datatype or no value entered my user. parameter_2= number datatype or no value entered my user. parameter_3= number datatype or no value entered my user. parameter_4= number datatype or no... (5 Replies)
Discussion started by: megha2525
5 Replies

4. Shell Programming and Scripting

Help required in passing multiple arguments from a shell script to a pl/sql block

Hi, hope everyone are fine. Please find my issue below, and I request your help in the same In a configuration file, i have a variable defined as below TEST = 'One','Two','Three' I am trying to pass this variable in to a sql script which is define in a pl/sql block as follows, In the... (1 Reply)
Discussion started by: ramakanth_burra
1 Replies

5. Shell Programming and Scripting

Passing Filename as a Parameter to Another script

Hi, I have a requirement to write a script file( to read the file from the folder say /usr1/profiles/active and pass the file name as a parameter to another script file say ( which would subsitute this parameter. The will have a line like the one below ... (1 Reply)
Discussion started by: ravisg
1 Replies

6. Shell Programming and Scripting

passing values from sql to shell script

Hi guyz, Posting a thread after a long time. I want to pass two variables to unix shell script from sql script. Note: I am calling sql script from unix script. sql script has 2 variables one is the return code for status of program run and second one email flag. I don't know how to capture... (3 Replies)
Discussion started by: sachin.gangadha
3 Replies

7. Shell Programming and Scripting

Passing argumnets from shell script to sql

hi I all , I have sql statment in my shell script , I pass two argument to the script I need to pass the this two arguments to the sql statment example : "1" "2" sql : updat tables_x set y=0 where A=:x should subsituted by "1" and B=:y shuold subsituted bt "2"... (1 Reply)
Discussion started by: habuzahra
1 Replies

8. Shell Programming and Scripting

passing parameter from Shell-script to Sql-script

Dear Friends, Please help me to achieve the following: I want to pass one parameter from Shell-script to Sql-script. Example: My is calling report.sql like this: /bin/sqlplus /reports.sql And My report.sql is calling many Stored-Procedures like this: exec... (0 Replies)
Discussion started by: subodhbansal
0 Replies

9. Shell Programming and Scripting

Passing PL/SQL variable value to Shell Varible

Hi my UNIX Friends, Im calling some SQL scripts through Unix Shell scripting. How do I export the value of PL/SQL variable value into a Unix shell script variable? Also could any one inform me about the 'search' and 'cut' utility of PL/SQL (like 'grep' and 'cut' in Shell scripting). ... (10 Replies)
Discussion started by: ganapati
10 Replies

10. Linux

Passing variables to sql from batch shell in linux

Hi, I need to put this command in a batch shell. sqlplus -s user/password @test.sql and in the test.sql I have this command select * from pbempl where pebempl_id = $1; How I can pass the variable $1 from the batch shell??? Thanks (2 Replies)
Discussion started by: rama71
2 Replies

Featured Tech Videos