SQL*Plus Read a File to Extract Data

The attached snippet is from a BASH script where I use Oracle SQL*Plus Ver. 11.2 to extract the last 6 hours of values for the KEYs listed.  The variable $DATA_VALUES_CURRMM is the database table for the current month that I pass to sqlplus.  What I would like to do is put the KEYs in a .csv file and pass the file to sqlplus using a BASH script.  The sqlplus portion of the script is attached.  The Experts helped me with this script before.  Any other suggestions would be appreciated.
SQLSnippet.sql
dwortmanEngineering AssociateAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
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;
%

Open in new window

Geert GOracle dbaCommented:
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, '[^,]');  					

Open in new window


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

Open in new window

dwortmanEngineering AssociateAuthor Commented:
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.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

dwortmanEngineering AssociateAuthor Commented:
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
slightwv (䄆 Netminder) Commented:
>>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
dwortmanEngineering AssociateAuthor Commented:
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
slightwv (䄆 Netminder) Commented:
It took me a while to get a VM unix machine set up.

It works for me.

setup:
echo 1,2,3 > q.txt

Open 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

Open 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?
dwortmanEngineering AssociateAuthor Commented:
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
slightwv (䄆 Netminder) Commented:
>>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
slightwv (䄆 Netminder) Commented:
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

Open 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.
dwortmanEngineering AssociateAuthor Commented:
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
slightwv (䄆 Netminder) Commented:
Then use the inline HERE doc that I posted above outside of the shell script:  #a42386410

That isn't a "script".  It is a single command executed from the shell prompt.

Once you've confirmed it works, you posted in the original question "The attached snippet is from a BASH script... pass the file to sqlplus using a BASH script" so you aren't creating a script, you are modifying an existing one to read the values from the CSV file.

>>I have been tasked in finding a way to read a .csv file from the SQLPLUS command prompt if it is possible

I don't believe that is possible.

If the CSV file is on the database server and in a location that the oracle user can read it, you can create a PL/SQL block or a stored procedure that can read the file and execute that from sqlplus but I don't think that is really what you are after.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Geert GOracle dbaCommented:
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

Open in new window


nobody has to break any rules, just bend them a little
dwortmanEngineering AssociateAuthor Commented:
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.
slightwv (䄆 Netminder) Commented:
You can test without a script.  No permissions necessary.
slightwv (䄆 Netminder) Commented:
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.
dwortmanEngineering AssociateAuthor Commented:
Great information.

Thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.