topic Retrieving the values from a Table ???? in Operating System - HP-UX

Retrieving the values from a Table ????

Rajkumar_3 — Tue, 06 Nov 2001 09:00:29 GMT

Hai all,

I am facing a problem on selecting the values from the Table...

Table Creation:
-------------------
Create table product(bat_in_dir varchar2(50),bat_out_dir varchar2(50));

In the above two columns i am having 9 directories names & paths in each column..

But i need to pass the output like below after getting the directory names from the table.Here the directory paths & names are not constant.

For bat_in_dir:
-----------------------------
BAC_in1=/home/test/
BAC_in2=/test/
.
.
.
.
BAC_in3=/product/

For bat_out_dir:
-----------------------------
BAC_out1=/home/test/
BAC_out2=/test/
.
.
.
.
BAC_out9=/product/
----------------------------------

I used this below code to get the Output from the Table in HP-Unix platform...

Example Shell Script:
-----------------------------------------------
v_filedate=`echo " select to_char(add_months(sysdate, -9),'MONYYYY') from dual; " |
$ORACLE_HOME/bin/sqlplus -s USERNAME/PASSWORD|tail -2 | head -1`;
echo $v_filedate;
-----------------------------------------------

Can any body help me???

Thanks & Regards
Ra

Re: Retrieving the values from a Table ????

Deepak Extross — Tue, 06 Nov 2001 10:19:40 GMT

Hi,

1. How are you telling sqlplus which database to query?

2. What does
$ORACLE_HOME/bin/sqlplus -s USERNAME/PASSWORD << EOF
>select to_char(add_months(sysdate,-9),'MONYYYY') from dual
>EOF
yield?

3. What is the relationship between the tables 'product' and 'dual'?

Re: Retrieving the values from a Table ????

Santosh Nair_1 — Tue, 06 Nov 2001 10:38:52 GMT

How about putting your query into a file and then calling the file from a shell script, i.e. to use your example:

---test.sql---
select to_char(add_months(sysdate, -9),'MONYYYY') from dual;
exit
-----end-----

---test.sh---
PATH=$ORACLE_HOME/bin:$PATH
v_filedate=$(sqlplus -S USERNAME/PASSWORD @test.sql|tail -2|head -1)
echo $v_filedate;
-----end-----
Hope this helps.

-Santosh

Re: Retrieving the values from a Table ????

Rajkumar_3 — Tue, 06 Nov 2001 11:24:14 GMT

Hai All,

The query which i have given its just an example only..
The actual query looks like this...
----------------------------
v_filedate=`echo " select bat_in_dir,bat_out_dir from product; " |
$ORACLE_HOME/bin/sqlplus -s USERNAME/PASSWORD|tail -2 | head -1`;
echo $v_filedate;
-----------------------------
The above mentioned query may not be correct...

The above script was executing successfully with out giving any rows..I want that two column output in to different varaibles as described above..

My purpose is to delete some files depending upon the directories..The directories are not constant and we dont know which direcories they were.Thats the reason i have to select the directory paths from the table and delete the files stored in those directories in the OS...

Can any one help me??

Thanks & Regards
Ra

Re: Retrieving the values from a Table ????

Rajkumar_3 — Tue, 06 Nov 2001 11:43:50 GMT

Hai santosh,

Its hanging ..Whats the reason ????

Raj

Re: Retrieving the values from a Table ????

Thierry Poels_1 — Tue, 06 Nov 2001 12:00:37 GMT

Hi,
don't forget to put an "exit;" in the SQL-script.

good luck,
Thierry.

Re: Retrieving the values from a Table ????

Santosh Nair_1 — Tue, 06 Nov 2001 13:24:41 GMT

Check that the username and password are set correctly for sqlplus. The script hung on my system too when I didn't set the login info properly. Basically, sqlplus is waiting for a proper login.

Hope this helps.

-Santosh

Re: Retrieving the values from a Table ????

Rajkumar_3 — Tue, 06 Nov 2001 13:29:03 GMT

Hai all,

When i execute the script it is displaying that 23 rows was selected.But its not displaying all the rows
If there is one row then its displaying..

What could be the reason ??

Bye
Raj

Re: Retrieving the values from a Table ????

Rajkumar_3 — Tue, 06 Nov 2001 13:40:52 GMT

Hai all,

Can any one can provide me a solution as above????

Its Urjent please

Raj

Re: Retrieving the values from a Table ????

Thierry Poels_1 — Tue, 06 Nov 2001 13:53:00 GMT

Hi,

maybe a different approach is possible: creation of a delete script from within sqlplus:

--- my.sql --
set heading off
set feedback off
spool /tmp/mydelete.sh
select 'find ' || bac_in_dir || ' ' || bac_out_dir || ' -type f -mtime +270 -exec rm {} \;'
from dual;
spool off;
exit;
-- end my.sql ---

-- start script
sqlplus -s u/p @my.sql
sh /tmp/mydelete.sh
-- end script

that's it, the sql should generate something like:
find /home/test/ /home/test/ -type f -mtime +270 -exec rm {} \;

(if 'BAC_in1=' is included in the value, you will need to trim it)

regards,
Thierry.

Re: Retrieving the values from a Table ????

harry d brown jr — Tue, 06 Nov 2001 14:37:55 GMT

Your answer lies in rewriting your script in perl:

http://www.orafaq.org/faqperl.htm

v_filedate=`echo " select bat_in_dir,bat_out_dir from product; " |
$ORACLE_HOME/bin/sqlplus -s USERNAME/PASSWORD|tail -2 | head -1`;
echo $v_filedate;

#!/pathtoperl/perl
use strict;
use DBI;
my $dbh = DBI->connect( 'dbi:Oracle:orcl',
'USERNAMEHERE',
'PASSWORDHERE',
{ RaiseError => 1, AutoCommit => 0 }
) || die "Database connection not made: $DBI::errstr";
my $sql = qq{ select bat_in_dir,bat_out_dir from product };
my $sth = $dbh->prepare($sql);
$sth->execute();

my($indir, $outdir); # Declare columns
$sth->bind_columns(\$indir, \$outdir);

print "List:\n\n"; # Fetch rows from DB
while( $sth->fetch() ) {
print "$indir, $outdir \n";
}
$sth->finish(); # Close cursor
$dbh->disconnect;

live free or die
harry

Re: Retrieving the values from a Table ????

Jonas Linden — Wed, 07 Nov 2001 14:46:41 GMT

Hi

Try this

echo "
set heading off
set feedback off
set pagesize 0
select bat_in_dir from product
union all
select bat_out_dir from product;"|sqlplus -s USERNAME/PASSWORD|xargs rm -rf

Regards

Jonas

Re: Retrieving the values from a Table ????

Rajkumar_3 — Thu, 08 Nov 2001 03:33:03 GMT

Hai all,

We cant remove the files directly...

I have attached one shell script which will read the parameters from the property file ...That script i recieved in the FAQ prepared by Robin...

so i have to follow in that way...Is it possible...

Regards
Raj