Dynamic Sql Query

DECLARE
P_CUR SYS_REFCURSOR;
v_key_value  varchar2(100) default  ‘GF6IQQ5B’;
    v_cursor_id integer;
    v_col_cnt integer;
    v_columns dbms_sql.desc_tab;
v_sql varchar2(32767) ;
v_sql_sonuc varchar2(32767) ;
v_barkod_text clob;
— İLK ÖNCE SUTUN ADLARI ALINIYOR
TYPE bin_array IS TABLE OF VARCHAR2(150)   INDEX BY BINARY_INTEGER;
 string_array  bin_array;
 fdbk INTEGER;
BEGIN
select B.TX_SOURCE_SQL, B.TX_BARKOD_SABLON INTO v_sql, v_barkod_text FROM KARGO.MNGKARGO_BARKOD_SET B
WHERE B.CH_BARKOD_ADI=’GONDERI_BARKOD’
AND B.FL_DURUM=1;
v_sql := REPLACE(v_sql,'{AnahtarValue}’,v_key_value);
    — CURSOR ALAN ADLARININ ALINMASI
    v_cursor_id := dbms_sql.open_cursor;
    dbms_sql.parse(v_cursor_id, v_sql, dbms_sql.native);
    dbms_sql.describe_columns(v_cursor_id, v_col_cnt, v_columns);
   ———————————————————————
        for i in 1 .. v_columns.count loop
           –dbms_output.put_line(v_columns(i).col_name);
        string_array(i) := v_columns(i).col_name;
        end loop;
        for i in 1 .. v_columns.count loop
        DBMS_SQL.DEFINE_COLUMN(v_cursor_id,i, v_columns(i).col_name,300);
        –dbms_output.put_line(v_columns(i).col_name || ‘ — ‘   );
        end loop;
  fdbk:= DBMS_SQL.EXECUTE (v_cursor_id);
   LOOP
      /* Try to fetch next row. If done, then exit the loop. */
      EXIT WHEN DBMS_SQL.FETCH_ROWS (v_cursor_id) = 0;
     if v_sql_sonuc is not null then
     v_sql_sonuc := v_sql_sonuc || ‘ UNION ALL’;
     end if;
        for i in 1 .. v_columns.count loop
          DBMS_SQL.COLUMN_VALUE(v_cursor_id,i, v_columns(i).col_name);
          — dbms_output.put_line(string_array(i) || ‘ => ‘|| v_columns(i).col_name    );
          v_barkod_text := REPLACE(v_barkod_text,'{‘|| string_array(i) ||’}’,v_columns(i).col_name);
        end loop;
        v_sql_sonuc := v_sql_sonuc || ‘ SELECT ”’ || v_barkod_text || ”’ EPL FROM DUAL ‘;
       —  dbms_output.put_line(v_barkod_text);
   END LOOP;
   OPEN P_CUR FOR v_sql_sonuc;
    :RC_LISTE := P_CUR;
    dbms_sql.close_cursor(v_cursor_id);
exception when others then
    dbms_sql.close_cursor(v_cursor_id);
    raise;
END;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: