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

In a Windows XP environment,
  running Perl 5.8,
  using  “use DBI”, I need coding help.

I create an Oracle DB table:
    CREATE TABLE Empl ( id INTEGER NOT NULL, 
                            name VARCHAR2(128), 
                            title VARCHAR2(128), 
                            phone CHAR(8) )  ;
I load 4 rows into the table:
       0,"Larry Wall","Perl Author","555-0101" ;
           1,"Tim Bunce","DBI Author","555-0202" ;
           2,"Randal Schwartz","Guy at Large","555-0303" ;
           3,"Doug MacEachern","Apache Man","555-0404" ;

    Question 1:  
o    What would the Perl code need to be to CREATE an Oracle stored pr
+ocedure (say named “GetNaem”) to extract the NAME value for a passed 
+in ID# to the procedure (equivalent to: SELECT name FROM Empl WHERE i
+d = ..PassedInValue.. )
    Question 2:
o    What would the Perl code need to be to pass in that ID argument a
+nd then retrieve the resultant data from that table via the stored pr
+ocedure into a return variable?

Thanks,
Dan (Perl newbie)

[download]

Replies are listed 'Best First'.
Re: I need Perl stored procedure help (against Oracle DB)
by chargrill (Parson) on Feb 23, 2006 at 21:00 UTC

    Hmm... I clicked on the download link to download your code so I could run it, but it didn't run. Perhaps I just missed the part where you show us the code that you've tried so far?



    --chargrill 
    $/  =  q#(\w)#  ;  sub  sig { print scalar reverse  join  ' ',  @_  } 
+ sig
map { s$\$/\$/$\$2\$1$g && $_ } split( ' ', ",erckha rlPe erthnoa stJu
+" );

    [download]
[reply]
[d/l]
      Tried this:
create or replace procedure GetNaem( dept_id IN NUMBER, dept_name OUT 
+VARCHAR2 )
is
begin
     select name 
     INTO dept_name 
     from Empl 
     where id = dept_id 
     order by name ;
end ;
# ============== Called by this:
my $rv ; 
eval {
    my $func = $dbh->prepare(q{
        BEGIN
            :rv := GetNaem(
                2 => :parameter1
            ) ;
        END ;
    }) ;

    # ... try to call empl_id no. 2
    $func->bind_param(":parameter1", 2) ;

    # values returned by the procedure's output parameters.
    $func->bind_param_inout(":rv", \$rv, 6) ;
    $func->execute ;

      [download]
[reply]
[d/l]

        I'm still not sure what you're trying to run, because the above 'code' won't execute at all. I'm used to seeing something like this (simplest case, incomplete, and completely untested):

        my $query=<<"EOQ";
create or replace procedure GetNaem( dept_id IN NUMBER, dept_name OUT 
+VARCHAR2 )
is
begin
     select name 
     INTO dept_name 
     from Empl 
     where id = dept_id 
     order by name ;
end ;
EOQ

my $dbh = new DBI::Oracle (or whatever module you're using with params
+ here);
# here you should bind your parameters, but I don't recall the syntax.
# yours doesn't quite look right either, so I would consult the docs

my $func = $dbh->prepare( $query );
$func->execute;

        [download]

        In short, it looks like you've got a little ways to go before you get code that will even compile. Currently, your eval brace isn't even closed...



        --chargrill 
        $/  =  q#(\w)#  ;  sub  sig { print scalar reverse  join  ' ',  @_  } 
+ sig
map { s$\$/\$/$\$2\$1$g && $_ } split( ' ', ",erckha rlPe erthnoa stJu
+" );

        [download]
[reply]
[d/l]
[select]
Re: I need Perl stored procedure help (against Oracle DB)
by Roy Johnson (Monsignor) on Feb 23, 2006 at 22:36 UTC
    You may or may not be able to get data out of a PL/SQL block using DBI, but if you use a function instead of a procedure, you can call it in a SELECT statement, and you will be able to fetch the data using DBI in the same way as you get it from any SELECT statement.
    Caution: Contents may have been coded under pressure.
[reply]
[reply]
        Sorry for the long delay in response, have been working on another problem lately. I found a work-around that seems to work fine in the Oracle Example link you sent. Thanks, dan
[reply]

Back to Seekers of Perl Wisdom