sql script to check database for specific record

[Ratzie]

I have a list (7000) of tn that I would like to check to see if they are in the data base. If they are print tn to a tn.yes file, if not print that tn to a tn.not file.

How can I manipulate the sql commands, I think i have the select statement but had to append the appropriate files baffles me.

cat tn.file |while read i
do
echo $i
sqlplus -s admin/admin1 < SELECT TN from TN_TABLE WHERE TN = '$i';
EOF

[Rick Garland]

For the select statement;

SQL> select tn from tn_table where tn like '%i';

Notice the % in place of the $ for wildcard searches.

[Indira Aramandla]

Hi

You can use the util_file package for this one. You can define three files one in read mode and the other 2 in write mode. To be able to write and read from external files in the directory you should have the patch sepcified in the init.ora file with theentry utl_dir=/path.
Then restart you database for this to be effective.

eg:-
wf1_handle := UTL_FILE.FOPEN ('/path','tn_list', 'R');

wf2_handle := UTL_FILE.FOPEN '/path','tn_yes', 'W');
wf3_handle := UTL_FILE.FOPEN ('/path','tn_no', 'W');

And then in the input file loop till the end and for each line read using (UTL_FILE.GET_LINE (wf1_handle, value) select from the table.
And if you find a match then write to the tn.yes file otherwise write to the tn.no file using (UTL_FILE.PUT_LINE (wf3_handle, value);.

You have an input file to read from all the tn and then select form a table and if matches write to the output file no.txt

I hope this helps.

IA

[Yogeeraj_1]

hi,

indira has proposed a good solution above. You did not mention your Oracle Version. As from Oracle 9i, you also have the External Tables facility.

see: http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96652/ch12.htm#1009462

for instance, you will start with creating a directory:
e.g.
create or replace directory external_tables_dir
as '/datafiles/'

then create an external table:
e.g.
create table external_tab
( text1 varchar2(4000) ,
text2 varchar2(4000) ,
text3 varchar2(4000)
)
organization external
(type oracle_loader
default directory external_tables_dir
access parameters
(
records delimited by newline
fields
missing field values are null
( text1 position(1:4000),
text2 position(4001:8000),
text3 position(8001:12000)
)
)
location ('foo.bad')
)
/

and then:
select count(*) from external_tab

thus, you can add additional logic by programming a plsql block!

hope this helps too!
regards
yogeeraj

[Ratzie]

Unfortunately, this is a production database and can not be shutdown. I am running 8.1.7.

Thought there would be a easy way to return a value.

[Michael Schulte zur Sur]

Hi,

this excerpt from oracle faq may help you.

greetings,

Michael

Can one pass operating system parameters to SQL*Plus?
One can pass operating system variables to sqlplus using this syntax:
sqlplus username/password @cmdfile.sql var1 var2 var3Parameter var1 will be mapped to SQL*Plus variable &1, var2 to &2, etc. Look at this example:


sqlplus scott/tiger @x.sql '"test parameter"' dual
Where x.sql consists of:
select '&1' from &2;
exit 5;

[Volker Borowski · Accepted solution]

Well just change it this way:

sqlplus -s <connect admin/admin1
SELECT TN from TN_TABLE WHERE TN = '$i';
exit;
EOF

This takes the password from the
"ps -ef" - output and terminates the sqlplus correctly.

Volker

[Ratzie]

I get an error when I try that

SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}] | [INTERNAL]
where ::= [/][@] | /

[Sandman!]

You needs are better met by a shell script that can handle both the aspects of your problem:

1.) first you need to find out if your table has any records that meet your criteria.
2.) secondly, if criteria is met decide which output file they should go to tn.yes or tn.not.

I'ave attached a shell script that does what you're looking for.

best of luck!

[Michael Schulte zur Sur]

Hi,

here is my new attempt. My first one was a misunderstanding of your question.

hope, that works for you.

Michael


#tn.ksh
#!/bin/ksh
cat tn.sql > tmp.sql
awk -f tn.awk tn.file >> tmp.sql
echo spool off; >> tmp.sql
echo exit >> tmp.sql
cat tn.file | sqlplus /nolog @tmp
grep "^EXN" tn.log | nawk -F\t '{print $2}' > tn.no
grep "^EXY" tn.log | nawk -F\t '{print $2}' > tn.yes

#tn.awk
BEGIN{}
{
print "select decode(count(TN),1,'EXY\t',0,'EXN\t')||'"$0"' from TN_TABLE where TN = '"$0"';"
}
END{}

#tn.sql
set echo off
set feedback off
set termout off
set timing off
set heading off
set pages 0
set lines 500
connect / as sysdba;
spool tn.log