Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

sql script to check database for specific record

SOLVED
Go to solution
Ratzie
Super Advisor

sql script to check database for specific record

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
10 REPLIES
Rick Garland
Honored Contributor

Re: sql script to check database for specific record

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
Honored Contributor

Re: sql script to check database for specific record

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
Never give up, Keep Trying
Yogeeraj_1
Honored Contributor

Re: sql script to check database for specific record

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Ratzie
Super Advisor

Re: sql script to check database for specific record

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
Honored Contributor

Re: sql script to check database for specific record

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
Honored Contributor
Solution

Re: sql script to check database for specific record

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
Super Advisor

Re: sql script to check database for specific record

I get an error when I try that

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

Re: sql script to check database for specific record

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
Honored Contributor

Re: sql script to check database for specific record

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

Ratzie
Super Advisor

Re: sql script to check database for specific record

Thanks for the info!