How can I run a SQL scipt from Perl

I have an SQL script in my oracle database and I want to send data to this script. How can I do it?  
unsalAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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:
Could you give more information. Do you mean you have a stored procedure in the database that you want to pass parameters to and call ?
unsalAuthor Commented:
yes I mean I have a stored procedure in the database that I want to pass parameters thats right.
cadabraCommented:
Hi,

In order to run this code, you must first install the "DBI" and "DBD::Oracle" packages (if not already installed).

Here is some code I cut out of the DBD::Oracle documentation from active-state. The first part of the code  builds a demo package with several types of stored procedures. The second part of the code demonstrates (with 4 examples) how to call the stored procedures with different types of parameter passing.

Cadabra.




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














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




# PART I

  # 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;
  };


# PART II

  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.

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.