How to fix error INTO clause is expected in this SELECT statement

Question

I am new in pl/sql and xml. I want fetch data as xml format from a table by calling user define function:

This the table sql:

CREATE TABLE "HIMADRI"."PAYROLLFILE" 
   (    "SALYR" NUMBER(4,0) NOT NULL ENABLE, 
    "SALMT" NUMBER(2,0) NOT NULL ENABLE, 
    "EMPID" NUMBER NOT NULL ENABLE, 
    "DPTID" NUMBER NOT NULL ENABLE, 
    "SALHD" VARCHAR2(2 BYTE) NOT NULL ENABLE, 
    "DESCR" VARCHAR2(50 BYTE) NOT NULL ENABLE, 
    "ALAMT" FLOAT(126) NOT NULL ENABLE, 
    "OPID" NUMBER NOT NULL ENABLE, 
    "TRADT" DATE);

I want to get data in following xml format:

  <transaction>
            <salary_year>SALYR</salary_year>
            <salary_month>SALMT</salary_month>
            <employee_id>EMPID</employee_id>
            <department_code>DPTID</department_code>
            <salary_head>SALHD</salary_head>
            <description>DESCR</description>
            <amount>ALAMT</amount>
            <operator_id>OPID</operator_id>
            <transaction_date>TRADT</transaction_date>
</transaction>

How should i write pl/sql function body:

FUNCTION get_all_payroll_transactions RETURN  VARCHAR2 IS  isSuccess VARCHAR2(50);

   BEGIN 
     SELECT XMLElement( "transaction",  
                    XMLElement("salary_year", SALYR),
                    XMLElement("salary_month", SALMT),
                    XMLElement("employee_id", EMPID),
                    XMLElement("department_code", DPTID),
                    XMLElement("salary_head", SALHD),
                    XMLElement("description", DESCR),
                    XMLElement("amount", ALAMT),
                    XMLElement("operator_id", OPID),
                    XMLElement("transaction_date", TRADT)

                 )AS transaction1
    FROM PAYROLLFILE ;
   END get_all_payroll_transactions;

How should i call the function from php:

$stid = oci_parse($conn, " begin   
                               :result :=  PAYROLL.get_all_payroll_transactions();                      

                                end;" );    

    oci_bind_by_name($stid, ':result',$ru, 500);    
    oci_execute($stid);

please give me clue.Thanks

Answer 1

Hi not sure about the call from PHP but when you do a select in a plsql block you need to select it into a variable so in this case it would look something like the below. You also need to explicitly return that variable. Also as you are selecting XML you are going to need a substantially bigger variable than VARCHAR2(50). Below is untested but gives you an idea the direction to head in.

FUNCTION get_all_payroll_transactions RETURN  VARCHAR2 IS
  isSuccess VARCHAR2(4000);

   BEGIN 
     SELECT XMLElement( "transaction",  
                    XMLElement("salary_year", SALYR),
                    XMLElement("salary_month", SALMT),
                    XMLElement("employee_id", EMPID),
                    XMLElement("department_code", DPTID),
                    XMLElement("salary_head", SALHD),
                    XMLElement("description", DESCR),
                    XMLElement("amount", ALAMT),
                    XMLElement("operator_id", OPID),
                    XMLElement("transaction_date", TRADT)

                 )AS transaction1
    into isSuccess
    FROM PAYROLLFILE ;

  Return isSuccess;
   END get_all_payroll_transactions;