Solved

How to execute a procedure on oraperl

Posted on 1998-08-18
9
987 Views
Last Modified: 2008-02-01
Hi, my question is conserning oraperl.
On SQL I can call and execute a procedure like this:
SQL> EXECUTE package_name.procedure_name('var1','var2',var3',...)

What is the equevalent on oraperl?

I use oraperl.pm  v1.37:  Oraperl Emulation Interface for perl 5 DBD::oracle DBI

If I do  the statement below it does'nt work !

$lda = &ora_login($dbname, $dbuser, $passwd)
$command = "EXECUTE package_name.procedure_name('var1','var2',var3',...) ";
$csr = &ora_open($lda, $command);
&ora_close($csr);
&ora_commit($lda)
&ora_logoff($lda)

Please tell me the rigth way  to do
Thank you very much.
0
Comment
Question by:helida21
  • 4
  • 4
9 Comments
 
LVL 4

Expert Comment

by:mitek
ID: 1081122
Here is the code from one perl library I wrote:

sub dda_alist {  my ($l,$tab_name,$rec_id,$alist) = @_;
  my (%anames,@alist,$aname,$ddesc,$row);  defined $l || return ();
  $tab_name || return ();  $rec_id = $rec_id || '';  $alist = $alist || '';
  $c = &ora_open($l,"BEGIN dda.dda_alist(:1,:2,:3); END;");
  $c->bind_param_inout(1, \$tab_name,32) || die "bind_param: $ora_errstr";
  $c->bind_param_inout(2, \$alist,32000) || die "bind_param: $ora_errstr";
  $c->bind_param_inout(3, \$rec_id,255) || die "bind_param_out: $ora_errstr";
  $c->execute || die "execute: $ora_errstr";  &ora_close($c);
  @alist = split /\n/,$alist;  for $row (@alist) {
    ($aname,$ddesc) = split /\t/,$row,2;    $anames{$aname} = $ddesc;  }
  return %anames;}

You may also check here for more code:
http://www.sarov.com/webmaster/dda.pl.txt

There is no logon/logoff calls in these functions. It is assumed (for speed reasons) that these functions receive an initialized $lda variable as one of parameters.

0
 

Author Comment

by:helida21
ID: 1081123
Thank Mitek for answering my question,  but I didn't understand what it represent the variable $rec_id, $alist, and what does it mean "1, \table_name,32". Can you give me more specification please.  I just start to work with oraperl.

In my situation, I have a package body : " adm.user_record_pg"  and a procedure :
 " create_user (inp_first_name VARCHAR2,  inp_last_nameVARCHAR2, inp_phoneVARCHAR2)".
The procedure will add a new record to the table "record" and will add the action to an orther table call "history"

I want to use oraperl to add a new user to my table "record" by calling the procedure "create_user"

what is the simply way to do it?

Thank a lot for your help

Helida
0
 
LVL 4

Expert Comment

by:mitek
ID: 1081124

here is the code for your situation
$lda = &ora_login($dbname, $dbuser, $passwd)
$c = &ora_open($lda,"BEGIN adm.create_user(:1,:2,:3); END;");
$c->bind_param_inout(1, \$first_name,32) || die "bind_param: $ora_errstr";
$c->bind_param_inout(2, \$last_name,32) || die "bind_param: $ora_errstr";
$c->bind_param_inout(3, \$phone,20) || die "bind_param_out: $ora_errstr";
$c->execute || die "execute: $ora_errstr";  
&ora_close($c);
&ora_commit($lda)
&ora_logoff($lda)

the process is not that simple as you might think, you won't get away with &ora_open($lda"EXECUTE package_name.procedure_name('var1','var2',var3',...)")

here is how you do it
a) open a complete PL/SQL block with ALL the code you need, substituting each variable that is supposed to be set/retrieved with :1, :2, :3, and so on (or simply :varname). Of course, the code can be as complex as you like -- maybe, it even makes sense to shift some loops from perl into the PL/SQL block for speed.
Then, open a cursor (this will PARSE the block w/o executing it)
syntax errors in PL/SQL code will surface usually at this step

