Hi, Im stuck with a problem, and dont know what the best solution would be.
I want to get the output from a select statement against v$rman_output and load it into a clob, so that I can then send that clob onto utl_smtp or utl_file, because it's easier to refer to a big block of text (like a logfile) using a clob, esp. when sending a mail where I just want to write v_body := v_clob. I don't want to run the SQL from the file system and simple spool the output to an OS file: I want to initiate the SQL from within a stored procedure, then write to an OS file. Maybe im over-thinking this, but my current thought process goes something like this...
DECLARE
TYPE tab_vc
IS
TABLE OF NVARCHAR2(4000);
v_contract tab_vc;
v_clob NCLOB;
BEGIN
dbms_output.enable(40000);
-- temp lob cause i dont need a permenant one
dbms_lob.createtemporary (v_clob, TRUE);
-- get the rman output into the pl/sql table using bulk collect
SELECT output
||chr(10) bulk collect
INTO v_contract
FROM v$rman_output
WHERE rman_status_stamp=v_stamp
ORDER BY recid;
-- load each row in pl/sql table into CLOB variable
FOR i IN 1..v_contract.count
LOOP
dbms_lob.writeappend(v_clob, LENGTH(v_contract(i)), v_contract(i));
END LOOP;
-- output the CLOB and free up the temporary lob
dbms_output.put_line(v_clob);
dbms_lob.freetemporary(v_clob);
END;
/
This is working great, but i'm only selecting one column to load into the pl/sql table. Now I want to load multiple columns into the pl/sql table with another select, but I can't figure out if I can create a multi-column pl/sql table, or if I should concatenate all of the columns into one big row to insert into the single column pl/sql table (and lose formatting), or if there is another way to do this?
EDIT: Ok, I read in the manual that pl/sql tables can only have one column. So should I concatenate the columns to load into the pl/sql table (seems like an ugly solution), or should I use something like a Record Type?