Calling stored procedures is currently not defined by the DBI.
Some drivers, such as DBD::Oracle, support it in non-portable
ways.
See driver documentation for more details.)

I don't know all the libraries but I believe in most there is no method to pass an generic array as a parameter. Of course - if you are using PostreSQL stored procedures in perl than you can pass parameters as usuall.

I am also not very familiar with all libraries, but i guess calling stored procedures is supported by using DBI and DBD::Oracle.

But if at all there is no method to pass arrays to a SP, is there no other alternative way which can solve the problem mentioned in an optimized manner ?

how about any obfuscations which can aim at this...curious..

Expecting some kind of enlightenment from the monk community to resolve this issue...

I'm still not really sure what you're trying to do, but maybe bind_param_array is what you're looking for. Note this statement from the docs, though:

Note that the default implementation currently only supports non-data returning statements.

If you want to get data back from Oracle stored procedures, you need to call bind_param_inout but this method doesn't take an array as an argument. If the values you want to pass are in an array, just pass the array elements.

If you are using Oracle then the most effective way would be to use the Oracle data importing tool to do the job. I can't racall the exact name of it - but I remember it can import data in a few formats (and CSV is one of them) and do the most needed conversions.
Update: I've overlooked that you need to call the procedure to do the additionall computation.

I know little about DBI and even less about Oracle, but I just came across DBIx::Procedure::Oracle on CPAN which has this description:

DBIx::Procedure::Oracle - Call PL/SQL stored procedures and functions without writing SQL or needing to know about data types or bindings.

That said, it goes on to talk about using positional parameters (:n) and not named parameters. It queries the definition of the stored procedure from the DB itself and then uses that to decide how many and what type the parameters are. So, it would seem to me that the problem you have is how to define a stored procedure in Oracle to accept a large number of values as arguments without needing to name or number them all indivually? That's very much an Oracle question not a Perl one.

Taking a quick look at the pod for DBD::Oracle (*This has a few PL/SQL examples that might be illuminating for you.) and trying to extrapolate from my long unused DB2 knowledge, I seem to recall that the usual way to handle the type of processing that you are talking about is to do a standard INSERT of the data records into seperate table that is either created on the fly or is defined simply for the purpose of receiving the raw data, and then call the stored procedure to process the raw data from that table into the real tables, possibly removing it from the raw storage table as it goes. That way, the large volumes of values are bound to the INSERT statement in the normal way, and then the procedure can be invoked with just the name of the TABLE that it is to process.

As I said at the top, I am on really shaky ground with this stuff, but it really sounds like your problem is one of how to define a stored procedure to accept large numbers of parameters in a convenient way--an Oracle/SQL problem-- rather than perl one.

Could you tell us some details of what you want to do?

Abigail

I am sorry that my statement was quite abstract.

The perl program i am working on is supposed to read a file containing some comma-separated values and load into a table.

Each line of the file containing the comma-separated values represent one record in a table.(there will be nearly 100 such lines). There is a stored procedure which has certain computations and finally inserts the values into the table.

I need to call this stored procedure from the perl program and pass it the records.

thot of storing the Comma-separated values of each record into an array. But as u pointed out, if arrays cannot be passed to stored procedure...then how to go about doing it??

I hope the experience of the monks will help me clear this block...quite urgent, so plz share ur knowledge in this area.

I think part of the problem we're having is that you are expecting the stored procedure to know what to do with with a Perl array. You need to feed the stored procedure what it likes to eat; not being a stored proc guru, I can't say precisely what stored procs like to eat, but I'm guessing it's not Perl arrays.

How would the stored procedure know how to unpack the array? Perhaps you could do some sort of binary packing that could then be unpacked by the stored proc, but that's not the kind of thing I'd use a stored procedure for.

My suggestion is to feed each record to the stored procedure individually. This is a pretty common thing to do.

By the way, 100 records is nothing. That's test data where I come from. When you start talking in millions and portions of millions things get interesting.

Also, it seems to me that stuffing everything into an array, passing that array to the proc and having the proc process each record in the array and then insert it is very, very similar to letting perl unpack the array and pass in each record to the proc for further processing and insertion.

I'm sure this isn't particularly helpful, but may shed some light on a solution for you.

Regards,

Cat

You would do exactly what you do when calling a subroutine in Perl: you pass it a list of arguments. Read the manual page of DBI, or whatever database interface you are using about how to pass parameters to stored procedures.

But I wouldn't do it that way. It's so.... not SQL-ish. I'd use the database's bulk copy feature to read in the CSV file into a temporary table. Then I'd modify the stored procedure to read the data from the temp table and insert it into the real table. No Perl needed, unless the database isn't able to read in a CSV file (most databases can). In which case I'd write a simple DBI program, using DBI::CVS to read in the CVS file, and DBI::whatever to write it to the temp table.

Abigail

# 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";
[download]

Substitute your own parameters like this

 
  # @parameter is the array with 3 parameters

  $csr = $db->prepare(q{
      BEGIN
          SQL_PROC_NAME(:1,:2,:3);
      END;
  });

  $csr->bind_param(":1", $parameter[0] );
  $csr->bind_param(":2", $parameter[1] );
  $csr->bind_param(":3", $parameter[2] );
 
  eval {
    $csr->execute;
  };
[download]

poj

If i use individual values instead of a complete array, this will mean i'll have to run this code and execute the procedure for each record insertion, which is be highly inefficient when no. of records is some 100!!

