XML with PL/SQL and CLOB newbie:

[sirnose1]

I have a table with two columns (acronym, description). I can put them into an xml format, however I need to store them in a temp table. Due to the large amount of data, i have deduced that i need to use a "CLOB". Unfortunately, I have never done this. Here is the code:

DECLARE

v_acronym tblacronym.acronym%type;
v_description tblacronym.description%type;
v_sad tblacronym.sad%type;
v_tad tblacronym.tad%type;
v_ed tblacronym.ed%type;
v_owq tblacronym.owq%type;
V_cs tblacronym.cs%type;
v_cmd tblacronym.cmd%type;
v_clob CLOB;

CURSOR tad_acronyms IS
SELECT *
from tblacronym
WHERE tad = 'TAD';

CURSOR sad_acronyms IS
SELECT *
FROM tblacronym
WHERE sad = 'SAD';

CURSOR ed_acronyms IS
SELECT *
FROM tblacronym
WHERE ed = 'ED';

CURSOR owq_acronyms IS
SELECT *
FROM tblacronym
WHERE owq = 'OWQ';

CURSOR cs_acronyms IS
SELECT *
FROM tblacronym
WHERE cs = 'CS';

CURSOR cmd_acronyms IS
SELECT *
FROM tblacronym
WHERE cmd = 'CMD';

BEGIN

/*Timeline Analysis (tad) */
v_clob = v_clob||<ResourceDetails>||
dbms_output.put_line('<ResourceDetails>');
dbms_output.put_line('<ResourceTitle>'||'Timeline Analysis'||'</ResourceTitle>');


for i in tad_acronyms LOOP

dbms_output.put_line('<AcronymDetails>');
dbms_output.put_line('<acronymName>'||i.acronym||'</acronymName>');
dbms_output.put_line('<acronymDescription>'||i.description||'</acronymDescription>');
dbms_output.put_line('</acronymDetails>');

END LOOP;

dbms_output.put_line('</ResourceDetails>');

/*System Architecture (sad)*/

dbms_output.put_line('');
dbms_output.put_line('<ResourceDetails>');
dbms_output.put_line('<ResourceTitle>'||'System Architecture'||'</ResourceTitle>');

for i in sad_acronyms LOOP

dbms_output.put_line('<AcronymDetails>');
dbms_output.put_line('<acronymName>'||i.acronym||'</acronymName>');
dbms_output.put_line('<acronymDescription>'||i.description||'</acronymDescription>');
dbms_output.put_line('</acronymDetails>');
END LOOP;

dbms_output.put_line('</ResourceDetails>');

/*Event Detection (ed)*/

dbms_output.put_line('');
dbms_output.put_line('<ResourceDetails>');
dbms_output.put_line('<ResourceTitle>'||'Event Detection'||'</ResourceTitle>');

for i in ed_acronyms LOOP

dbms_output.put_line('<AcronymDetails>');
dbms_output.put_line('<acronymName>'||i.acronym||'</acronymName>');
dbms_output.put_line('<acronymDescription>'||i.description||'</acronymDescription>');
dbms_output.put_line('</acronymDetails>');
END LOOP;

dbms_output.put_line('</ResourceDetails>');

/*Online Water Quality (owq)*/

dbms_output.put_line('');
dbms_output.put_line('<ResourceDetails>');
dbms_output.put_line('<ResourceTitle>'||'Online Water Quality'||'</ResourceTitle>');

for i in owq_acronyms LOOP

dbms_output.put_line('<AcronymDetails>');
dbms_output.put_line('<acronymName>'||i.acronym||'</acronymName>');
dbms_output.put_line('<acronymDescription>'||i.description||'</acronymDescription>');
dbms_output.put_line('</acronymDetails>');
END LOOP;

dbms_output.put_line('</ResourceDetails>');



/*Contaminant Selection (cs) */

dbms_output.put_line('');
dbms_output.put_line('<ResourceDetails>');
dbms_output.put_line('<ResourceTitle>'||'Contaminant Selection'||'</ResourceTitle>');

for i in cs_acronyms LOOP

dbms_output.put_line('<AcronymDetails>');
dbms_output.put_line('<acronymName>'||i.acronym||'</acronymName>');
dbms_output.put_line('<acronymDescription>'||i.description||'</acronymDescription>');
dbms_output.put_line('</acronymDetails>');
END LOOP;

dbms_output.put_line('</ResourceDetails>');

/* Consequence Management (cmd) */

dbms_output.put_line('');
dbms_output.put_line('<ResourceDetails>');
dbms_output.put_line('<ResourceTitle>'||'Consequence Mangement'||'</ResourceTitle>');

for i in ed_acronyms LOOP

dbms_output.put_line('<AcronymDetails>');
dbms_output.put_line('<acronymName>'||i.acronym||'</acronymName>');
dbms_output.put_line('<acronymDescription>'||i.description||'</acronymDescription>');
dbms_output.put_line('</acronymDetails>');
END LOOP;

