| Converting XMLTYPE data to CLOB and getClobVal() (merged 3) [message #318473] |
Tue, 06 May 2008 23:52  |
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]
|
|
|
| Re: getClobVal() returns a null [message #318477 is a reply to message #318473 ] |
Wed, 07 May 2008 00:21  |
rajatratewal
Messages: 97
Registered: March 2008
Location: INDIA |
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 |
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 |
rajatratewal
Messages: 97
Registered: March 2008
Location: INDIA |
Member |
|
|
Try this:-
xmltype.getclobval(lHTMLOutput)
|
|
|
|
| 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 |
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 #318843 is a reply to message #318831 ] |
Thu, 08 May 2008 03:32 |
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 |
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 |
Michel Cadot
Messages: 15244
Registered: March 2007
Location: Nanterre, France, http://... |
Senior Member |
|
|
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 |
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 |
bloodhound
Messages: 9
Registered: May 2008 |
Junior Member |
|
|
I have got the solution...
This can be done using XMLDOM package... 
|
|
|
]
Members
Search
Help
Register
Login
Home
it gives the same error...