How to call a sql function in perl

[sam009]

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

 

[Rieekan]

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

 

[sam009]

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;

 

[vjcyrano]

Try this

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

 

[Rieekan]

OK...

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.

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:BD 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