Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

Huge consume of tablespace temp using pipeline function

Avatar of apuertas
apuertas asked on
Oracle Database
5 Comments1 Solution1661 ViewsLast Modified:
I am developing a way to generate xml files from tables information in oracle 10g. I found the pipeline table function a good manner of doind this king of job.
The problem is that I have some tables with a big quantity of rows and it is throwing the oracle error UNABLE TO EXTEND TEMP TABLESPACE.

My code is the following:
CREATE OR REPLACE TYPE clob_table IS TABLE OF CLOB ;
CREATE OR REPLACE FUNCTION dyn_rows(p_query IN VARCHAR2) RETURN clob_table PIPELINED IS
l_xml XMLTYPE ;
BEGIN
l_xml := XMLTYPE(dbms_xmlgen.getxml(p_query)) ;
FOR rec IN (SELECT * FROM   TABLE(xmlsequence(l_xml.extract('/ROWSET/ROW'))))
LOOP
    PIPE ROW (rec.column_value.getCLOBVal()) ;
END LOOP;
END;

The reason why i am using TABLE OF CLOB  if because in same case the result is bigger than the maximun size of the varchar2 type.
In the view V$TEMPSEG_USAGE, i can watch that the consume is increasing exponentially because of the use of clobs.
Does anyone know how to manage this situation?
Thanks in advance
ASKER CERTIFIED SOLUTION
Avatar of apuertas
apuertas

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Commented:
This problem has been solved!
Unlock 1 Answer and 5 Comments.
See Answers