function dynamic Fetch in Oracle

In coding, we often meet some SQL queries like ' Select many Cloumns from TableName where condition; ‘. When the number of cloumns are dynamic, we should use ResultSet to achieve in Java. As we all know, the class ResultSet have been defined in the Java class library. We can use it to get the result of a query with optional cloumns directly. However, we now in Oracle/plsql, and if we are want to get the same effect as Java, we should get one better the "to" solve that . Think about using the cursor to achieve you want. Actually, you'll realized that's not exactly what do you think. There is one solution for what we said, the following function makes full use of the cursor to get what we want.

Create or Replace function Func_dynamic_fetch (p_sql in varchar2, P_no_of_cols Integer, P_return_multiple_row    Boolean: = True) return anydatalist is Lc_rs sys_refcursor;    Strlist varchar2list: = Varchar2list ();    N Number: = 3;    Code VARCHAR2 (32767);    TYPE ref_cursor is REF CURSOR;      RC_ Ref_cursor;    C_ number;    I_ number;    Col_count_ number; Desc_tab_ Dbms_sql.    DESC_TAB2;    Col_num number;    N2 number;    L_any_list anydatalist;     L_types varchar2list;    L_add_stmts varchar2list;    L_fetch_type varchar2 (32767);    C_DATE_TYPE constant Number: = 12;    C_TIMESTAMP_TYPE constant Number: = 180; function repeatstmt (n in integer, template in Varchar2, seperator in varchar2: = ", vals in varchar2list: = null) return        VARCHAR2 is L_result varchar2 (32767);    L_stmt varchar2 (32767);            Begin for I in 1..N loop l_stmt: = template; If Vals is isn't null then l_stmt: = RepLace (template, ' __val__ ', Vals (i));            End If; L_result: = L_result | |             Replace (l_stmt, ' __$__ ', i); If I < n then l_result: = L_result | |            Seperator;        End If;        End Loop;    return l_result;      End Repeatstmt;begin open rc_ for P_sql;    N2: = P_no_of_cols;    C_: = Dbms_sql.to_cursor_number (RC_); Dbms_sql.    Describe_columns2 (C_, Col_count_, desc_tab_);    Col_num: = Desc_tab_.first;    L_types: = Varchar2list ();    L_add_stmts: = Varchar2list (); If Col_num is not NULL then loop if Desc_tab_ (col_num). Col_type = C_date_type or Desc_tab_ (col_num). Col_                Type =c_timestamp_type then Varchar2listadd (l_types, ' Date ');  --to conform to JDBC date format varchar2listadd (L_add_stmts, ' Varchar2listadd (L_str_list, To_char (a__$__,            ' Yyyy-mm-dd hh:mi:ss ');                else Varchar2listadd (L_types, ' varchar2 (32767) '); Varchar2liStadd (L_add_stmts, ' Varchar2listadd (L_str_list, a__$__);            End If;            col_num:= Desc_tab_.next (Col_num);        Exit when (Col_num is null);    End Loop;    End If;    --dbms_output.put_line (' Cursor count: ' | | col_count_);     N: = Col_count_;     If n2 > N then n2: = N;    End If;    --dbms_output.put_line (' Number of columns: ' | | n);    --dbms_output.put_line (' Number of columns to fetch: ' | | n2);    Dbms_sql.close_cursor (C_);    --if Rc_%isopen then--close rc_;    --end if;        Code: = ' Declare lc_rs sys_refcursor;        L_sql varchar2 (32767);         L_str_list varchar2list;        L_counter integer; L_any_list anydatalist;          ' | | REPEATSTMT (n, ' a__$__ __val__; ', seperator = Chr (ten), Vals = l_types) | |        ' Begin L_sql: =: 0;        --dbms_output.put_line (L_sql);        Open lc_rs for L_sql;        L_any_list: = Anydatalist ();         L_counter: = 0; Loop fetch LC_RS inTo ' | | REPEATSTMT (n, ' a__$__ ', ', ') | | '; ' | |            ' Exit when Lc_rs%notfound;             L_counter: = L_counter + 1;             L_str_list: = Varchar2list (); ' | | --repeatstmt (N2, ' Varchar2listadd (L_str_list, a__$__); ', Chr (ten), Vals = l_types) | | ' Repeatstmt (N2, ' __val__ ', Chr (ten), Vals = L_add_stmts) | | ' Anydatalistadd (L_any_list, Anydata.convertcollection (l_str_list));             ' | |            (case is P_return_multiple_row = False Then ' exit when l_counter = 1; ' End) | |        ' End loop;        If Lc_rs%isopen then close lc_rs;        End If;      : 1: = L_any_list;        Exception when others then Dbms_output.put_line (' Func_dynamic_fetch exec immediate exception: ' | | sqlerrm);        Raise If Lc_rs%isopen then close lc_rs;      End If;    End     ‘ ;    --dbms_output.put_line (code);    Execute immediate code using P_SQL, out l_any_list; Return l_any_list;exception when others tHen if Rc_%isopen then close rc_;     End If;    Dbms_output.put_line (' func_dynamic_fetch:exception: ' | | sqlerrm); return anydatalist (); end; /show errors;

function dynamic Fetch in Oracle