Re^2: I need Perl stored procedure help (against Oracle DB)

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]

Comment on Re^2: I need Perl stored procedure help (against Oracle DB) Download Code

Replies are listed 'Best First'.
Re^3: I need Perl stored procedure help (against Oracle DB) by chargrill (Parson) on Feb 23, 2006 at 22:46 UTC
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]

Replies are listed 'Best First'.

Re^3: I need Perl stored procedure help (against Oracle DB)
by chargrill (Parson) on Feb 23, 2006 at 22:46 UTC

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]