Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2003
    Posts
    4

    Unanswered: can any help me in writing this procedure

    please assist me in writing the procedure

    declare

    tname varchar2(20);
    ecode number(3);

    begin --b1
    declare cursor c2 is select table_name from all_tables where table_name like 'SDIDTIREJ%';
    begin --b2
    open c2;
    loop
    fetch c2 into tname;
    exit when c2%notfound;
    begin --b3

    declare cursor c1 is select distinct error_code from tname;
    begin --4
    open c1;
    loop

    fetch c1 into ecode;
    exit when c1%notfound;
    dbms_output.put_line('errror code is:'||ecode);
    end loop;
    close c1;
    end loop;

    close c2;
    end;
    /



    here i had to display the distinct error codes in each table

  2. #2
    Join Date
    Sep 2002
    Location
    Austria
    Posts
    37

    try this one ...

    -- create "your tname-table .."
    drop table tname;
    create table tname (
    table_name varchar2(30),
    error_code integer);

    insert into tname values ('STMT_AUDIT_OPTION_MAP',1);
    insert into tname values ('STMT_AUDIT_OPTION_MAP',2);
    insert into tname values ('STMT_AUDIT_OPTION_MAP',3);
    insert into tname values ('AUDIT_ACTIONS',3);
    insert into tname values ('MVIEW$_ADV_WORKLOAD',5);


    create or replace procedure testproc is
    begin
    for tabCur in (select table_name from all_tables where table_name like '%A%') loop
    dbms_output.put_line ('Processing errorcodes for table: ' || tabCur.table_name);
    -- inner loop - here you have to match the table-name
    for errCur in (select error_Code from tname where table_name=tabCur.table_name) loop
    dbms_output.put_line('Error code for ' || tabCur.table_name || ' is: ' || errCur.error_Code);
    end loop;
    end loop;
    end testproc;
    /
    show errors;


    results are as follow:
    SQL> execute testproc;
    Processing errorcodes for table: DUAL
    Processing errorcodes for table: SYSTEM_PRIVILEGE_MAP
    Processing errorcodes for table: TABLE_PRIVILEGE_MAP
    Processing errorcodes for table: STMT_AUDIT_OPTION_MAP
    Error code for STMT_AUDIT_OPTION_MAP is: 1
    Error code for STMT_AUDIT_OPTION_MAP is: 2
    Error code for STMT_AUDIT_OPTION_MAP is: 3
    Processing errorcodes for table: AUDIT_ACTIONS
    Error code for AUDIT_ACTIONS is: 3
    Processing errorcodes for table: MVIEW$_ADV_WORKLOAD
    Error code for MVIEW$_ADV_WORKLOAD is: 5
    ....

  3. #3
    Join Date
    Aug 2003
    Posts
    4

    Re: try this one ...

    Originally posted by pre4711
    -- create "your tname-table .."
    drop table tname;
    create table tname (
    table_name varchar2(30),
    error_code integer);

    insert into tname values ('STMT_AUDIT_OPTION_MAP',1);
    insert into tname values ('STMT_AUDIT_OPTION_MAP',2);
    insert into tname values ('STMT_AUDIT_OPTION_MAP',3);
    insert into tname values ('AUDIT_ACTIONS',3);
    insert into tname values ('MVIEW$_ADV_WORKLOAD',5);


    create or replace procedure testproc is
    begin
    for tabCur in (select table_name from all_tables where table_name like '%A%') loop
    dbms_output.put_line ('Processing errorcodes for table: ' || tabCur.table_name);
    -- inner loop - here you have to match the table-name
    for errCur in (select error_Code from tname where table_name=tabCur.table_name) loop
    dbms_output.put_line('Error code for ' || tabCur.table_name || ' is: ' || errCur.error_Code);
    end loop;
    end loop;
    end testproc;
    /
    show errors;


    results are as follow:
    SQL> execute testproc;
    Processing errorcodes for table: DUAL
    Processing errorcodes for table: SYSTEM_PRIVILEGE_MAP
    Processing errorcodes for table: TABLE_PRIVILEGE_MAP
    Processing errorcodes for table: STMT_AUDIT_OPTION_MAP
    Error code for STMT_AUDIT_OPTION_MAP is: 1
    Error code for STMT_AUDIT_OPTION_MAP is: 2
    Error code for STMT_AUDIT_OPTION_MAP is: 3
    Processing errorcodes for table: AUDIT_ACTIONS
    Error code for AUDIT_ACTIONS is: 3
    Processing errorcodes for table: MVIEW$_ADV_WORKLOAD
    Error code for MVIEW$_ADV_WORKLOAD is: 5
    ....

    hi pre,
    i have tried ur code .the problem is i have a colum called error_code in my table for which using distinct i have to retrieve to get them.
    when i executed the procedure u sent i am getting the following errors.
    please help me in rectifying them

    SQL> create or replace procedure testprocvam is
    2 begin
    3 for tabCur in (select table_name from all_tables where table_name like 'SDIDTIREJ%' ) loop
    4 dbms_output.put_line ('Processing errorcodes for table: ' || tabCur.table_name);
    5 -- inner loop - here you have to match the table-name
    6
    7 for errCur in (select error_Code from tmpvamrejtbl where table_name=tabCur.table_name) loop
    8 dbms_output.put_line('Error code for ' || tabCur.table_name || ' is: ' || errCur.error_Code);
    9 end loop;
    10 end loop;
    11 end testprocvam;
    12 /

    Warning: Procedure created with compilation errors.

    SQL> show errors;
    Errors for PROCEDURE TESTPROCVAM:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    7/16 PL/SQL: SQL Statement ignored
    7/58 PLS-00201: identifier 'TABLE_NAME' must be declared
    8/1 PL/SQL: Statement ignored
    8/75 PLS-00364: loop index variable 'ERRCUR' use is invalid

  4. #4
    Join Date
    Sep 2002
    Location
    Austria
    Posts
    37

    Re: try this one ...

    SQL> create or replace procedure testprocvam is
    2 begin
    3 for tabCur in (select table_name from all_tables where table_name like 'SDIDTIREJ%' ) loop
    4 dbms_output.put_line ('Processing errorcodes for table: ' || tabCur.table_name);
    5 -- inner loop - here you have to match the table-name
    6
    7 for errCur in (select error_Code from tmpvamrejtbl where table_name=tabCur.table_name) loop
    8 dbms_output.put_line('Error code for ' || tabCur.table_name || ' is: ' || errCur.error_Code);
    9 end loop;
    10 end loop;
    11 end testprocvam;
    12 /

    Warning: Procedure created with compilation errors.

    SQL> show errors;
    Errors for PROCEDURE TESTPROCVAM:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    7/16 PL/SQL: SQL Statement ignored
    7/58 PLS-00201: identifier 'TABLE_NAME' must be declared
    8/1 PL/SQL: Statement ignored
    8/75 PLS-00364: loop index variable 'ERRCUR' use is invalid

    hi:

    i am rather sure that there is no column called table_name in your table:
    tmpvamrejtbl - thus you get all the compiler errors:

    do a "desc tmpvamrejtbl " , find out the right name (XXXCOLUMN) and then change the following statement:

    select DISTINCT error_Code from tmpvamrejtbl where XXXCOLUMN=tabCur.table_name

    should work then

  5. #5
    Join Date
    Aug 2003
    Posts
    4
    hi pre,
    i think i am connfusing u as i am new to oracle.
    i have a large database in which there are 100 sdidtirej tables are there.
    so i had to select all those tables as they change some added and some removed.

    in each sdidtirej table there is a column called error_code in which mosly 1 or 2 error codes recorded .so i need to use distinct error code there.


    i hope i have cleared the confusion i have created.


    the result i need is select all sdidtirej tables from a database and extract distinct error_code from each table.finally had to display each table name and error code on same line.

Posting Permissions

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