Passing variable from file to sql from script

Hi Friend,

I have one file in which some number are mentioned and number of lines are vary every time

And i need to pass that number to my sql command from script.

Suppose i have file acc.txt

45456546456
45464564565
67854353454
67657612132

Number of records are vary every time.

and i need to check the status of all these acc in our database.

select * from tablename where acc in ( all records which are in acc.txt)

my unix script is

sqlplus -s username/password @acc.sql 

Please let us know how i can pass all acc records which are present in acc.txt from script to sql

i know i can pass parameter like this, but that i know that i can do if i know number of record is fixed. Please let us know how i can do this

well, first up, don't pass the password on the command line like that, it can be "seen" by anyone using ps -ef ..

There's a lot of other options:

1) externally identified id.
2) sqlplus -s /nolog
connect user@password
...
etc.

Anyway, to your question:

first, stuff the count into a unix variable:

fileCOUNT=`wc -l acc.txt`

then when you invoke your sql command, pass it in:

sqlplus -s /nolog << EOF         
   connect <user>/<password>
   @acc.sql   ${fileCOUNT}
   exit
EOF

select &1 from dual;

declare
  ln_input   number := nvl('&1',0);
begin
end;
/

but how i can call this in .sql, how $filecount replace in .sql file

select * from tablename where acc in (
45456546456, 45464564565, 67854353454, 67657612132)

Try

select * from tablename where acc in ($(paste -sd, file))

HI Rudic,

what does

($(paste -sd, file))

do.

It will convert

45456546456
45464564565
67854353454
67657612132

in file to

45456546456,45464564565,67854353454,67657612132

Hi Rudic,

is this command, can i read some tutorial on this, please let me know where i can study this, i did google, not getting anything on this.

If installed on your system, read man paste .

how are you trying to use it?
That sql you provided doesn't have any "line counts" .. so where do you want to use the line count in that query ?

i have oracle

---------- Post updated at 04:03 PM ---------- Previous update was at 03:51 PM ----------

Hi Ditto,

We have passed $file_count variable to .sql file.

what does this command do

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

In that case, this becomes an Oracle related question, not Unix related one

Read up on External tables here:

Tables and Table Clusters

You can see an example of an external table here:
CREATE TABLE
and the full syntax is on that page.

Further questions should probably go into an Oracle forum, not a Unix one

Good luck!

---------- Post updated at 16:07 ---------- Previous update was at 16:04 ----------

FYI - I don't think it works in Oracle - I just tried, not getting it to work, and it's installed

nvl provides a "default" value for missing i.e. NULL variables, so if &1 has not been assigned to, number will become 0.

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

How about starting over, reformulating the request and posting the script and related files?

Yes, look up SQL*Loader.
Again, Oracle related ... you should hop over to an Oracle forum to continue this question 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 You need External tables (preferred), or SQL*Loader if you can't use External Tables.

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

 "'324324324'"
"'232423434'"
 "'232424444'"
 "'234324444'"

UNIX SCRIPT

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

.SQL FILE

 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.

unix variables generally are not available within SQL.

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

Again, I'd definitely handle this using SQLloader, or external tables, not via unix. Might work, but it'll be very difficult to maintain going forward. Using SQLloader 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. 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.

I don't quite understand why you are suggesting SQL*loader for this task. There's nothing to load/insert

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

Something like

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

What the paste command does was explained in post #6 (http://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 (http://www.unix.com/programming/252645-how-pass-parameter-file-sqlplus-unix.html\).

Well, that's why I suggested external tables first

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"

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).

I see! I think I finally understand what you are talking about

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

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.