 XMLTYPE : CAST(MULTISET....) help ^^ [message #184654] |
Thu, 27 July 2006 06:35  |
ms_bee
Messages: 5
Registered: July 2006
Location: indonesia
|
Junior Member |
|
|
hi all,
can anyone help me?
here's the thing :
------------------
CREATE TABLE Basic(
ApplicationNo varchar2(10),
Domain varchar2(1),
Description varchar2(100)
constraint pk_basic PRIMARY KEY ApplicationNo);
CREATE TABLE Applicant(
ApplicationNo varchar2(10),
ApplicantName varchar2(30),
DueDate date
constraint fk_applicant FOREIGN KEY (ApplicationNo)
REFERENCES Basic(ApplicationNo) ENABLE);
CREATE TABLE Creator(
ApplicationNo varchar2(10),
CreatorName varchar2(40),
CreatorAddress varchar2(200)
constraint fk_creator FOREIGN KEY (ApplicationNo)
REFERENCES Basic(ApplicationNo) ENABLE);
i want to create an xml file with DBMS_XMLGEN, so i create xmltype :
--------------------------------------------------------------------
create or replace TYPE BASIC_TYP AS OBJECT(
"ApplicationNo" varchar2(10),
"Domain" varchar2(1),
Description varchar2(100));
create or replace TYPE APPLICANT_TYP AS OBJECT(
"ApplicantName" varchar2(120),
"DueDate" date);
create or replace TYPE ApplicantList_Typ AS TABLE OF APPLICANT_TYP;
create or replace TYPE CREATOR_TYP AS OBJECT(
"CreatorName" varchar(40),
"CreatorAddress" varchar2(200));
create or replace TYPE CREATORLIST_TYP AS TABLE OF CREATOR_TYP;
i 'expect' that my xml would look like this:
-----------------------------------------
<root>
<row>
<BasicTable>
<ApplicationNo>1111</ApplicationNo>
<Domain>A</Domain>
<Description>description no 1</Description>
</BasicTable>
<ApplicantTable>
<row>
<ApplicantName>John</ApplicantName>
<DueDate>20060806</DueDate>
</row>
<row>
<ApplicantName>David</ApplicantName>
<DueDate>20060301</DueDate>
</row>
<row>
<ApplicantName>Pricilla</ApplicantName>
<DueDate>20060104</DueDate>
</row>
</ApplicantTable>
<CreatorTable>
<row>
<CreatorName>Diana</CreatorName>
<CreatorAddress>Road No 1</CreatorAddress>
</row>
<row>
<CreatorName>Michael</CreatorName>
<CreatorAddress>Road No 2</CreatorAddress>
</row>
</CreatorTable>
</row>
</root>
so i create a function :
------------------------
procedure getXML(newContext_qry varchar2,filename varchar2)
is
begin
declare
qryCtx dbms_xmlgen.ctxHandle ;
result clob;
lob_length integer;
read_amount integer;
read_offset integer;
buffer varchar2(100);
loc varchar2(100) := 'usr_dir';
f_hand utl_file.file_type;
begin
-- Setting up offset and no. of chars to be read in
-- in one go from clob datatype.
read_offset := 1;
read_amount := 75;
dbms_output.put_line('opening');
--Opening file
f_hand := utl_file.fopen('ORALOAD',filename,'w');
dbms_output.put_line('file open');
-- Creating new context
qryCtx := dbms_xmlgen.newContext(
'SELECT BASIC_TYP(A.DOMAIN, A.APPLICATIONNO, A.DESCRIPTION) ' ||
' AS "BasicTable", ' ||
' CAST(MULTISET(SELECT B.APPLICANTNAME, B.DUEDATE ' ||
' FROM APPLICANT B ' ||
' WHERE B.APPLICATIONNO = A.APPLICATIONNO) ' ||
' AS APPLICANTLIST_TYP) AS "ApplicantTable", ' ||
' CAST(MULTISET(SELECT C.CREATORNAME, C.CREATORADDRESS ' ||
' FROM CREATOR C ' ||
' WHERE C.APPLICATIONNO = A.APPLICATIONNO) ' ||
' AS CREATORLIST_TYP) AS "CreatorTable" ' ||
' FROM BASIC A '|| newContext_qry);
-- Defining Rowsettag
DBMS_XMLGEN.setRowSetTag(qryCtx,'root');
-- Defining Rowtag
DBMS_XMLGEN.setRowTag(qryCtx,'row');
-- Null Handling Option
DBMS_XMLGEN.setNullHandling(qryCtx, 2);
-- Generating XML and storing in an clob datatype
result :=DBMS_XMLGEN.getXML(qryCtx);
dbms_output.put_line('xml generated');
-- Getting the length of the data stored in Clob
lob_length := dbms_lob.getlength(result);
-- Reading data from clob variable and writng into file.
while (lob_length > 0) loop
dbms_lob.read(result,read_amount,read_offset,buffer);
dbms_output.put_line('writing in file');
utl_file.put(f_hand,buffer);
dbms_output.put_line('written');
read_offset := read_offset+read_amount;
lob_length := lob_length-read_amount;
if lob_length < read_amount then
read_amount := lob_length;
end if;
end loop;
utl_file.fclose(f_hand);
EXCEPTION WHEN UTL_FILE.INVALID_PATH THEN
RAISE_APPLICATION_ERROR(-20100,'Invalid Path');
WHEN UTL_FILE.INVALID_MODE THEN
RAISE_APPLICATION_ERROR(-20101,'Invalid Mode');
WHEN UTL_FILE.INVALID_OPERATION then
RAISE_APPLICATION_ERROR(-20102,'Invalid Operation');
WHEN UTL_FILE.INVALID_FILEHANDLE then
RAISE_APPLICATION_ERROR(-20103,'Invalid Filehandle');
WHEN UTL_FILE.WRITE_ERROR then
RAISE_APPLICATION_ERROR(-20104,'Write Error');
WHEN UTL_FILE.READ_ERROR then
RAISE_APPLICATION_ERROR(-20105,'Read Error');
WHEN UTL_FILE.INTERNAL_ERROR then
RAISE_APPLICATION_ERROR(-20106,'Internal Error');
WHEN OTHERS THEN
UTL_FILE.FCLOSE(f_hand);
end;
end;
instead of what i expected, i have this result :
-----------------------------------
<root>
<row>
<BasicTable>
<ApplicationNo>1111</ApplicationNo>
<Domain>A</Domain>
<Description>description no 1</Description>
</BasicTable>
<ApplicantTable>
<APPLICANT_TYP> -----------> here's my problem T-T
<ApplicantName>John</ApplicantName>
<DueDate>20060806</DueDate>
</APPLICANT_TYP>
<APPLICANT_TYP>
<ApplicantName>David</ApplicantName>
<DueDate>20060301</DueDate>
</APPLICANT_TYP>
<APPLICANT_TYP>
<ApplicantName>Pricilla</ApplicantName>
<DueDate>20060104</DueDate>
</APPLICANT_TYP>
</ApplicantTable>
<CreatorTable>
<CREATOR_TYP> -----------> here's my problem T-T
<CreatorName>Diana</CreatorName>
<CreatorAddress>Road No 1</CreatorAddress>
</CREATOR_TYP>
<CREATOR_TYP>
<CreatorName>Michael</CreatorName>
<CreatorAddress>Road No 2</CreatorAddress>
</CREATOR_TYP>
</CreatorTable>
</row>
</root>
so, my problem is, i cannot rename APPLICANT_TYP and CREATOR_TYP into "row"
does Oracle support this?
i've tried to rename the tag :
CAST(MULTISET((SELECT B.APPLICANTNAME, B.DUEDATE
FROM APPLICANT B
WHERE B.APPLICATIONNO = A.APPLICATIONNO) AS "row")
AS APPLICANTLIST_TYP) AS "ApplicantTable"
but i get error missing right parentesis, etc...
i need to know, is it possible to rename multiset tag or not in Oracle?
if it is possible, can someone tell me how?
(^_________________^)
thanks
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
