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

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]
      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]

Back to Seekers of Perl Wisdom