topic Re: sql script to check database for specific record in Operating System - HP-UX

sql script to check database for specific record

Ratzie — Wed, 29 Jun 2005 11:31:10 GMT

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

Re: sql script to check database for specific record

Rick Garland — Wed, 29 Jun 2005 12:10:14 GMT

For the select statement;

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

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

Re: sql script to check database for specific record

Indira Aramandla — Wed, 29 Jun 2005 21:46:24 GMT

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

Re: sql script to check database for specific record

Yogeeraj_1 — Wed, 29 Jun 2005 23:56:15 GMT

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

Re: sql script to check database for specific record

Ratzie — Thu, 30 Jun 2005 09:23:30 GMT

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.

Re: sql script to check database for specific record

Michael Schulte zur Sur — Thu, 30 Jun 2005 09:38:53 GMT

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;

Re: sql script to check database for specific record

Volker Borowski — Thu, 30 Jun 2005 11:12:21 GMT

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

Re: sql script to check database for specific record

Ratzie — Thu, 30 Jun 2005 12:36:34 GMT

I get an error when I try that

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

Re: sql script to check database for specific record

Sandman! — Thu, 30 Jun 2005 18:33:03 GMT

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!

Re: sql script to check database for specific record

Michael Schulte zur Sur — Fri, 01 Jul 2005 07:47:29 GMT

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

Re: sql script to check database for specific record

Ratzie — Thu, 25 Aug 2005 16:06:27 GMT

Thanks for the info!