DBI custom functions

------=_Part_126225_17858280.1157531818054
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Hello there,

I must retrieve a particular field from an Oracle DB with perl/DBI. This
field is actually formatted as a kind of bytestring and I developed an
oracle function to retrieve that field in a correct way, but as I told I
have to connect through DBI.

So the question is:
Is there a way to define custom functions to be used in a dbi session?

Thanks in advance.

--
Riccardo
http://www.flickr.com/photos/askthepixel

------=_Part_126225_17858280.1157531818054--

RE: DBI custom functions

You should be able to simply use the function in the SQL within your Perl
script. For example :

my $sth = $dbh->prepare(q(
SELECT my_func(raw_data)
FROM my_table
));

etc

If you can use the function in SQL*Plus, you can use it with Perl/DBI.

HTH,

Steve

> -----Original Message-----
> From: Riccardo Bonuccelli [mailto:riccardo.bonuccelli@gmail.com]
> Sent: Wednesday, 6 September 2006 6:37 PM
> To: dbi-users@perl.org
> Subject: DBI custom functions
>
> Hello there,
>
> I must retrieve a particular field from an Oracle DB with perl/DBI. This
> field is actually formatted as a kind of bytestring and I developed an
> oracle function to retrieve that field in a correct way, but as I told I
> have to connect through DBI.
>
> So the question is:
> Is there a way to define custom functions to be used in a dbi session?
>
> Thanks in advance.
>
> --
> Riccardo
> http://www.flickr.com/photos/askthepixel

Re: DBI custom functions

------=_Part_126938_25415376.1157535586387
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Thanks Steve,

I agree, you can call the function this way, but then you have to be sure
that the function is already defined in Oracle. I was wondering if there is
a way to define the function with DBI..


2006/9/6, Steve Baldwin :
>
> You should be able to simply use the function in the SQL within your Perl
> script. For example :
>
> my $sth = $dbh->prepare(q(
> SELECT my_func(raw_data)
> FROM my_table
> ));
>
> etc
>
> If you can use the function in SQL*Plus, you can use it with Perl/DBI.
>
> HTH,
>
> Steve
>
> > -----Original Message-----
> > From: Riccardo Bonuccelli [mailto:riccardo.bonuccelli@gmail.com]
> > Sent: Wednesday, 6 September 2006 6:37 PM
> > To: dbi-users@perl.org
> > Subject: DBI custom functions
> >
> > Hello there,
> >
> > I must retrieve a particular field from an Oracle DB with perl/DBI. This
> > field is actually formatted as a kind of bytestring and I developed an
> > oracle function to retrieve that field in a correct way, but as I told I
> > have to connect through DBI.
> >
> > So the question is:
> > Is there a way to define custom functions to be used in a dbi session?
> >
> > Thanks in advance.
> >
> > --
> > Riccardo
> > http://www.flickr.com/photos/askthepixel
>
>
>


--
Riccardo
http://www.flickr.com/photos/askthepixel

------=_Part_126938_25415376.1157535586387--

RE: DBI custom functions

Not sure I follow exactly what you're after?

You could $dbh->do a create or replace function step at the beginning of
your perl script to ensure that the function exists (I guess you could
select from the dictionary to check before bothering), or you could
re-write your oracle function as a perl function and select the actual
bytestring to pass to this function instead.

Ken.


-----Original Message-----
From: Riccardo Bonuccelli [mailto:riccardo.bonuccelli@gmail.com]=0D
Sent: 06 September 2006 10:40
To: Steve Baldwin
Cc: dbi-users@perl.org
Subject: Re: DBI custom functions

Thanks Steve,

I agree, you can call the function this way, but then you have to be
sure
that the function is already defined in Oracle. I was wondering if there
is
a way to define the function with DBI..


2006/9/6, Steve Baldwin :
>
> You should be able to simply use the function in the SQL within your
Perl
> script. For example :
>
> my $sth =3D $dbh->prepare(q(
> SELECT my_func(raw_data)
> FROM my_table
> ));
>
> etc
>
> If you can use the function in SQL*Plus, you can use it with Perl/DBI.
>
> HTH,
>
> Steve
>
> > -----Original Message-----
> > From: Riccardo Bonuccelli [mailto:riccardo.bonuccelli@gmail.com]
> > Sent: Wednesday, 6 September 2006 6:37 PM
> > To: dbi-users@perl.org
> > Subject: DBI custom functions
> >
> > Hello there,
> >
> > I must retrieve a particular field from an Oracle DB with perl/DBI.
This
> > field is actually formatted as a kind of bytestring and I developed
an
> > oracle function to retrieve that field in a correct way, but as I
told I
> > have to connect through DBI.
> >
> > So the question is:
> > Is there a way to define custom functions to be used in a dbi
session?
> >
> > Thanks in advance.
> >
> > --
> > Riccardo
> > http://www.flickr.com/photos/askthepixel
>
>
>


--=0D
Riccardo
http://www.flickr.com/photos/askthepixel


************************************************************ ***************=
************************************************************ *************
This e-mail and any accompanying documents contain confidential information=
intended for a specific individual which is private and protected by law.
If you are not the intended recipient, any disclosure, copying,=
distribution or other use of this information is strictly prohibited.
You are also requested to advise us immediately if you receive information=
which is not addressed to you.
beCogent is Authorised and Regulated by the Financial Services Authority.
************************************************************ ***************=
************************************************************ **************

Re: DBI custom functions

------=_Part_127019_26999602.1157536520934
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

