Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to call a sql function in perl

Status
Not open for further replies.

sam009

Programmer
Mar 1, 2006
2
US
Hi everybody,

When i am trying to run below sql in perl, i am getting an error message. In the sql i am trying to run a function. Sql is working fine in sqlplus command prompt.
Is there any problem in my syntax?
If i run "select sysdate from dual" sql, it works fine. Does it make a difference that actual sql is calling a function. Do i have to prepare this sql differently? ANY IDEAS.

Part of the script,

my $dbh = DBI->connect('dbi:Oracle:', $ENV{LOG}, $ENV{PWD}, undef)
or die "Unable to connect to DB: $DBI::errstr\n";
my $sth = $dbh->prepare(EXEC wag_batch_ddl.table_stats('patient'))
or die "Couldn't prepare statement: " . $dbh->errstr;

$sth->execute() # Execute the query
or die "Couldn't execute statement: " . $sth->errstr;

$dbh->disconnect;

This is the ERROR message:

Can't locate object method "EXEC" via package "wag_batch_ddl" (perhaps you forgot to load "wag_batch_ddl"?) at my_practice_script1.pl line 18.
/var/area51/pbm/coredss/ai_env/private_sand/dss_extract/bin
 
You need to wrap your SQL inside quotes. The prepare sub-routine in DBI accepts a string which it parses to get it ready for sending to the database.

- George
 
Thanks for replying George. I tried the quotes before but it did not work. I tried it again.

When i using single quote:
my $sth = $dbh->prepare('EXEC wag_batch_ddl.table_stats('pbm_eob_patient')')
or die "Couldn't prepare statement: " . $dbh->errstr;

Getting this error message:
Bad name after pbm_eob_patient' at my_practice_script1.pl line 18.

when i am using double quote for sql:
my $sth = $dbh->prepare("EXEC wag_batch_ddl.table_stats('pbm_eob_patient')")
or die "Couldn't prepare statement: " . $dbh->errstr;

Getting this error message:
DBD::Oracle::st execute failed: ORA-00900: invalid SQL statement (DBD ERROR: OCI StmtExecute) [for Statement "EXEC wag_batch_ddl.table_stats('pbm_eob_patient')"]at my_practice_script1.pl line 22.

This statement works fine:
my $sth = $dbh->prepare('select sysdate from dual')
or die "Couldn't prepare statement: " . $dbh->errstr;
 
Try this

my $sql = qq[EXEC wag_batch_ddl.table_stats('pbm_eob_patient')];
my $sth = $dbh->prepare($sql);
$sth->execute();
 
OK...

sam009 said:
When i using single quote:
my $sth = $dbh->prepare('EXEC wag_batch_ddl.table_stats('pbm_eob_patient')')
or die "Couldn't prepare statement: " . $dbh->errstr;

This wouldn't work because you used a single quote and didn't escape the single quotes in your statement.

sam009 said:
when i am using double quote for sql:
my $sth = $dbh->prepare("EXEC wag_batch_ddl.table_stats('pbm_eob_patient')")
or die "Couldn't prepare statement: " . $dbh->errstr;

Getting this error message:
DBD::Oracle::st execute failed: ORA-00900: invalid SQL statement (DBD ERROR: OCI StmtExecute) [for Statement "EXEC wag_batch_ddl.table_stats('pbm_eob_patient')"]at my_practice_script1.pl line 22.

Since it's failing on the execute, it looks to me like the Oracle::DBD module doesn't handle running this type of function. I'd suggest referring to the documentation to ensure that these types of functions can be run via the DBD::Oracle module.

- George
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top