Oracle functions through DBI?

------=_Part_8432_16364166.1146062779015
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Hello,

I was wondering (and found nothing bout that on the web), can I use DBI to
execute some oracle DB functions?
Here's my case: I have a (oracle) function that returns a new free ROW ID
randomly and I have to manage that RI from a perl script. Can I use DBI to
submit the request to oracle? for me it would be much more simple and
efficient than the same old way!!

thanks
Riccardo

------=_Part_8432_16364166.1146062779015--

Re: Oracle functions through DBI?

Yes one can quite simply at least for a Stored procedure as for a function
you may have to wrap it in some SQL first.

my $db="";
my $db = DBI->connect();
my $desc = 'Comments from user ';
my $desc_out ="";

my $sql="begin insert_comment(:p_id ,:p_desc,:p_desc_out); end;";

my $c=$db->prepare($sql) or die "err 1 is $DBI::errstr\n";

$c->bind_param(":p_id",param('id')) or die "err 2 is $DBI::errstr\n";

$c->bind_param_inout(":p_desc",\$desc,{ ora_type => ORA_CLOB }) or die "err
3 is $DBI::errstr\n";

$c->bind_param_inout(":p_desc_out",\$desc_out,{ ora_type => ORA_CLOB }) or
die "err 3 is $DBI::errstr\n";

$c->execute() or die "err 4 is $DBI::errstr\n";

my $page_data = $c -> fetchall_arrayref();

$c->finish();


print @$page_data;
print "

";
print $desc_out;

where my insert_comment stored Procedure starts like

CREATE procedure insert_comment(in_ID in NUMBER, in_log in out clob, out_log
in out clob )





----- Original Message -----
From: "Riccardo Bonuccelli"
To:
Sent: Wednesday, April 26, 2006 10:46 AM
Subject: Oracle functions through DBI?


Hello,

I was wondering (and found nothing bout that on the web), can I use DBI to
execute some oracle DB functions?
Here's my case: I have a (oracle) function that returns a new free ROW ID
randomly and I have to manage that RI from a perl script. Can I use DBI to
submit the request to oracle? for me it would be much more simple and
efficient than the same old way!!

thanks
Riccardo

MySql function

--0-1133999965-1146067532=:93371
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

Hi Everyone,

i did the following

$dbh->prepare("UPDATE contentTable SET filename = '$dbfile date_modefied = 'CURRENT_DATE()' time_modefied = 'CURRENT_TIME() WHERE ext = '$ext' ")

and the value of the current date and current time were not updated on the table. It seems perl interpolated the function CURRENT_DATE() and CURRENT_TIME() .....everything else is ok

can someone help with a way out.


Rgds

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--0-1133999965-1146067532=:93371--

RE: MySql function

Akpome Akpoguma [mailto:akpome_perl@yahoo.com] wrote:
>
> Hi Everyone,
>
> i did the following
>
> $dbh->prepare("UPDATE contentTable SET filename = '$dbfile date_modefied
> = 'CURRENT_DATE()' time_modefied = 'CURRENT_TIME() WHERE ext = '$ext' ")
>
> and the value of the current date and current time were not updated on
> the table. It seems perl interpolated the function CURRENT_DATE() and
> CURRENT_TIME() .....everything else is ok

Why did you put single quotes around the MySql function calls? That tells
MySql that these are strings, not function calls.

Also, you should be using placeholders for the variables. Try this:

$dbh->prepare('UPDATE contentTable SET filename = ?, date_modified =
CURRENT_DATE(), time_modified = CURRENT_TIME() WHERE ext = ?');

$dbh->execute($dbfile, $ext);


Ronald

Re: Oracle functions through DBI?

You want to read
http://search.cpan.org/~pythian/DBD-Oracle-1.17/Oracle.pm#PL /SQL_Examples

Alexander

Riccardo Bonuccelli wrote:

>Hello,
>
>I was wondering (and found nothing bout that on the web), can I use DBI to
>execute some oracle DB functions?
>Here's my case: I have a (oracle) function that returns a new free ROW ID
>randomly and I have to manage that RI from a perl script. Can I use DBI to
>submit the request to oracle? for me it would be much more simple and
>efficient than the same old way!!
>
>thanks
>Riccardo
>
>
>

--
Alexander Foken
mailto:alexander@foken.de http://www.foken.de/alexander/

RE: Oracle functions through DBI?

http://search.cpan.org/~pythian/DBD-Oracle-1.17/Oracle.pm#Ot her_Data_Typ
es

-----Original Message-----
From: Riccardo Bonuccelli [mailto:riccardo.bonuccelli@gmail.com]=20
Sent: Wednesday, April 26, 2006 8:46 AM
To: dbi-users@perl.org
Subject: Oracle functions through DBI?

Hello,

I was wondering (and found nothing bout that on the web), can I use DBI
to
execute some oracle DB functions?
Here's my case: I have a (oracle) function that returns a new free ROW
ID
randomly and I have to manage that RI from a perl script. Can I use DBI
to
submit the request to oracle? for me it would be much more simple and
efficient than the same old way!!

thanks
Riccardo

This electronic message transmission is a PRIVATE communication which =
contains
information which may be confidential or privileged. The information is =
intended=20
to be for the use of the individual or entity named above. If you are =
not the=20
intended recipient, please be aware that any disclosure, copying, =
distribution=20
or use of the contents of this information is prohibited. Please notify =
the
sender of the delivery error by replying to this message, or notify us =
by
telephone (877-633-2436, ext. 0), and then delete it from your system.