| 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] Report message to a moderator |
|
|
|
|
|
| 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: 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...
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
it gives the same error...
