How I can call a stored procedure from Perl5

I must call a stored procedure for Oracle 8 Release 8.0.5.0.0.
How I can call my SP
execute ao1 param1,param2,..  from Perl?
HandschuhAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

cadabraCommented:
Here is some code I cut out of the DBD::Oracle documentation from active-state.

Cadabra.




Code starts bellow:
=================================================================














These PL/SQL examples come from: Eric Bartley <bartley@cc.purdue.edu>


  # we assume this package already exists
  my $plsql = q{



    CREATE OR REPLACE PACKAGE plsql_example
    IS
      PROCEDURE proc_np;



      PROCEDURE proc_in (
          err_code IN NUMBER
      );



      PROCEDURE proc_in_inout (
          test_num IN NUMBER,
          is_odd IN OUT NUMBER
      );



      FUNCTION func_np
        RETURN VARCHAR2;



    END plsql_example;



    CREATE OR REPLACE PACKAGE BODY plsql_example
    IS
      PROCEDURE proc_np
      IS
        whoami VARCHAR2(20) := NULL;
      BEGIN
        SELECT USER INTO whoami FROM DUAL;
      END;



      PROCEDURE proc_in (
        err_code IN NUMBER
      )
      IS
      BEGIN
        RAISE_APPLICATION_ERROR(err_code, 'This is a test.');
      END;



      PROCEDURE proc_in_inout (
        test_num IN NUMBER,
        is_odd IN OUT NUMBER
      )
      IS
      BEGIN
        is_odd := MOD(test_num, 2);
      END;



      FUNCTION func_np
        RETURN VARCHAR2
      IS
        ret_val VARCHAR2(20);
      BEGIN
        SELECT USER INTO ret_val FROM DUAL;
        RETURN ret_val;
      END;



    END plsql_example;
  };



  use DBI;



  my($db, $csr, $ret_val);



  $db = DBI->connect('dbi:Oracle:database','user','password')
        or die "Unable to connect: $DBI::errstr";



  # So we don't have to check every DBI call we set RaiseError.
  # See the DBI docs now if you're not familiar with RaiseError.
  $db->{RaiseError} = 1;



  # Example 1
  #
  # Calling a PLSQL procedure that takes no parameters. This shows you the
  # basic's of what you need to execute a PLSQL procedure. Just wrap your
  # procedure call in a BEGIN END; block just like you'd do in SQL*Plus.
  #
  # p.s. If you've used SQL*Plus's exec command all it does is wrap the
  #      command in a BEGIN END; block for you.



  $csr = $db->prepare(q{
    BEGIN
      PLSQL_EXAMPLE.PROC_NP;
    END;
  });
  $csr->execute;



  # Example 2
  #
  # Now we call a procedure that has 1 IN parameter. Here we use bind_param
  # to bind out parameter to the prepared statement just like you might
  # do for an INSERT, UPDATE, DELETE, or SELECT statement.
  #
  # I could have used positional placeholders (e.g. :1, :2, etc.) or
  # ODBC style placeholders (e.g. ?), but I prefer Oracle's named
  # placeholders (but few DBI drivers support them so they're not portable).



  my $err_code = -20001;



  $csr = $db->prepare(q{
        BEGIN
            PLSQL_EXAMPLE.PROC_IN(:err_code);
        END;
  });



  $csr->bind_param(":err_code", $err_code);



  # PROC_IN will RAISE_APPLICATION_ERROR which will cause the execute to 'fail'.
  # Because we set RaiseError, the DBI will croak (die) so we catch that with eval.
  eval {
    $csr->execute;
  };
  print 'After proc_in: $@=',"'$@', errstr=$DBI::errstr, ret_val=$ret_val\n";



  # Example 3
  #
  # Building on the last example, I've added 1 IN OUT parameter. We still
  # use a placeholders in the call to prepare, the difference is that
  # we now call bind_param_inout to bind the value to the place holder.
  #
  # Note that the third parameter to bind_param_inout is the maximum size
  # of the variable. You normally make this slightly larger than necessary.
  # But note that the perl variable will have that much memory assigned to
  # it even if the actual value returned is shorter.



  my $test_num = 5;
  my $is_odd;



  $csr = $db->prepare(q{
        BEGIN
            PLSQL_EXAMPLE.PROC_IN_INOUT(:test_num, :is_odd);
        END;
  });



  # The value of $test_num is _copied_ here
  $csr->bind_param(":test_num", $test_num);



  $csr->bind_param_inout(":is_odd", \$is_odd, 1);



  # The execute will automagically update the value of $is_odd
  $csr->execute;



  print "$test_num is ", ($is_odd) ? "odd - ok" : "even - error!", "\n";



  # Example 4
  #
  # What about the return value of a PLSQL function? Well treat it the same
  # as you would a call to a function from SQL*Plus. We add a placeholder
  # for the return value and bind it with a call to bind_param_inout so
  # we can access it's value after execute.



  my $whoami = "";



  $csr = $db->prepare(q{
        BEGIN
            :whoami := PLSQL_EXAMPLE.FUNC_NP;
        END;
  });



  $csr->bind_param_inout(":whoami", \$whoami, 20);
  $csr->execute;
  print "Your database user name is $whoami\n";



  $db->disconnect;


You can find more examples in the t/plsql.t file in the DBD::Oracle source directory.

0
HandschuhAuthor Commented:
Adjusted points to 50
0
cadabraCommented:
Why did you reject the answer ? to my understanding, you wanted to know how to call an oracle stored procedure from perl. The answer I submitted demonstrates this. If I misunderstood your meaning, please give more details as to what you want to do.

Cadabra.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Perl

From novice to tech pro — start learning today.