How to call a oracle function which returns a ref cursor.

[Guest_imported]

Hi

I am able to connect to oracle database and insert records by calling a SP.(Stored procedure). This is the program for that which is working fne.

if (open (infl,"$indir/$filenm&quot)
{

$dbh=DBI->connect("DBI:Oracle:BSCSDEV",mnp,mnp789) or "$!\n";

while ($record=<infl>)
{
chomp ($record);
$rec++;
#print &quot;$record \n&quot;;
@param = split('\|',$record);

$r=&quot;&quot;; # Return value of SP
$inp1 = $param[0]; #in param for SP
$inp2 = $param[1];
$inp3 = $param[2];
$inp4 = $param[3];
$inp5 = $param[4];
$inp6 = $param[5];
$inp7 = $param[6];
$inp8 = $param[7];

#print &quot;$inp1 $inp2 $inp3 $inp4 $inp5 $inp6 $inp7 $inp8
\n&quot;;
$sqlqry=$dbh->prepare (q{
BEGIN
mvno_create_npr( :inp1, :inp2, :inp3, :inp4, :inp5, :inp
6, :inp7, :inp8, :r);
END;});

$r = 0;
$sqlqry->bind_param(&quot;:inp1&quot;,$inp1);
$sqlqry->bind_param(&quot;:inp2&quot;,$inp2);
$sqlqry->bind_param(&quot;:inp3&quot;,$inp3);
$sqlqry->bind_param(&quot;:inp4&quot;,$inp4);
$sqlqry->bind_param(&quot;:inp5&quot;,$inp5);
$sqlqry->bind_param(&quot;:inp6&quot;,$inp6);
$sqlqry->bind_param(&quot;:inp7&quot;,$inp7);
$sqlqry->bind_param(&quot;:inp8&quot;,$inp8);
$sqlqry->bind_param_inout(&quot;:r&quot;,\$r,1);
$sqlqry->execute;




Same way I want to call a function which returns a ref cursor. How to call this function. The following are the parameters for the function. Also the output of the cursor i want to store in a file.

Please help on this at the earliest




variable CUR refcursor
execute :CUR := select_mvno_npr_port_in('SHELL', '20011031', '01:00')
print CUR

 

[hmerrill]

I'm pretty sure the answer to your question can be found in the perldocs for the DBD::Oracle module. At a command prompt, do

$ perldoc DBD::Oracle

and search(using the &quot;/&quot for &quot;stored&quot;.

HTH.
Hardy Merrill
Mission Critical Linux, Inc.

http://www.missioncriticallinux.com