Passing variable from file to sql from script


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Passing variable from file to sql from script
# 15  
Old 11-04-2014
Tools

Quote:
Originally Posted by pallvi_mahajan
Hi All,

am getting confused, please let me know how i can do this

how
in_put number :-nvl('&1',0) can take all values from file.

---------- Post updated at 06:01 PM ---------- Previous update was at 04:17 PM ----------

Hi Ditto,

we have no permission in database to create external table. so is there any alternate way for this
Yes, look up SQL*Loader.
Again, Oracle related ... you should hop over to an Oracle forum to continue this question Smilie It's definitely an Oracle question, not unix.


Breakdown of:
ln_input number := nvl('&1',0);

'&1' ==> Captures the first parameter passed to the .sql script (ie the file count). Note, that this information is mostly useless to you, since you don't care how many rows are in the file, you just want to query a database table which matches each line in the file ...

nvl(<value>,0)

as mentioned by RudiC, if <value> is NULL or EMPTY (ie not provided), it uses the value "0" (numeric, not string).

Note that as written, it will then try to "convert" the string '&1' into a number.
If the input was "hello", then this will fail, since "hello" cannot convert to a number.

Again, however, after re-reading your original post ... this doesn't really help you at all Smilie You need External tables (preferred), or SQL*Loader if you can't use External Tables.
# 16  
Old 11-05-2014
Hi All,

I am asking this question again here because seems like i am doing some mistake in unix part. please help me what i need to do now on this, why am facing issue, it is passing variable, but sqlplus is not executing it.

and file has below content

Code:
 "'324324324'"
"'232423434'"
 "'232424444'"
 "'234324444'"

UNIX SCRIPT

Code:
 d=`paste -sd, file.txt` sqlplus -s username/password@servername @file.sql $d >> file1.txt
file.sql

.SQL FILE
Code:
 define id=&1 
select * from acct_table where acct in ($id) exit

But i am not getting any o/p, seems like variable is not extended in sql, please let me know where i did mistake.

Last edited by pallvi_mahajan; 11-05-2014 at 05:19 AM..
# 17  
Old 11-07-2014
Quote:
Originally Posted by pallvi_mahajan
But i am not getting any o/p, seems like variable is not extended in sql, please let me know where i did mistake.
unix variables generally are not available within SQL.

You need to pass them in to use them ...

Again, I'd definitely handle this using SQL*loader, or external tables, not via unix. Might work, but it'll be very difficult to maintain going forward. Using SQL*loader or such will give you a very clean/robust solution.

regarding that paste command, I don't think that command is setup properly .. it doesn't look right. Smilie However, not being familiar with the paste command myself, I'm not sure how to suggest changing it.

Have you tried to do this via SQL*loader yet? I think you'll find it very very simple:

1) create a table to hold the incoming data in Oracle.
2) create a .ctl file mapping the data file to the table.
3) invoke sql*loader.

There, your data is in the database, run your query.
# 18  
Old 11-07-2014
I don't quite understand why you are suggesting SQL*loader for this task. There's nothing to load/insert Smilie

The OP just wants to run a single SELECT statement which will include some values from a file, that's all.

Something like
Code:
SELECT * FROM SOMETABLE WHERE SOMECOLUMN IN ( <insert comma-delimited values from a file here> );

What the paste command does was explained in post #6 (https://www.unix.com/302923563-post6.html). Check manual page of paste for details: man paste

Btw. I think the problem is solved. Also check the associated parallel thread (How to pass parameter from file to sqlplus in UNIX?).
# 19  
Old 11-07-2014
Quote:
Originally Posted by junior-helper
I don't quite understand why you are suggesting SQL*loader for this task. There's nothing to load/insert Smilie
Well, that's why I suggested external tables first Smilie

But from a DB standpoint, I see the data as needed in the database, hence the load. Agreed, he didn't explicitly ask how to load it, but it's implied on how he wants to use the data.

As a learning experience, I tried playing with paste a bit based on those earlier posts, and couldn't get it working in Oracle, hence my comments on "still not sure about this" Smilie

Just going by what I know is a basic, and robust solution:

1) load data into a table.
2) use data in table in query.

since in Oracle, it's very "odd" to use data in a query "Directly" from a file (other than perhaps an external table).
This User Gave Thanks to Ditto For This Post:
# 20  
Old 11-07-2014
I see! I think I finally understand what you are talking about Smilie