That's exactly what I wanted to know.
Thank you very much!

Riccardo

2006/9/6, Gaul, Ken :
>
>
> Not sure I follow exactly what you're after?
>
> You could $dbh->do a create or replace function step at the beginning of
> your perl script to ensure that the function exists (I guess you could
> select from the dictionary to check before bothering), or you could
> re-write your oracle function as a perl function and select the actual
> bytestring to pass to this function instead.
>
> Ken.
>
>
> -----Original Message-----
> From: Riccardo Bonuccelli [mailto:riccardo.bonuccelli@gmail.com]
> Sent: 06 September 2006 10:40
> To: Steve Baldwin
> Cc: dbi-users@perl.org
> Subject: Re: DBI custom functions
>
> Thanks Steve,
>
> I agree, you can call the function this way, but then you have to be
> sure
> that the function is already defined in Oracle. I was wondering if there
> is
> a way to define the function with DBI..
>
>
> 2006/9/6, Steve Baldwin :
> >
> > You should be able to simply use the function in the SQL within your
> Perl
> > script. For example :
> >
> > my $sth = $dbh->prepare(q(
> > SELECT my_func(raw_data)
> > FROM my_table
> > ));
> >
> > etc
> >
> > If you can use the function in SQL*Plus, you can use it with Perl/DBI.
> >
> > HTH,
> >
> > Steve
> >
> > > -----Original Message-----
> > > From: Riccardo Bonuccelli [mailto:riccardo.bonuccelli@gmail.com]
> > > Sent: Wednesday, 6 September 2006 6:37 PM
> > > To: dbi-users@perl.org
> > > Subject: DBI custom functions
> > >
> > > Hello there,
> > >
> > > I must retrieve a particular field from an Oracle DB with perl/DBI.
> This
> > > field is actually formatted as a kind of bytestring and I developed
> an
> > > oracle function to retrieve that field in a correct way, but as I
> told I
> > > have to connect through DBI.
> > >
> > > So the question is:
> > > Is there a way to define custom functions to be used in a dbi
> session?
> > >
> > > Thanks in advance.
> > >
> > > --
> > > Riccardo
> > > http://www.flickr.com/photos/askthepixel
> >
> >
> >
>
>
> --
> Riccardo
> http://www.flickr.com/photos/askthepixel
>
>
>
> ************************************************************ ************************************************************ ****************************
> This e-mail and any accompanying documents contain confidential
> information intended for a specific individual which is private and
> protected by law.
> If you are not the intended recipient, any disclosure, copying,
> distribution or other use of this information is strictly prohibited.
> You are also requested to advise us immediately if you receive information
> which is not addressed to you.
> beCogent is Authorised and Regulated by the Financial Services Authority.
>
> ************************************************************ ************************************************************ *****************************
>



--
Riccardo
http://www.flickr.com/photos/askthepixel

------=_Part_127019_26999602.1157536520934--

RE: DBI custom functions

If you already have the function, I don't understand why you wouldn't want
to use it in Perl/DBI. If you are talking about a brand new function, then
can't you just write it in Perl and call it after fetching the data?

For example :

my $sth = $dbh->prepare(q(select raw_data from my_table));
$sth->execute;
my ($raw_data, $cooked_data);
$sth->bind_columns(\($raw_data));
while ($sth->fetch) {
$cooked_data = my_perl_func($raw_data);
:
}

However, if there is even a remote chance that you may want to access the
raw data from PL/SQL, then it would be best to write the function in PL/SQL
and have it live in the database. As Ken mentioned, you can of course
define a PL/SQL function using Perl/DBI just the same as you would using
SQL*Plus or any other Oracle/SQL tool.

Regards,

Steve

> -----Original Message-----
> From: Riccardo Bonuccelli [mailto:riccardo.bonuccelli@gmail.com]
> Sent: Wednesday, 6 September 2006 7:40 PM
> To: Steve Baldwin
> Cc: dbi-users@perl.org
> Subject: Re: DBI custom functions
>
> Thanks Steve,
>
> I agree, you can call the function this way, but then you have to be sure
> that the function is already defined in Oracle. I was wondering if there
> is
> a way to define the function with DBI..
>
>
> 2006/9/6, Steve Baldwin :
> >
> > You should be able to simply use the function in the SQL within your
> Perl
> > script. For example :
> >
> > my $sth = $dbh->prepare(q(
> > SELECT my_func(raw_data)
> > FROM my_table
> > ));
> >
> > etc
> >
> > If you can use the function in SQL*Plus, you can use it with Perl/DBI.
> >
> > HTH,
> >
> > Steve
> >
> > > -----Original Message-----
> > > From: Riccardo Bonuccelli [mailto:riccardo.bonuccelli@gmail.com]
> > > Sent: Wednesday, 6 September 2006 6:37 PM
> > > To: dbi-users@perl.org
> > > Subject: DBI custom functions
> > >
> > > Hello there,
> > >
> > > I must retrieve a particular field from an Oracle DB with perl/DBI.
> This
> > > field is actually formatted as a kind of bytestring and I developed an
> > > oracle function to retrieve that field in a correct way, but as I told
> I
> > > have to connect through DBI.
> > >
> > > So the question is:
> > > Is there a way to define custom functions to be used in a dbi session?
> > >
> > > Thanks in advance.
> > >
> > > --
> > > Riccardo
> > > http://www.flickr.com/photos/askthepixel
> >
> >
> >
>
>
> --
> Riccardo
> http://www.flickr.com/photos/askthepixel