Simpler Navigation for Servers and Operating Systems
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

Retrieving the values from a Table ????

Rajkumar_3
Regular Advisor

Retrieving the values from a Table ????

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
Oracle DBA
12 REPLIES
Deepak Extross
Honored Contributor

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

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

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

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
Life is what's happening while you're busy making other plans
Rajkumar_3
Regular Advisor

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

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
Oracle DBA
Rajkumar_3
Regular Advisor

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

Hai santosh,

Its hanging ..Whats the reason ????

Raj
Oracle DBA
Thierry Poels_1
Honored Contributor

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

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

good luck,
Thierry.
All unix flavours are exactly the same . . . . . . . . . . for end users anyway.
Santosh Nair_1
Honored Contributor

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

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
Life is what's happening while you're busy making other plans
Rajkumar_3
Regular Advisor

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

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
Oracle DBA
Rajkumar_3
Regular Advisor

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

Hai all,

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

Its Urjent please

Raj
Oracle DBA
Thierry Poels_1
Honored Contributor

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

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.
All unix flavours are exactly the same . . . . . . . . . . for end users anyway.
harry d brown jr
Honored Contributor

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

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
Live Free or Die
Jonas Linden
Occasional Visitor

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

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

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

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
Oracle DBA