problem with pl/sql tables
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: problem with pl/sql tables

  1. #1
    Join Date
    May 2004
    Posts
    29

    problem with pl/sql tables

    hi,
    i am trying to insert a value in a pl/sql table inside a cursor. the code is as below:

    create or replace procedure err_logging(p_owner varchar2)
    --declare
    --p_owner varchar2(20):= 'DRA';
    is

    TYPE rec_diff_cols IS table of varchar2(20)
    index by binary_integer;
    rec_cols rec_diff_cols;
    j number := 1;
    .
    .
    .
    begin
    ..........
    ........
    for i in cur_diff_cols_tab_mview(c_rec.dra_mview_name,c_rec.src_tblname)
    loop
    l_cols := i.column_name;
    rec_cols(j) := l_cols;
    --fetch cur_diff_cols_mview_tab into rec_cols;
    j:=j+1;
    end loop;

    here when i am debugging the code i am getting this in TOAD...

    undeclared identifier rec_cols..and no value is going into that I guess.
    KIndly help ASAP.

    regards.

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    If you've "index by binary_integer" you'd better declare j as binary_integer!

  3. #3
    Join Date
    May 2004
    Posts
    29
    thanks, but it doesn't work.

    this is the entire code, if possible just have a look.


    create or replace procedure err_logging(p_owner varchar2)
    --declare
    --p_owner varchar2(20):= 'DRA';
    is

    TYPE rec_diff_cols IS table of varchar2(20)
    index by binary_integer;
    rec_cols rec_diff_cols;

    cursor cur_sel_tbl_mviews is select owner,src_tblname,dra_mview_name from resync_src_master where owner = p_owner;

    cursor cur_diff_cols_tab_mview(l_mview_name varchar2,l_tbl_name varchar2) is
    select column_name from dba_tab_cols where owner = p_owner and table_name = l_tbl_name
    minus
    select column_name from dba_tab_cols where owner = p_owner and table_name = l_mview_name;

    cursor cur_diff_cols_mview_tab(l_mview_name varchar2,l_tbl_name varchar2) is
    select column_name from dba_tab_cols where owner = p_owner and table_name = l_mview_name
    minus
    select column_name from dba_tab_cols where owner = p_owner and table_name = l_tbl_name;



    l_mview_cnt_col_name number(3);
    l_tbl_cnt_col_name number(3);
    j binary_integer := 0;
    l_cols varchar2(20);

    begin

    for c_rec in cur_sel_tbl_mviews
    loop
    select count(column_name) into l_mview_cnt_col_name from dba_tab_cols where owner = p_owner and table_name = c_rec.dra_mview_name;
    select count(column_name) into l_tbl_cnt_col_name from dba_tab_cols where owner = p_owner and table_name = c_rec.src_tblname;
    IF l_mview_cnt_col_name > l_tbl_cnt_col_name THEN

    for i in cur_diff_cols_mview_tab(c_rec.dra_mview_name,c_rec.src_tblname)
    loop
    l_cols := i.column_name;
    rec_cols(j) := l_cols;
    --fetch cur_diff_cols_mview_tab into rec_cols;
    j:=j+1;
    end loop;
    forall j in rec_cols.first..rec_cols.last
    insert into uiiadmin values (p_owner, c_rec.src_tblname,rec_cols(j),'DELETED', 'Replication must be done');
    raise_application_error(-20101, '''Columns deleted from '||p_owner||'.'||c_rec.src_tblname||', check UIIADMIN table for details''');

    ELSIF l_mview_cnt_col_name < l_tbl_cnt_col_name THEN

    for i in cur_diff_cols_tab_mview(c_rec.dra_mview_name,c_rec.src_tblname)
    loop
    l_cols := i.column_name;
    rec_cols(j) := l_cols;
    --fetch cur_diff_cols_mview_tab into rec_cols;
    j:=j+1;
    end loop;
    forall j in rec_cols.first..rec_cols.last
    insert into uiiadmin values (p_owner, c_rec.src_tblname,rec_cols(j),'INSERTED', 'Replication may be needed');
    raise_application_error(-20101, '''Columns inserted into '||p_owner||'.'||c_rec.src_tblname||', check UIIADMIN table for details''');

    END IF;
    end loop;
    end;

  4. #4
    Join Date
    May 2004
    Posts
    29

    Thumbs up

    thanks a lot, got the solution quite some time back, but didn't find time to shoot the mail. the thing was working fine, only i cudn't see it in the table after the insert as i hadn't committed the transaction.

    thanks and regards.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width