I am new in php, XML, pl/SQL.I made a pl/SQL function which is stored inside a php file. This function takes some values from php in XML format and inserts them to oracle table the pl/SQL function works fine without XML format.Problem arise when i give it in XML format.
I got this errors.Image Sample given below:
I want to take php variables in XML format whenever i call pl/SQL function.How i do this?
Here is the php Code given below latest :
<?php
$conn = oci_connect('usr', '123', 'localhost/xe');
if (!$conn)
{
trigger_error("Could not connect to database", E_USER_ERROR);
}
else
{
echo "Connection established";
}
insertTable($conn); //This to insert date into table each time.
function insertTable($conn)
{
$year = $_POST["SALYR"];
$month = $_POST["SALMT"];
$empID = $_POST["EMPID"];
$dptCode = $_POST["DPTID"];
$salHD = 'SH';
$description = $_POST["DESCR"][0];
$amount = $_POST["ALAMT"][0];
$optID = 3501;
$trDate = date("Y/m/d", strtotime(date("Y/m/d")));
$function_define = "create or replace FUNCTION save_payroll_row (i_xml in xmltype) return varchar2 is
rw payrollfile%rowtype;
result varchar2(10) ;
v_code NUMBER;
v_errm varchar2(600);
select extractvalue(i_xml, '/transaction/salary_year'),
extractvalue(i_xml, '/transaction/salary_month'),
extractvalue(i_xml, '/transaction/employee_id'),
extractvalue(i_xml, '/transaction/department_code'),
extractvalue(i_xml, '/transaction/salary_head'),
extractvalue(i_xml, '/transaction/description'),
extractvalue(i_xml, '/transaction/amount'),
extractvalue(i_xml, '/transaction/operator_id'),
extractvalue(i_xml, '/transaction/transaction_date')
into rw.SALYR, rw.SALMT, rw.EMPID, rw.DPTID, rw.SALHD, rw.DESCR, rw.ALAMT, rw.OPID, rw.TRADT from dual;
insert into PAYROLLFILE (SALYR, SALMT, EMPID, DPTID, SALHD, DESCR, ALAMT, OPID, TRADT)
values (rw.SALYR, rw.SALMT, rw.EMPID, rw.DPTID,rw.SALHD, rw.DESCR, rw.ALAMT, rw.OPID, rw.TRADT);
result := 'success';
return result;
Exception
When others then
v_code := SQLCODE;
v_errm := SUBSTR(SQLERRM,1, 600);
DBMS_OUTPUT.PUT_Line('Error Code' || v_code || ': ' || v_errm);
end save_payroll_row;";
$stid = oci_parse($conn, $function_define);
oci_execute($stid);
$function_call = " declare
result varchar2(10);
declare
result varchar2(10);
begin
result := save_payroll_row(
xmltype('
<transaction>
<salary_year>$_POST["SALYR"]</salary_year>
<salary_month>$_POST["SALMT"]</salary_month>
<employee_id>$_POST["EMPID"]</employee_id>
<department_code>$_POST["DPTID"]</department_code>
<salary_head>$salHD</salary_head>
<description>$description</description>
<amount>$amount</amount>
<operator_id>$optID</operator_id>
<transaction_date>$trDate</transaction_date>
</transaction>'));
dbms_output.put_Line(result);
end;" ;
$stid = oci_parse($conn, $function_call);
oci_execute($stid);
}
As a beginner, I think i definitely made few syntax & date format related mistakes for which i have no clue to fix this.please let me know for any further information.Thanks