Retrieving the values from a Table ????

[Rajkumar_3]

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

[Deepak Extross]

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'?

[Santosh Nair_1]

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

[Rajkumar_3]

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

[Rajkumar_3]

Hai santosh,

Its hanging ..Whats the reason ????

Raj

[Thierry Poels_1]

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

good luck,
Thierry.

[Santosh Nair_1]

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

[Rajkumar_3]

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

[Rajkumar_3]

Hai all,

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

Its Urjent please

Raj

[Thierry Poels_1]

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.

[harry d brown jr]

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

[Jonas Linden]

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

[Rajkumar_3]

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