dou29106 2015-12-29 11:43
浏览 78

如何从php传递值作为xml格式的输入来调用pl / sql函数

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: enter image description here

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

  • 写回答

1条回答 默认 最新

  • dongzan9069 2015-12-29 14:23
    关注

    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.
    评论

报告相同问题?

悬赏问题

  • ¥15 MATLAB动图问题
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题