CODEWITHSUNDEEP
phpxmlplsql
user5005768Himadree
user5005768Himadree

Reputation: 1427

PLSQL PHP: How to take large xml data from PLSQL function to PHP

I want to fetch all employee id from the Oracle database using PLSQL function into php.I've arround 10000 employee IDs each size is varchar2(10)

Here is my PLSQL function Code:

FUNCTION view_emp_list_by_office  return  clob IS
ret xmltype;
BEGIN                 
SELECT  xmlelement("employee_id_list",                   
              XMLAGG(                    
                   xmlelement("employee",
                   xmlelement("emp_id", EMP_ID)                   
                   )ORDER BY EMP_NAME ASC
              )
) as clob into ret
FROM TABLE_EMPLOYEE_INFO;

RETURN '<result><status >success</status>'||ret.getClobval()||'</result>'; 
EXCEPTION
WHEN OTHERS THEN
RETURN  '<result><status>Error</status></result>'; 

END view_emp_list_by_office;

Here is the PHP code:

$stid = oci_parse($conn, " begin   
       :result :=  package1.view_emp_list_by_office;  
                      end;" );      
    oci_bind_by_name($stid, ':result',$ru, 247800);
    $output = oci_execute($stid);

The code works fine if xml data size not huge.But when i try to fetch all the employee IDs then it don't work.It looks like a Memory issue.How solve this? do i need any code modification to do that.please help me.

Upvotes: 2

Views: 63

Answers (1)

romulos
romulos

Reputation: 268

I got a problem in the past but performing a stored procedure and exporting it to a file. Is not the same case but... Check your memory_limit. I think it comes with 64mb by default

Upvotes: 1

Related Questions