Hi,
I am new in this forum. I am developing an email program which is sending 4 attachments based on the 4 different queries. Example:
Attachment 1:
v_rows CLOB;
For i in (select EMPNO, EMPNAME, EMPADDR from emp where dept = 10)
LOOP
v_rows := v_rows
|| i.EMPNO
|| ','
|| i.EMPNAME
|| ','
|| i.EMPADDR
|| UTL_TCP.crlf;
END LOOP;
Attachment 2
v_rows := NULL;
For i in (select DEPTNO, DEPTNAME from DEPT where DEPT > 10)
LOOP
v_rows := v_rows
|| i.DEPTNO
|| ','
|| i.DEPTNAME
|| UTL_TCP.crlf;
END LOOP;
and so on upto 4 attachment
I am storing all these resultset in clob recordset.
Is there any way to make it a single block which will run 4 times in a loop and generate the resultset. Particularly how can I create this block in generic way:
For i in (select DEPTNO, DEPTNAME from DEPT where DEPT > 10)
LOOP
v_rows := v_rows
|| i.DEPTNO
|| ','
|| i.DEPTNAME
|| UTL_TCP.crlf;
1) cursor query can be changed and
2) how can I address i.DEPTNO for writing in LOB?
You could use some DBMS_SQL for this, eg
SQL> create or replace
2 procedure clob_maker(p_query varchar2) is
3
4 l_theCursor integer default dbms_sql.open_cursor;
5 l_columnValue varchar2(4000);
6 l_status integer;
7 l_descTbl dbms_sql.desc_tab;
8 l_colCnt number;
9 n number := 0;
10
11 l_data clob;
12 begin
13 dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
14 dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
15
16 for i in 1 .. l_colCnt loop
17 dbms_sql.define_column(l_theCursor, i, l_columnValue, 4000);
18 end loop;
19
20 l_status := dbms_sql.execute(l_theCursor);
21
22 while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
23 for i in 1 .. l_colCnt loop
24 dbms_sql.column_value( l_theCursor, i, l_columnValue );
25 l_data := l_data || l_columnValue ||',';
26 end loop;
27 n := n + 1;
28 end loop;
29 dbms_output.put_line(l_data);
30 end;
31 /
Procedure created.
SQL>
SQL> set serverout on
SQL> exec clob_maker('select empno, ename from emp');
7369,SMITH,7499,ALLEN,7521,WARD,7566,JONES,7654,MARTIN,7698,BLAKE,7782,CLARK,7788,SCOT
T,7839,KING,7844,TURNER,7876,ADAMS,7900,JAME
S,7902,FORD,7934,MILLER,
PL/SQL procedure successfully completed.