I'm a newbie Perl programmer and I have been looking at the documentation
for calling an Oracle package from Perl. From this and with the advice of
others here, I wrote some code which I thought would do the trick. I think
I'm close but it's just not quite there. Can anyone see what I'm doing
wrong?? HELP? Here is my code...
USE DBI;
# Define the output FILE AND its path
$outfile1 = "cursor_test_1.txt";
# Define the logon variables FOR AWASUN7
$INSTANCE = "database_name";
$username = "username";
$password = "password";
# OPEN a connection TO DBC DATABASE
$dbh = DBI->CONNECT("DBI:Oracle:$instance",
"$username",
"$password",
{RaiseError => 1, PrintError => 1, AutoCommit =>
1});
$dbh->func(1000000,'dbms_output_enable');
$dbh->func(@text,'dbms_output_put');
# Define the SQL STATEMENT TO retrieve the data
$pl_sql_1 = "BEGIN
Cursor_Test_Pkg.test_reacom_comp
END";
$sth_1 = $dbh->prepare($pl_sql_1);
$sth_1->EXECUTE();
$sth_1->finish();
@text = $dbh->func('dbms_output.put_get');
print STDOUT (@text);
$dbh->disconnect();
Once run the only output to be retrieved is the number of records (counter)
that were updated. The error I receive when I run the program is as
follows...
DBD::Oracle::st execute failed: ORA-06550: line 3, column 7:
PLS-00103: Encountered the symbol "END" when expecting one of the following:
:= . ( @ % ; (DBD ERROR: OCIStmtExecute) at package_call.pl line 38.
The package compiled without error and running it as an anonymous block
update the records as expected. Package code...
CREATE OR REPLACE PACKAGE Cursor_Test_Pkg IS
PROCEDURE test_reacom_comp;
END Cursor_Test_Pkg;
CREATE OR REPLACE PACKAGE BODY Cursor_Test_Pkg IS
-- The following PROCEDURE defines the scenario for records in the PAX_HDR
-- that HAVE REACOM records and that HAVE compensation.
PROCEDURE test_reacom_comp IS
-- This CURSOR will LOOP thru the pax records from 4 days ago to present.
CURSOR reacom_comp_cur IS
SELECT DISTINCT ph.pax_hdr_id
FROM PAX_HDR ph, PAX_REMARKS_DET prm, PAX_COMP_DET pc, FLIGHT_HDR fh
WHERE fh.flt_hdr_id = ph.flt_hdr_id
AND ph.pax_hdr_id = prm.pax_hdr_id
AND ph.pax_hdr_id = pc.pax_hdr_id
AND ph.arr_late_gate = 'N'
AND pc.arr_one_hour = 'N'
AND pc.comp_amt = 0
AND EXISTS (
SELECT 1
FROM PAX_REACOM_DET pr
WHERE ph.pax_hdr_id = pr.pax_hdr_id
AND fh.flt_num = pr.flt_num
AND fh.dep_dttm = pr.flt_dttm);
ctr_1 NUMBER := 0;
BEGIN
-- LOOP thru the pax records from 4 days ago to present.
FOR reacom_comp_rec IN reacom_comp_cur LOOP
-- Count records to be UPDATED.
ctr_1 := ctr_1 + 1;
dbms_output.put_line(ctr_1||' record(s) updated.');
-- Update the field 'TRUE_DB' = 'N' since these records are NOT TRUE denied
boardings.
UPDATE PAX_HDR
SET true_db = 'N'
WHERE pax_hdr_id = reacom_comp_rec.pax_hdr_id;
END LOOP;
-- This is just a double check to show how many records will be updated as
well as how many total records there are for this period.
dbms_output.put_line('The total NUMBER OF records TO be UPDATED IS
'||ctr_1);
COMMIT;
END test_reacom_comp;
END Cursor_Test_Pkg;
Am I calling this prodedure right? Any suggestions??
Thanks in Advance,
Bob.
