i wrote code , works ok:
declare cursor c_emp select last_name, first_name employees; type c_list table of employees.last_name%type index binary_integer; type c_list2 table of employees.first_name%type index binary_integer; last_list c_list; first_list c_list2; counter integer := 0; begin in c_emp loop counter := counter + 1; last_list(counter) := i.last_name; first_list(counter) := i.first_name; dbms_output.put_line('employee(' || counter || '): ' || last_list(counter) || ' ' || first_list(counter)); end loop; end; / this time trying make procedure parameters can insert table name , column cursor. , have tried :
create or replace procedure show_data(tab_name in varchar2, data_list in varchar2) str varchar2(100); str2 varchar2(100); column_name varchar2(100); begin str := 'select ' || data_list || ' ' || tab_name; vrec in str loop dbms_output.put_line(str); end loop; end; / it gave error str not cursor. not sure if cursor can done in way, error seems can't.
which part of code wrong, or because didn't declare cursor? if declare cursor, can't parameter using dynamic sql way.
the below should produce same result original pl/sql block. note table_name can dynamic , can data_list need know column names in data_list able fetch cursor , print them.
create or replace procedure show_data(tab_name in varchar2 , data_list in varchar2) str varchar2(100); str2 varchar2(100); column_name varchar2(100); type cursor_ref ref cursor; vrec_cursor cursor_ref; counter integer = 0; last_name employee.last_name%type; first_name employee.first_name%type; begin str := 'select ' || data_list || ' ' || tab_name; open vrec_cursor str; loop fetch vrec_cursor last_name, first_name; exit when vrec_cursor%notfound; dbms_output.put_line('employee(' || counter || '): ' || last_name || ' ' || first_name; counter = counter + 1; end loop; end; / note: haven't run above code yet
Comments
Post a Comment