Objects in PL/SQL in DBD::Oracle

archfool has asked for the wisdom of the Perl Monks concerning the following question:

Assuming an object in PL/SQL on Oracle:

CREATE OR REPLACE TYPE myObj
AS OBJECT (
   Col1  number,
   Col2  varchar(2),
   Col3  timestamp,
   ...
   Col57 number
);
[download]

(Why an object? Because OCI programming doesn't allow PL/SQL RECORD type. ;)

And now here's what I'd LIKE to run. Is anything like this possible? I need to call a package function that takes IN this object. How can I bind perl variables to the PL/SQL object? I'm using PL/SQL to get at a package/function in the database. This function does post-processing on the object (which really just represents a row of data). We're all trying to avoid 57 bind variables. :)

my $sql = "
DECLARE
  in_row  myObj;
  retcode number;
BEGIN
  :retcode := Otherpackage.Function(:in_row);
END;
";

my $sth = $dbh->prepare($sql) ...
[download]

My main question is... how do I bind a list or hash to :in_row in the $dbh->bind_param(":in_row", \%hash, ...);

-ArchFool

Comment on Objects in PL/SQL in DBD::Oracle Select or Download Code

Replies are listed 'Best First'.
Re: Objects in PL/SQL in DBD::Oracle by andreas1234567 (Vicar) on Jun 21, 2007 at 06:28 UTC
Interesting question. I guess you cannot directly use the object as a bind variable since there's no corresponding value in ora_types: `:ora_types ORA_VARCHAR2 ORA_STRING ORA_NUMBER ORA_LONG ORA_ROWID ORA_DATE ORA_RAW ORA_LONGRAW ORA_CHAR ORA_CHARZ ORA_MLSLABEL ORA_NTY ORA_CLOB ORA_BLOB ORA_RSET` [download] But you could possibly turn the arguments into an XML document and pass that as a CLOB. See this sample from the DBD::Oracle man page: # Build a large XML document, bind it as a CLOB, # extract elements through PL/SQL and return as a CLOB # $dbh is a connected database handle # output will be large local $dbh->{LongReadLen} = 1_000_000; my $in_clob = "<document>\n"; $in_clob .= " <value>$_</value>\n" for 1 .. 10_000; $in_clob .= "</document>\n"; my $out_clob; my $sth = $dbh->prepare(<<PLSQL_END); -- extract 'value' nodes DECLARE x XMLTYPE := XMLTYPE(:in); BEGIN :out := x.extract('/document/value').getClobVal(); END; PLSQL_END # :in param will be converted to a temp lob # :out parameter will be returned as a string. $sth->bind_param( ':in', $in_clob, { ora_type => ORA_CLOB } ); $sth->bind_param_inout( ':out', \$out_clob, 0, { ora_type => ORA_CLOB +} ); $sth->execute; [download] -- print map{chr}unpack(q{A3}x24,q{074117115116032097110111116104101114032080101114108032104097099107101114})	[reply] [d/l] [select]
Re^2: Objects in PL/SQL in DBD::Oracle by archfool (Monk) on Jun 21, 2007 at 14:10 UTC
That's FANTASTIC! It's a shame that DBD::Oracle doesn't support objects. =sigh= OCI does. Maybe I'll submit a patch. ;) But thanks for the idea.. I think this may work in this particular case. :) --ArchFool	[reply]


Don't ask to ask, just ask
	PerlMonks