dbms_output.put_line('</ResourceDetails>');

end;
/

 

[Thargy]

Sirnose,

I'm having a little difficulty understanding the need for the clob in your post. I believe that you have a table of abbreviated acronyms and their verbose text descriptions. Is this correct?

If it is, can you confirm that you need > 4,000 characters to describe each acronym? Can you post the structure of the table in question, and some sanitised data (i.e. remove any references to things confidential) so that I can check it over?

Regards

Tharg

Grinding away at things Oracular

 

[sirnose1]

Figured it out. Due to the number of records and storing it in the XML format, the CLOB was necessary. I couldn't use dbms_output.putline. Here is what I came up with:


DECLARE

v_id tblacronym.id%type;
v_acronym tblacronym.acronym%type;
v_description tblacronym.description%type;
v_sad tblacronym.sad%type;
v_tad tblacronym.tad%type;
v_ed tblacronym.ed%type;
v_owq tblacronym.owq%type;
V_cs tblacronym.cs%type;
v_cmd tblacronym.cmd%type;
v_clob CLOB;

CURSOR tad_acronyms IS
SELECT *
from tblacronym
WHERE tad = 'TAD';

CURSOR sad_acronyms IS
SELECT *
FROM tblacronym
WHERE sad = 'SAD';

CURSOR ed_acronyms IS
SELECT *
FROM tblacronym
WHERE ed = 'ED';

CURSOR owq_acronyms IS
SELECT *
FROM tblacronym
WHERE owq = 'OWQ';

CURSOR cs_acronyms IS
SELECT *
FROM tblacronym
WHERE cs = 'CS';

CURSOR cmd_acronyms IS
SELECT *
FROM tblacronym
WHERE cmd = 'CMD';

BEGIN

/*Timeline Analysis (tad) */


for i in tad_acronyms LOOP

v_clob := ('<ResourceDetails>'||'<ResourceTitle>'||'Timeline_Analysis'||'</ResourceTitle>'||'<AcronymDetails>'||'<acronymName>'||i.acronym||'</acronymName>'||'<acronymDescription>'||i.description||'</acronymDescription>'||'</acronymDetails>'||'</ResourceDetails>');

insert into t_ap_clob values(acro_id.nextval, v_clob);

END LOOP;


for i in sad_acronyms LOOP

v_clob := ('<ResourceDetails>'||'<ResourceTitle>'||'System_Architecture'||'</ResourceTitle>'||'<AcronymDetails>'||'<acronymName>'||i.acronym||'</acronymName>'||'<acronymDescription>'||i.description||'</acronymDescription>'||'</acronymDetails>'||'</ResourceDetails>');

insert into t_ap_clob values(acro_id.nextval, v_clob);

END LOOP;


for i in ed_acronyms LOOP

v_clob := ('<ResourceDetails>'||'<ResourceTitle>'||'Event_Detection'||'</ResourceTitle>'||'<AcronymDetails>'||'<acronymName>'||i.acronym||'</acronymName>'||'<acronymDescription>'||i.description||'</acronymDescription>'||'</acronymDetails>'||'</ResourceDetails>');

insert into t_ap_clob values(acro_id.nextval, v_clob);

END LOOP;


for i in owq_acronyms LOOP

v_clob := ('<ResourceDetails>'||'<ResourceTitle>'||'Online_Water_Quality'||'</ResourceTitle>'||'<AcronymDetails>'||'<acronymName>'||i.acronym||'</acronymName>'||'<acronymDescription>'||i.description||'</acronymDescription>'||'</acronymDetails>'||'</ResourceDetails>');

insert into t_ap_clob values(acro_id.nextval, v_clob);

END LOOP;

for i in cs_acronyms LOOP

v_clob := ('<ResourceDetails>'||'<ResourceTitle>'||'Contaminant_Selection'||'</ResourceTitle>'||'<AcronymDetails>'||'<acronymName>'||i.acronym||'</acronymName>'||'<acronymDescription>'||i.description||'</acronymDescription>'||'</acronymDetails>'||'</ResourceDetails>');

insert into t_ap_clob values(acro_id.nextval, v_clob);

END LOOP;

for i in cmd_acronyms LOOP

v_clob := ('<ResourceDetails>'||'<ResourceTitle>'||'Consequence_Management'||'</ResourceTitle>'||'<AcronymDetails>'||'<acronymName>'||i.acronym||'</acronymName>'||'<acronymDescription>'||i.description||'</acronymDescription>'||'</acronymDetails>'||'</ResourceDetails>');

insert into t_ap_clob values(acro_id.nextval, v_clob);

END LOOP;



end;

Is there a better way to do this?

 

[Thargy]

Sirnose,

I'm still not exactly sure what you're trying to achieve, but if XML is the problem, have you checked out oracle's XML DB package?

Regards

Tharg

Grinding away at things Oracular