Home » Developer & Programmer » JDeveloper, Java & XML » Converting XMLTYPE data to CLOB and getClobVal() (merged 3)
Converting XMLTYPE data to CLOB and getClobVal() (merged 3) [message #318473] Tue, 06 May 2008 23:52 Go to next message
bloodhound
Messages: 9
Registered: May 2008
Junior Member
I am pulling data from a table, converting it to an XML and then converting the XMLType to CLOB using the getClobVal() function and subsequently i have to send that HTML file as an attachment in a mail.

When i use a small table having max of 40 rows then it is getting executed but when i use a table with more than 40 rows, this function returns a null value.

Have tried with getStringval also but the problem persists.

Can anybody tell what can be the problem or any alternative to it.

[Updated on: Tue, 06 May 2008 23:54]

Report message to a moderator

Re: getClobVal() returns a null [message #318477 is a reply to message #318473] Wed, 07 May 2008 00:21 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
can you show us what you have tried.
Re: getClobVal() returns a null [message #318482 is a reply to message #318473] Wed, 07 May 2008 00:31 Go to previous messageGo to next message
bloodhound
Messages: 9
Registered: May 2008
Junior Member
CREATE OR REPLACE PACKAGE BODY CONVERT_XML_TO_HTML AS

function GENERATE_HTML(TABLE_NAME VARCHAR2, FILE_NAME VARCHAR2, STYLESHEET_QUERY VARCHAR2, WHERE_CLAUSE VARCHAR2, ORDERBY_CLAUSE VARCHAR2) RETURN CLOB AS
lHTMLOutput XMLType;
--HTML_OUTPUT VARCHAR2(4000);
lXSL CLOB;
lXMLData XMLType;
FILEID UTL_FILE.FILE_TYPE;
HTML_RESULT CLOB;
SQL_QUERY VARCHAR2(300);
WHERE_QUERY VARCHAR2(200);
fileDirectory VARCHAR2(100);
slashPosition NUMBER;
actual_fileName VARCHAR2(100);
XML_HTML_REF_CUR_PT XML_HTML_REF_CUR;
ECODE NUMBER(38);

BEGIN



IF WHERE_CLAUSE IS NOT NULL AND ORDERBY_CLAUSE IS NOT NULL THEN

SQL_QUERY := 'SELECT * FROM ' || TABLE_NAME ||' WHERE ' || WHERE_CLAUSE || ' ORDER BY ' || ORDERBY_CLAUSE;

ELSE IF WHERE_CLAUSE IS NOT NULL AND ORDERBY_CLAUSE IS NULL THEN

SQL_QUERY := 'SELECT * FROM ' || TABLE_NAME || ' WHERE ' || WHERE_CLAUSE;

ELSE IF WHERE_CLAUSE IS NULL AND ORDERBY_CLAUSE IS NOT NULL THEN

SQL_QUERY := 'SELECT * FROM ' || TABLE_NAME || ' ORDER BY ' || ORDERBY_CLAUSE;

ELSE IF WHERE_CLAUSE IS NULL AND ORDERBY_CLAUSE IS NULL THEN

SQL_QUERY := 'SELECT * FROM ' || TABLE_NAME;

END IF;
END IF;
END IF;
END IF;
OPEN XML_HTML_REF_CUR_PT FOR SQL_QUERY;

lXMLData := GENERATE_XML(XML_HTML_REF_CUR_PT);

DBMS_OUTPUT.PUT_LINE('XML RESULT GENERATED');

lHTMLOutput := lXMLData.transform(XMLType(STYLESHEET_QUERY));

DBMS_OUTPUT.PUT_LINE('IN CON_XML_HTML');

HTML_RESULT := lHTMLOutput.getClobVal();

-- If the filename has been supplied ...
IF FILE_NAME IS NOT NULL THEN

-- locate the final '/' or '\' in the pathname ...
slashPosition := INSTR(FILE_NAME, '/', -1 );

IF slashPosition = 0 THEN
slashPosition := INSTR(FILE_NAME,'\', -1 );
END IF;

-- separate the filename from the directory name ...
fileDirectory := SUBSTR(FILE_NAME, 1,slashPosition - 1 );
actual_fileName := SUBSTR(FILE_NAME, slashPosition + 1 );
END IF;

DBMS_OUTPUT.PUT_LINE(fileDirectory||' ' ||actual_fileName);

FILEID := UTL_FILE.FOPEN(fileDirectory,actual_fileName, 'W',32767);

UTL_FILE.PUT_LINE(FILEID, '<title> hi </title>');
UTL_FILE.PUT_LINE(FILEID, HTML_RESULT);

UTL_FILE.FCLOSE (FILEID);

RETURN HTML_RESULT;

EXCEPTION
WHEN OTHERS THEN
ECODE:=SQLCODE;
DBMS_OUTPUT.PUT_LINE(SQLCODE||'ERROR!!!!!!!!!!!!');

END GENERATE_HTML;

FUNCTION GENERATE_XML(XML_HTML_REF_CUR_PT XML_HTML_REF_CUR) RETURN XMLType IS

QUERYCTX DBMS_XMLGEN.CTXHANDLE;
RESULT XMLType;
RESULT1 CLOB;

BEGIN
QUERYCTX := DBMS_XMLGEN.NEWCONTEXT(XML_HTML_REF_CUR_PT);
DBMS_XMLGEN.setNullHandling(QUERYCTX,2);
RESULT := DBMS_XMLGEN.GETXMLTYPE(QUERYCTX,DBMS_XMLGEN.NONE);
RESULT1:=result.getclobval(); --DBMS_XMLGEN.GETXML(QUERYCTX);
DBMS_OUTPUT.PUT_LINE('CHECKING THE RESULT'||result1);
iNSERT INTO XML_CHK VALUES(1,XMLTYPE(RESULT1));
RETURN RESULT;
END GENERATE_XML;

END CONVERT_XML_TO_HTML;


The line which is blue in colour is creating a probelm.
Re: getClobVal() returns a null [message #318492 is a reply to message #318482] Wed, 07 May 2008 00:53 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Try this:-

xmltype.getclobval(lHTMLOutput)
Re: getClobVal() returns a null [message #318497 is a reply to message #318492] Wed, 07 May 2008 00:57 Go to previous messageGo to next message
bloodhound
Messages: 9
Registered: May 2008
Junior Member
Sad it gives the same error...
how to convert XMLTYPE data to CLOB without using getClobVal() function [message #318828 is a reply to message #318473] Thu, 08 May 2008 02:28 Go to previous messageGo to next message
bloodhound
Messages: 9
Registered: May 2008
Junior Member
Please tell me how to convert data which is stored in the table in XMLTYPE column to a CLOB.

When i use getClobVal(), i get an error. So please tell me some other option except getClobVal().
Re: how to convert XMLTYPE data to CLOB without using getClobVal() function [message #318831 is a reply to message #318828] Thu, 08 May 2008 02:48 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
bloodhound wrote on Thu, 08 May 2008 09:28
Please tell me how to convert data which is stored in the table in XMLTYPE column to a CLOB.

When i use getClobVal(), i get an error. So please tell me some other option except getClobVal().



Is that the way you work? Every time you encounter an error, instead of solving you look for another solution or work-around?

Maybe if you had posted the error, we could help you solving it.
Re: how to convert XMLTYPE data to CLOB without using getClobVal() function [message #318843 is a reply to message #318831] Thu, 08 May 2008 03:32 Go to previous messageGo to next message
bloodhound
Messages: 9
Registered: May 2008
Junior Member
Its not the error i get...

I am converting a html table to XML and then using the stylesheet m gettin the HTML output which is in XMLTYPE...further i have to send that file as an attachment thru my procuedure.

Now when i use getClobVal to get it converted...it simply does not convert for a table with having more than 40 rows but when i use it for a table with less than 40 rows...it works fine...

That was my approach...nw if u think that smthn else can be done then let me know...
Is is possible to write XMLTYPE to a File in Oracle [message #319071 is a reply to message #318473] Thu, 08 May 2008 23:05 Go to previous messageGo to next message
bloodhound
Messages: 9
Registered: May 2008
Junior Member
Is is possible to write XMLTYPE data to a file in Oracle 10g.

If it is possible kindly share the code for the same.
Re: Is is possible to write XMLTYPE to a File in Oracle [message #319098 is a reply to message #319071] Fri, 09 May 2008 01:04 Go to previous messageGo to next message
Michel Cadot
Messages: 59814
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, use getClobVal function to convert it into a clob then split it and use utl_file to write into a file.

Regards
Michel
Re: Is is possible to write XMLTYPE to a File in Oracle [message #319100 is a reply to message #319098] Fri, 09 May 2008 01:08 Go to previous messageGo to next message
bloodhound
Messages: 9
Registered: May 2008
Junior Member
I am not able to use getclobval for larger values... i mean for larger data this function des'nt work...

please tell me some other alternative...
Re: Is is possible to write XMLTYPE to a File in Oracle [message #319151 is a reply to message #319071] Fri, 09 May 2008 03:21 Go to previous message
bloodhound
Messages: 9
Registered: May 2008
Junior Member
I have got the solution...

This can be done using XMLDOM package... Razz
Previous Topic: for ORACLE+XML
Next Topic: XMLTypes as in, out from JSP page
Goto Forum:
  


Current Time: Thu Nov 27 21:26:07 CST 2014

Total time taken to generate the page: 0.20803 seconds