Loading the data from the file using SQL*loader into a table "newtable" and then performing the select statement like

Code:
SELECT * FROM sometable WHERE somecolumn IN (SELECT number FROM newtable);

Right?

Btw. paste won't work in Oracle like DEFINE won't work on UNIX/Linux shell.
# 21  
Old 11-07-2014
Quote:
Originally Posted by junior-helper
I see! I think I finally understand what you are talking about Smilie

Loading the data from the file using SQL*loader into a table "newtable" and then performing the select statement like

Code:
SELECT * FROM sometable WHERE somecolumn IN (SELECT number FROM newtable);

Right?

Btw. paste won't work in Oracle like DEFINE won't work on UNIX/Linux shell.
Yep Smilie (sorry if I didn't make that clearer earlier Smilie sometimes you get so used to things, you "assume" they're obvious .. Smilie )
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Passing string from SQL to a BASH script

OS Solaris 10, DB oracle 10g Hello, We currently have a BASH script that runs and moves image files from a remote server to the local db server. A snippet of the code shows that we are picking up all Images that are 'mtime -1' some code... for file in `ssh user@10.200.200.10 'find... (3 Replies)
Discussion started by: JonP
3 Replies

2. Shell Programming and Scripting

Passing variable to sql

How to pass variable to sql file. Im tryin in two ways, Method 1: my.sql select * from table where col1 = '$1' and col2 = 'text'; Method 1execute: dbaccess database my.sql $var Method2: select * from table col1 in (`cat inputfile`) and col2 = 'text'; method... (2 Replies)
Discussion started by: Roozo
2 Replies

3. Shell Programming and Scripting

Passing a string variable from Unix to Sql Plus

Hi Guys, I am trying to pass a string variable from Unix shell script to sqlplus as a parameter. I have tried using single quotes with the variable name but it does not work. Please help me with it. I am using BASH. My code: Your help is much appreciated. Thanks, shil (2 Replies)
Discussion started by: infintenumbers
2 Replies

4. UNIX for Dummies Questions & Answers

SQL Script to use variable value from input file

Here is the requirement, When I run the "run file KSH (sql)", it should substitute '${pCW_Bgn_DT}' with 201120 and '${pCW_End_DT}' with 201124 Input File ---------- $ cat prevwk.dat 201124 20110711 run file KSH (sql) ------------------ In this file, I want to use the variables... (1 Reply)
Discussion started by: shanrice
1 Replies

5. Shell Programming and Scripting

Replace a var in sql file with shell script variable

I have a requirement where i have a sql file (filetext.sql). This file contains a variable ss_code. Now in a shell script im trying to replace the variable ss_code with a value contained in the shell script variable MTK_DC..tried the below in the script MTK_DC="mit,cit,bit" OUT=`awk -v... (4 Replies)
Discussion started by: michaelrozar17
4 Replies

6. UNIX for Advanced & Expert Users

Passing Hash variable in to sql query in perl

Hi Everyone, Can anyone help me how do i call hash variable in to sql query in perl. Please see the script below i have defined two Hash %lc and %tab as below $lc{'REFF'}='V_RES_CLASS'; $lc{'CALE'}='V_CAP_CLASS'; $lc{'XRPD'}='V_XFMR_CLASS'; $tab{'V_RES_CLASS'}='V_MFR_SERS';... (6 Replies)
Discussion started by: jam_prasanna
6 Replies

7. Shell Programming and Scripting

passing arguments to sql script

Hi Gurus, i have one requirement in unix script, i have a file called abc.txt in that few lines are there with the empid, i need to read each line and pass to .sql script. ex: abc.txt 2345 2346 1243 1234 i need to pass these arguments to .sql script rom unix ex: select * from... (1 Reply)
Discussion started by: Devendar
1 Replies

8. 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 : runsql.sh "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

9. UNIX for Advanced & Expert Users

passing value to a variable in a SQL

Hi Folks, This is a small chunk of the bigger problem which i am facing and some help here will help me resolve the rest of the issue. Problem is that i need to pass the value of a variable from a shell script to a SQL query (infact a lot of SQL's) i have the following solution but somehow... (4 Replies)
Discussion started by: kamitsin
4 Replies

10. 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
Login or Register to Ask a Question