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?
 
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.

0
 
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 ?
0
 
unsalAuthor Commented:
yes I mean I have a stored procedure in the database that I want to pass parameters thats right.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.