Example for DBMS_SQL

CREATE OR REPLACE PROCEDURE PROC_EVENT_TAB_VALUE_MAIL

(

P_BU VARCHAR2,

P_ALERT_ID VARCHAR2,

P_QUERY VARCHAR2,

P_MAIL_COL VARCHAR2,

P_REP_FLG VARCHAR2,

P_MAIL_ID OUT VARCHAR2,

P_VALUE OUT VARCHAR2

)

IS

Cursor c1 is

SELECT alrmap_filed_id,alrmap_filed_column

FROM alert_mail_attach_params

WHERE alrmap_bu = P_BU

AND alrmap_alert_id=P_ALERT_ID

AND alrmap_filed_id <> 'BU';

source_cursor NUMBER;

v VARCHAR2(4000);

tab_col VARCHAR2(4000);

rec_tab dbms_sql.desc_tab;

col_cnt PLS_INTEGER;

result NUMBER;

c NUMBER;

d NUMBER;

LC$Query VARCHAR2(4000):=P_QUERY;

LC$Line VARCHAR2(4000);

LC$TAB VARCHAR2(4000);

BEGIN

dbms_output.put_line(LC$Query);

c := dbms_sql.open_cursor;

dbms_sql.parse(c, LC$Query , 1);

d := dbms_sql.EXECUTE(c);

dbms_sql.describe_columns(c, col_cnt, rec_tab);

source_cursor := dbms_sql.open_cursor;

dbms_sql.parse(source_cursor, LC$Query, 1);

FOR i IN 1 .. rec_tab.last LOOP

dbms_sql.DEFINE_COLUMN(source_cursor, i, v,4000);

END LOOP ;

result := dbms_sql.EXECUTE(source_cursor);

LOOP

IF dbms_sql.FETCH_ROWS(source_cursor)>0 THEN

FOR i IN 1.. rec_tab.last LOOP

dbms_sql.COLUMN_VALUE(source_cursor, i, v);

LC$Line :=LC$Line||' '||NVL(v,' ') ;

IF REC_TAB(i).col_name=P_MAIL_COL THEN

P_MAIL_ID:=NVL(v,' ');

END IF;

IF P_REP_FLG='Y' THEN

FOR CR1 IN C1 LOOP

IF REC_TAB(i).col_name=CR1.alrmap_filed_column THEN

UPDATE ALERT_MAIL_ATTACH_PARAMS

SET ALRMAP_COL_VALUE=NVL(v,' ')

WHERE ALRMAP_BU = P_BU

AND ALRMAP_ALERT_ID=P_ALERT_ID

AND ALRMAP_FILED_COLUMN=CR1.ALRMAP_FILED_COLUMN;

END IF;

END LOOP;

END IF;

END LOOP ;

LC$TAB:=LC$TAB||CHR(10)||LC$Line;

ELSE

EXIT;

END IF;

END LOOP;

P_VALUE:=LC$TAB;

dbms_sql.close_cursor(source_cursor);

END;

/