Instead, what i am looking for is parsing the CSV and getting all records into an array and passing it in one-go to the Stored procedure(HOW?) which will then loop through it and insert accordingly

...anyway, thanx!

Thanks for the code posted!...but how is it relevant to the problem at hand ?

What is required is the same kind of Stored Procedure parameter passing to be done, but when the parameter is an array of values.

All new ideas welcome...

Ok your probally doing something like this:



open (CSVFILE, input.csv);
while (!eof(CSVFILE)) {
   $currLine = <CSVFILE>;
   split /\,/, $currLine, @curline;
   dbi_sub (@currline);
}
[download]



where dbi_sub will feed that to your stored proc. Now as said before I would just have oracle do this make a PL/SQL function that takes a single varchar(255) as a parameter, being the name of the textfile and read it in. But another method would be to pass the $currLine that contains a string of parameters seperated by commas and have your stored procedure parse it. But you never answered the $25,000 question, why aren't you having oracle do this for you. It can and more effecietly than perl probally, but then again the great stored procedures debate is something for the grand council of DB admin and RDMS coder wizards to debate.

 
     CREATE OR REPLACE PROCEDURE (?????(the array cannot be used ) IN)
+ as

     You can only use 

     CREATE OR REPLACE PROCEDURE (v_scalr_in IN) as
[download]

        my $a;
        foreach (@arrayofvariables){
           $a .= $_;
        }

        my $proc_run = "BEGIN
                           run_proc(:1)
                        END\;" 

        # then bind it with $a
[download]

  CREATE OR REPLACE PROCEDURE ( v_scalar_in IN VARCHAR) AS 

      here you have to substr with the index of "," and then send it i
+n for loop for your processing
[download]

Passing and getting array parameters/return values If we want to pass or return arrays, DBI binding does not support it directly. However, there is a trick how we can do it, both for input parameters, and return/output parameters. This is basically idea:

a) For output parameters, we use reference cursor instead of directly returning the array ( or any other collection). We will show an example for array, but you can easily make it for associative array.

b) For input parameters, we do not try to bind them directly. Instead, we loop through Perl array/hash, and create a string, which can be used in PL/SQL Perl block string. So we simply call the PL SQL procedure/function directly.

Example: We define the type STRING_TABLE on the schema level, in the schema TAXNET:

CREATE OR REPLACE
TYPE STRING_TABLE AS TABLE OF VARCHAR2(50);
……

We define the following function in the package RV_TEST:

  FUNCTION collectionInputExampleTable( inputList IN STRING_TABLE) RET
+URN sys_refcursor AS
    tableCollection STRING_TABLE:=STRING_TABLE();
    retCur sys_refcursor;
  BEGIN
    
    FOR i IN 1..inputList.count LOOP
      tableCollection.extend(1);
      tableCollection(i):=inputList(i);
      --outputList(i):=inputList(i);
      --DBMS_OUTPUT.put_line('ELEMENT:'||inputList(i));
    END LOOP;
    
    OPEN retCur FOR SELECT * FROM TABLE (tableCollection);
    
    return retCur;
    
  EXCEPTION WHEN OTHERS THEN
      txnvs_db_util.tx_log( 3, 2, SQLCODE, SQLERRM, null );
      RAISE;
  END collectionInputExampleTable;
[download]

Input array parameter:

The example shows what we need to do:

…..
print "\n============Stored Procedure=========\n";

my @arrParam=('Dusica 1','Dusica 2',"Dusica 3","Dusica 4","Dusica 5");


my $counter=0;
my $plSqlCodeBlock = "
      DECLARE
          inArr    TAXNET.STRING_TABLE:=TAXNET.STRING_TABLE('";
    foreach my $arrEl( @arrParam )
    {
        $plSqlCodeBlock .=$arrEl;
        if($counter<scalar @arrParam-1)
        {
            $plSqlCodeBlock .="','";
        }
        $counter++;
    }
      $plSqlCodeBlock .=    "');
      BEGIN 
           :retVal:= TAXNET.RV_TEST.collectionInputExampleTable(inArr)
+;
      END;"; 

#print "$plSqlCodeBlock\n\n\n";

my $sth = $dbHandle->prepare($plSqlCodeBlock);
my $csr; 

$sth->bind_param_inout( ":retVal", \$csr, 0, { ora_type => ORA_RSET } 
+);
$sth->execute();

while(@row = $csr->fetchrow_array)
{
        #$csr->finish;    
    print "\nList Element:$row[0]";
    
}    

$csr->finish;
$sth->finish;


$dbHandle->disconnect;

print "\n============End=========<<<<<\n";
…..
[download]

Output array parameter/returned value:

We see how we define the cursor to return the collection. We cannot define cursor directly with associative array, but we can “copy” the associative array to the nested table type variable, and after that we can define the cursor with nested table elements. In the function above, we can change inputList to bean associative array too. Now it is easy to get the reference cursor in Perl. We did it in the stoed function above, and this is the line:

…..
    OPEN retCur FOR SELECT * FROM TABLE (tableCollection);
…..
[download]

Re: Passing Arrays from Perl to Stored Procedures by Anonymous Monk on May 14, 2012 at 21:06 UTC This article is created by Ratimir Vukicevic - Sorry, did not pay attention how to put my "signature". Anyway the name does not matter, only if I need it for work reference somewhere ... ( hopefully not )