SQL*Plus Read a File to Extract Data

My UNIX is very rusty and I don't have access to any UNIX machine to test this but see if this might work:

sqlplus -s $LOGIN << % >> $OutPutFile
set pagesize 30000
set linesize 30000
set wrap off
set head on
set feed off
set colsep ","
column U_TIME heading 'BeginDate'
column KEY heading 'ID'
column VALUE heading 'Value'
column QUALITY heading 'Quality'
column DATA_TYPE heading 'TYPE'
alter session set nls_date_format='MM/DD/YYYY HH24:MI';
select U_TIME, KEY, VALUE, QUALITY
from $DATA_VALUES_CURRMM 
where TIME >= sysdate - 6/24
and KEY in ( `cat keys.csv`
				)
and DATA_TYPE = 4	
order by U_TIME;
exit;
%

Select allOpen in new window

why not put the list in a table ?
you could also add a version column to it

i some replaces on your text:
',' replace with ,
', replace with ,'||
and added a , at the end

create table keys as 					
select 1 version, regexp_substr(d, '[^,]+', 1, level) value from 
   (select 
		'03006002,03019001,03019007,03019012,03019017,03019022,03019025,03030002,'||
                    '03030006,03030008,03030012,03031003,03031012,03031015,03031024,03031027,'||
                    '03031030,03039002,03039004,03040003,03040005,03041002,03053025,03054035,'||
                    '03065002,03065003,03065012,03065013,03065022,03065023,03065034,03065035,'||
                    '03065044,03065045,03065051,03065053,03065055,03065056,03065060,03065061,'||
                    '03065062,03075002,03084038,03088009,03148001,03149001,03150001,03151003,'||
                    '03152001,03153003,03153006,03160026,03161003,03162003,03163003,03164004,'||
                    '03164006,03164008,03164010,03165006,03165008,03179001,03184003,03185041,'||
                    '03186004,03186006,03187003,03190008,03194241,03194281,03205001,03206002,'||
                    '03208005,03208037,03208069,03222003,03222009,03223004,03223008,03224003,'||
                    '03225013,03229007,03236001,03236017,03237007,03237008,03237011,03237012,'||
                    '03237016,03237018,03237027,03237028,03237029,03237030,03239001,03252001,'||
                    '03252002,03252003,03252004,03252005,03252008,03252009,03252010,03252011,'||
                    '03252012,03252013,03252015,03252016,03252017,03252018,03252019,03252020,'||
                    '03252021,03252022,03252023,03252024,03252025,03252026,03252027,03252029,'||
                    '03252030,03252031,03252032,03252033,03252034,03252035,03252036,03252037,'||
                    '03252038,03252039,03252041,03252042,03252043,03252044,03252048,03252049,'||
                    '03252050,03252051,03252052,03253001,03013036,03017047,03018014,03027009,'||
                    '04199109,04199110,04200127,04201001,04201002,03050005,03050020,03050063,'||
                    '04201003,04201004,04201005,04201015,03069033,03084010,03086001,03086007,'||
                    '04208007,04237140,04237141,04237142,04237143,03088033,03157065,'||
                    '04237148,04250013,04250061,04252005,04252006,03157100,03160013,03160042,'||
					'04448014,03168001,03168005,03178027,03184004,03185039,03191004,03225029,'||
					'04160001,04201006,04201007,04201008,' d from dual) x
connect by level <= regexp_count(d, '[^,]');  					

Select allOpen in new window

and then ...

key in (select value from keys where version = 1)

Select allOpen in new window

Guys,

Thanks for the suggestions.  I will not be able to work on this until next week because I am leaving now to go out of town.  Have a great Thanksgiving.

Mr. Gruwez,
I would love to try your idea but we are not allowed to create tables at all (even temporary ones).

Slightwv,

I had to modify ( `cat keys.csv`) to ('cat keys.csv') because I got an invalid character error message.  I now get "no rows selected".  I tried using the complete path for the file as well.

Thanks

>>I had to modify ( `cat keys.csv`) to ('cat keys.csv') because I got an invalid character error

Normal single quotes makes it a string.  Back single quotes should have executed the command but again, my SHELL scripting is really old.

You might also try:
and KEY in ( $(cat keys.csv) )


Look around for command substitution examples:
https://www.gnu.org/software/bash/manual/html_node/Command-Substitution.html

I have tried the permeations below but received no joy.
KEY in ('cat /dir/aav.csv')       
KEY in $(cat aav.csv)                   
KEY in ($('cat /dir/aav.csv'))       
KEY in &('/dir/aav.csv')             
KEY in ("aav"."csv")                   
KEY in '/dir/aav.csv'                   
KEY in (`cat '/dir/aav.csv'`)       
KEY in (cat '/dir/aav.csv')       
KEY in (`cat /dir/aav.csv`)       

I get an invalid character with the $ and & signs.  Any suggestions?
Thanks

It took me a while to get a VM unix machine set up.

It works for me.

setup:

echo 1,2,3 > q.txt

Select allOpen in new window

Then my test shell script (I had to retype because I couldn't copy/paste from the VM):

#/bin/bash

sqlplus -s /nolog << EOF
conn user/password
select sysdate from dual where 1 in (`cat q.txt`);
EOF

Select allOpen in new window

When I run the script, I get the date.

Can you try the simple test case and see if it works for you?

I tried it and I get an "invalid character" under the first tick mark.

select sysdate from dual where 1 in (`cat q.txt`);
                                                                  *
00911: invalid character

>>00911: invalid character

Are you trying that in straight sqlplus?  If so, it should fail.  That is an invalid character in SQLPLUS but in the context of a shell script it should be evaluated before sqlplus executes.

You must execute the shell script as posted:
save it as q.sh
chmod 755 q.sh
./q.sh

Technically you can run it from a shell prompt but you must us the entire here document:

sqlplus -s /nolog << EOF
conn user/password
select sysdate from dual where 1 in (`cat q.txt`);
EOF

Select allOpen in new window

You just can't run it directly from sqlplus because you need the SHELL to interpret the cat command before passing everything to sqlplus.

slightwv,
You are right.  However, creating a shell script (or any type of executable) at my location takes an act of Congress (or close to it) because of security concerns (I do not make these rules, trust me).  I have been tasked in finding a way to read a .csv file from the SQLPLUS command prompt if it is possible.  I have searched the web and all I could find was at StackOverflow.  It talked about OPENROWSET with BULK.  However, the FORMATFILE part needs to create a table (if I understood what I was reading) first.  This I cannot do.  
Thanks

you are not allowed to create tables ON THAT DATABASE ?
has anybody forbid you to create tables on another database ?

i'd setup a db link on another database, create that table there
and then via table@dblink run the query

this way you do the reading only part on that database
and the configuration on another database.

from $DATA_VALUES_CURRMM@REMOTE

Select allOpen in new window

nobody has to break any rules, just bend them a little

I have made a mess of this post.  You are right about the BASH script and I will try your suggestion once I get permission to.  That will take some time though.

Thanks for your help.

You can test without a script.  No permissions necessary.

I was trying another option but I like what I already posed better.

While I was testing I noticed something I wanted to comment on:
"sqlplus -s $LOGIN << % >> $OutPutFile"

You should NEVER put the username and password on the sqlplus command line.  Any user that does a "ps" will be able to see the username and password.

While you are changing the script you should use the method I posted.  run sqlplus with the /nolog option and provide the connect inside sqlplus.

Great information.

Thanks