Re: Help Again, Something about Dynamic PL/SQL

From: Joe Long <joe.long.nospam_at_cticallcenter.com>
Date: Fri, 17 Dec 1999 09:04:19 -0600
Message-ID: <385A50F3.760FE67C@cticallcenter.com>

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

---

---

  PROCEDURE chk_tsfrd_recs(temp_tab_name IN varchar2, comm_tab_name IN varchar2, trace_file IN varchar2) IS

     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;

• Open cursor to get the number of records for each
• distinct rept_flag in the comm temp table

     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;
 

• Open cursor to get the number of records for the
• corresponding rept_flag date in the actual comm
• table. Loops thru all of the dates

     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