Connect Perl to ORACLE Database

[Ricardo Bassoi]

Hi All,

I have some doubts:

- I want to connect to a database called tpn1
using the shell script I do this:
ORACLE_HOME=/oracle
ORACLE_SID=SMP
sqlplus tpn1/tpn1 << EOF
set pages 0;
set colsep ";";
set linesize 0;
col cus_nbr for a10;
col acc_nbr justify left for 999999;
col use_hour for 99:99:99;
col max_per for 99;
set feed off;
spool card_used.txt;
select acc_nbr,curcred,exp_dat3,max_cred,max_per,use_date,use_hour,cli_call,
cus_nbr from card where cus_nbr is not null;
spool off
exit;
EOF

- For Perl I know that I need to use DBI, but is a little confused to me how to implement it.

- After I want to use the fields that I got from the select command and compare a with a parameter is newer than the sysdate ( How to get the sysdate ? ), if its is so I change the state.

[Ricardo Bassoi]

Anybody have an idea ?

[Reinhard Burger]

Hi Ricardo

Honestly i do not understand exactly what you want to do. But maybe it is somehow a hint .
Why not using SYSDATE in oracle ?

If you just need the day an easy example can be :

select SYSDATE from dual;

or what you can try also is : search the web
maybe you find a solution that just needs minor adaptions to what yopu want to do

[Steve Coates · Accepted solution]

Ricardo,
You will need to install DBI and DBD. DBD is specific to the database type, in your case Oracle.

There is a lot of information on line about using DBD. There are also "Programming the Perl DBI" published by O'Reilly.

Once you have things loaded you basicly connect to the database and issue SQL statments by way of the methods defined in DBI.

It is pretty straight foward once you get it loaded and try a couple of examples you can find online.

http://www.saturn5.com/~jwb/dbi-examples.html
http://www.they.com/doc/local/perl/DBI.html

Those are just a couple of many you can find by searching the web.

Steve