How to pass values from php as xml formatted input for calling a pl/sql function

Question

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

Answer 1

It is not clear for me WHY you want to change your input data into xml format and pass it this way to function, but you can do it like here: create function with xmltype parameter and use extractvalue to get all values. I also used variable rw payroll%rowtype to reduce code length.

create or replace function save_payroll_row_xml (i_xml in xmltype) return varchar2 is

  rw payrollfile%rowtype;
  result varchar2(10) ;

begin

  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;

end;

Then call your function like here:

declare 
  result varchar2(10);
begin 
  result := save_payroll_row_xml(
    xmltype('
      <transaction>
        <salary_year>2012</salary_year>
        <salary_month>05</salary_month>
        <employee_id>707</employee_id>
        <department_code>30</department_code>
        <salary_head>str</salary_head>
        <description>something</description>
        <amount>102</amount>
        <operator_id>7</operator_id>
        <transaction_date>2015-12-20</transaction_date>
      </transaction>'));
  dbms_output.put_Line(result);
end;

Notes:

• use prepared statements to avoid SQL injection,
• be careful with date format sent to database - it has to correspond with Oracle settings,
• in my example I ommited exception handling, you can leave it as in your code, but it should be improved (try to avoid when others). But that's not the main topic of your question,
• I'm not experienced in PHP, so I could misunderstood something.