Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Help Again, Something about Dynamic PL/SQL
Good Morning,
Here is part of a package I am currently writing to automate some daily checks. In it I use Dynamic Sql to return several rows which I put into a holding table using dbms_sql.define_array and dbms_sql.column_value. I then compare those values against a similar column in another table, this time using dbms_sql.define_column and dbms_sql.column_value. I have tested this procedure and it does work. One cautionary (sp?) note, I am new to dynamic sql, and I have an error somewhere in my package that causes a memory leak. I know it is in the dynamic sql code, but I don't yet know which particular procedure it is in. Hope it helps.
Joe Long
file_handle UTL_FILE.FILE_TYPE; file_name varchar2(40); c_sel_tmp_cnt integer; c_sel_comm_cnt integer; select_rows number; fetch_rows integer(1); fetch_count integer; total_count integer; temp_tbl_name varchar2(30); comm_tbl_name varchar2(30); rept_flag_tab dbms_sql.Date_Table; rept_count_tab dbms_sql.Number_table; indx number := 1; comm_rept_count number; pass_rept_flag date;
BEGIN
temp_tbl_name := temp_tab_name; comm_tbl_name := comm_tab_name; file_name := trace_file;
c_sel_tmp_cnt := dbms_sql.open_cursor; dbms_sql.parse(c_sel_tmp_cnt, 'SELECT distinct trunc(rept_flag), count(*) ' || 'FROM ' || temp_tbl_name || ' GROUP by trunc(rept_flag)',dbms_sql.native);
dbms_sql.define_array(c_sel_tmp_cnt, 1, rept_flag_tab, 10, indx); dbms_sql.define_array(c_sel_tmp_cnt, 2, rept_count_tab, 10, indx); select_rows := dbms_sql.execute(c_sel_tmp_cnt); loop fetch_count := dbms_sql.fetch_rows(c_sel_tmp_cnt); total_count := dbms_sql.last_row_count; dbms_sql.column_value(c_sel_tmp_cnt, 1, rept_flag_tab); dbms_sql.column_value(c_sel_tmp_cnt, 2, rept_count_tab); exit when fetch_count != 10; end loop;
c_sel_comm_cnt := dbms_sql.open_cursor;
FOR i in 1..total_count loop comm_rept_count := null; pass_rept_flag := null; dbms_sql.parse(c_sel_comm_cnt, 'SELECT count(*) ' || 'FROM ' || comm_tbl_name || ' WHERE trunc(rept_flag) =trunc(:rept_date)', dbms_sql.native);
dbms_sql.bind_variable(c_sel_comm_cnt, ':rept_date', rept_flag_tab(i));
dbms_sql.define_column(c_sel_comm_cnt, 1, comm_rept_count);
select_rows := dbms_sql.execute(c_sel_comm_cnt);
fetch_rows := dbms_sql.fetch_rows(c_sel_comm_cnt);
dbms_sql.column_value(c_sel_comm_cnt, 1, comm_rept_count);
IF comm_rept_count = rept_count_tab(i) THEN NULL; ELSE file_handle := utl_file.fopen('D:\oracle\trace_file', file_name, 'A'); utl_file.put_line(file_handle, 'Records didn''t transfer to ' || comm_tbl_name || ' for ' || to_char(rept_flag_tab(i), 'DD-MON-YY')); utl_file.fclose(file_handle); pass_rept_flag := rept_flag_tab(i); tsfr_comm_prob_recs(temp_tbl_name, comm_tbl_name,pass_rept_flag);
END IF; END loop;
dbms_sql.close_cursor(c_sel_comm_cnt); dbms_sql.close_cursor(c_sel_tmp_cnt);
END chk_tsfrd_recs;
lkw wrote:
> > hi everybody, > > at first i would like to say thanks to M.Armaghan and Bob Bain for telling > me that there is a way to construct dynamic SQL statements in stored proc. > > i have tried them and found them useful. But right now i faced another > problem which is that i need to return a cursor from the > DBMS_SQL.COLUMN_VALUE or DBMS_SQL.VARIALBLE_VALUE. i always get compilation > errors like > > *PLS-00306: wrong number or types of arguments in call to 'COLUMN_VALUE'* > or > *PLS-00306: wrong number or types of arguments in call to 'VARIABLE_VALUE'* > > So i guess that izzit DBMS_SQL package doesn't support cursor variable? if > yes, how shall i declare them in the first place? > > Any helps would be greatly appreciated! thanks! > > lkw.Received on Fri Dec 17 1999 - 09:04:19 CST