Shell Programming and Scripting

Hi friends,

Need your help again to get a best approach for the below scenario.

I am previously having one shell script which accepts request_id/s as the command line argument.
single req_id arg= 1111
Multiple arg= 1111,2222,3333

which gets passed to the embedded sql inside to the in operator to update the status to inactive

Pseudo code for the same : (please ignore syntax error)

Code:

#! /bin/bash
set -vx

req_ids=$1

sqlplus -s xxxx/yyyy@zzzz  << EOF >> /grs/sql_logs
update table_name  set status='INACTIVE' where req_id in ($req_ids);
commit;
exit;
EOF

The above method was working like a charm for some small set of request_ids.
As presently I am getting a huge volume of request_ids(~1200) I fear whether the command line argument will allow a string of 5000bytes.
If at all it allows the in operator will not allow > 2499 bytes of data.

I have tried inserting the record into a temp table and run a for loop in sql but can't as i am not privileged to create temp table in prod env.
Also tried to pass the file as the command line (.csv file) argument then split the file into 300 records each by pivoting using awk command and process one by one file and remove them once processed but still restricted to create temp files in prod box.

Can you please focus some idea to deal with this situation without using temporary table and split method.

Many Thanks to all the members and UNIX.com

Do you really (need to) supply the IDs on the command line? How do you construct the line? How about creating an sql- file with several update lines for a max number of IDs? And then executing that?

Thanks Rudic. Good idea of creating multiple updates in a single SQL
File . but the ids are not constant. Everyday I am getting request 3-4 times with different sets of ids.

How do those IDs come in?

user provides in the form of txt file (single column) through mail. then Sent to the deployment team through some defined process.

However i have access to transfer the files to production box.

I am wondering whether it is possible to cut 300 lines every time pivot them to a string and pass to the sql.

say 900 ids are there then 3 loops of unix same update with different strings of ids will suffice.

I think the line length limitation has to do with parameters that are passed to the script and not what is in the script itself. You say there is a text file with arguments in a single column, so that means this?

Code:

$ cat file.txt
1111
2222
3333
....

?

If so, you could try something like this
Pass the filename a parameter to the script:

Code:

#! /bin/bash
set -vx

req_ids=( $(<"$1") )
IFS=,
sqlplus -s xxxx/yyyy@zzzz  << EOF >> /grs/sql_logs
update table_name  set status='INACTIVE' where req_id in ($(printf "%s" "${req_ids[*]}"));
commit;
exit;
EOF

Obviously this is just a principle. Your script would need exception handling, for example to test where the filename is passed as an argument and whether it exists.
IFS=, is used for the expansion of the array so that the values are separated by comma's. If you have more lines in your script then you'll probably want to save the contents of IFS and restore it afterwards..

Code:

oldIFS=$IFS
IFS=,
....
IFS=$oldIFS

Yes ! I meant that. I will simulate the solution you have provided in dev and post the result. Thanks for your time.