-
How to get SQL data into XML format
Hi,
I am trying to select data from the emp table and i want to display the data in XML format. I am having problems with the examples
*** stroing the result into another table temp_clon_tab as CLOB
*** How to extract the result from CLOB?
CREATE TABLE temp_clob_tab(result CLOB)
1 DECLARE
2 qryCtx DBMS_XMLGEN.ctxHandle;
3 result CLOB;
4 BEGIN
5 qryCtx := dbms_xmlgen.newContext
6 ('SELECT * from emp;');
7 DBMS_XMLGEN.setRowTag(qryCtx, 'EMPLOYEE');
8 DBMS_XMLGEN.setMaxRows(qryCtx, 5);
9 LOOP
10 result := DBMS_XMLGEN.getXML(qryCtx);
11 EXIT WHEN
12 DBMS_XMLGEN.getNumRowsProcessed(qryCtx)=0;
13 INSERT INTO temp_clob_tab VALUES(result);
14 END LOOP;
15* END;
I got the following error!
SQL> /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-00911: invalid character
How can i print the XML format CLOB output, instead of inserting into table.
Many Thanks,
-
try this
DECLARE
--qryCtx DBMS_XMLGEN.ctxHandle;
type a is ref cursor;
c a;
qryCtx number;
result CLOB;
BEGIN
open c for SELECT * from emp;
qryCtx := dbms_xmlgen.newContext (c);
DBMS_XMLGEN.setRowTag(qryCtx, 'EMPLOYEE');
DBMS_XMLGEN.setMaxRows(qryCtx, 5);
LOOP
result := DBMS_XMLGEN.getXML(qryCtx);
EXIT WHEN
DBMS_XMLGEN.getNumRowsProcessed(qryCtx)=0;
INSERT INTO temp_clob_tab VALUES(result);
END LOOP;
END;
Cheers
Slash
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|