$c = &ora_open($lda,"BEGIN adm.create_user(:1,:2,:3); END;");

After that, each "coloned" variable has to be bound to a corresponding perl variable. This is done via bind_param_inout function.

$c->bind_param_inout(1, \$first_name,32)

here, 1 corresponds to :1 variable in PL/SQL block
\$first_name to perl variable (you'll need a backslash to get a reference to the variable, instead of its value)
32 is maximum length the variable is expected to handle

$c->execute will actually execute the block

after successful execution, if "coloned" variables were modified within the block, it will be reflected in corresponding perl variables -- that's why binding is done before the execution. It allows the SP to basically modify perl variables.

of course, you could just use
$c->bind_param_in or $c->bind_param_out, if you need only to pass a var to block or to only read its modified value. with $c->bind_param_inout you can both pass something in and retrieve this value modified.

0
 

Author Comment

by:helida21
ID: 1081125
Thank Mitek, is more clear now, But I have an orther little problem with "->bind_param_inout"
when I execute my program on Unix, I got this message

 Can't call method "bind_param_in" without a package or object reference at test.pl line 38.

I declared these library at the begining of my program:
use lib '/usr/local/perl5/lib/';
use CGI;
use DBI;

What are  other libraries shoud I declare?

Thank a lot for your time.


0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:helida21
ID: 1081126
Thank Mitek, is more clear now, But I have an orther little problem with "->bind_param_inout"
when I execute my program on Unix, I got this message

 Can't call method "bind_param_in" without a package or object reference at test.pl line 38.

I declared these library at the begining of my program:
use lib '/usr/local/perl5/lib/';
use CGI;
use DBI;

What are  other libraries shoud I declare?

Thank a lot for your time.


0
 
LVL 4

Expert Comment

by:mitek
ID: 1081127
I used this entire block to initialize ...


################ Oraperl initialization block ###########################
# Initialize Oraperl DCD library (v. 0.38)                              #
eval 'use Oraperl; 1' || die $@ if $] >= 5;                             #
# Get Oraperl error codes                                               #
require ('oraperl.ph');                                                 #
# Check if Oraperl is available                                         #
die ("Oraperl Library is not Available\n") unless defined &ora_login;   #
delete($ENV{'ORA_NLS32'});                                              #
#########################################################################  
0
 
LVL 4

Expert Comment

by:mitek
ID: 1081128
Also, make sure that ORACLE_HOME and ORACLE_SID environment variables are exported. something like that.
sub ora_env
{
    $ENV{'ORACLE_SID'} = "casio";
    $ENV{'ORACLE_HOME'} = "/export/home/oracle7";
    $ENV{'ORACLE_BASE'} = "/export/home/oracle7";
    $ENV{'ORACLE_TERM'} = "386x";
    $ENV{'ORACLE_OWNER'} = "oracle";
    delete $ENV{'ARCH'};
    1;
}

I remember the error you are talking about, and I found some way around it. I don't remember how -- it's been long ago. Do some research yourself. In DCD directory there is a perl test file that is run right after DCD Oraperl install. It makes use of these functions. So, the best way would be to figure out why it works in test.pl and doesn't in your script ...
Hope, that helped.

0
 
LVL 7

Accepted Solution

by:
yoren earned 30 total points
ID: 1081129
helida,

Try the code below. It's fairly simple, and it's worked well for me:

$var1 = "'string1'";
$var2 = 54; # some number

$lda = &ora_login($dbname, $dbuser, $passwd) or die "$ora_errstr";
$command = "BEGIN\npackage_name.procedure_name($var1,$var2);\nEND;";
$status = &ora_do($lda, $command) or die "$ora_errstr";
&ora_commit($lda);
&ora_logoff($lda);

0
 

Author Comment

by:helida21
ID: 1081130
Thank a lot Yoren and Mitek;

Finally I can solve my problem with the solution of Yoren.  Is work very wel. Thank a lot.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Oracle 12c 10 100
select query - oracle 16 81
Cannot open form error 6 47
SQL Retrieve Values 4 41
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now