create or replace package cognos_pk as /* Creates Package Header*/
TYPE project_type IS record( /* A record declaration is used to */
c1 NUMBER /* provide a definition of a record */
); /* that can be used by other variables*/
TYPE project_type1 IS REF CURSOR return project_type; /* Variable declaration */
procedure conosg_sp (result1 out project_type1); /* SP declaration */
end;
/
CREATE OR REPLACE PACKAGE BODY cognos_pk AS /* Name of package body must be same as header */
PROCEDURE conosg_sp(result1 OUT project_type1) IS
countrow number;
BEGIN
FOR X IN (SELECT TABLE_NAME
FROM USER_TAB_COLUMNS
WHERE COLUMN_NAME='PROC_STAT_CODE' AND TABLE_NAME LIKE 'INPT%')
LOOP
execute immediate 'select count(*) from '||X.TABLE_NAME ||' WHERE PROC_STAT_CODE <>10 ' into countrow;
--result1 := X.TABLE_NAME|| ROW_CNT;
-- dbms_output.put_line(result1 );
OPEN result1 for countrow;
END loop;
END;
end;
/
This is my requirement ...
I want to count the table starting with Inpt and and proc stat _code =10 which is the column name in all the table.
i wan to return the count and the table name to be used in my cognos report.
Edited by: BluShadow on 10-May-2013 09:22
added {noformat}
{noformat} tags around the code/data for readability (no accounting for OP's lack of formatting)