TABLE FINDER

Table_Finder is a procedure to find all the tables that are queried in a particular package or procedure. It takes Package name or procedure name as input parameter. Final output will be the list of table names that are queried by that package along with the table?s owner name. It finds the table names involved in all schemas. This developed procedure will be useful to all development and enhancement projects as it is gives output without consuming time.

CREATE OR REPLACE PROCEDURE table_finder(pkg_name IN VARCHAR) AS
CURSOR Test IS
 SELECT owner,referenced_name,object_type FROM user_dependencies ud,all_objects ao,user_synonyms us
  WHERE ud.name=pkg_name
  AND ud.referenced_type IN ( 'TABLE','SYNONYM')
  AND ud.referenced_owner NOT IN ('SYS','PUBLIC')
  AND ud.referenced_name=ao.object_name
  AND ud.referenced_name=us.synonym_name(+)
  AND ao.object_type LIKE'TABLE';
   
     BEGIN
           for rec in test
               loop
                      dbms_output.put_line(rec.owner);
                      dbms_output.put_line(rec.referenced_name);
                      dbms_output.put_line(rec.object_type);
               end loop;
      END;

[ Read More ]

Oracle PL/SQL Query for Pagination

Oracle PL/SQL function makes the input query statement such a way that the query out put returns for a start row index to end row index.

CREATE OR REPLACE FUNCTION pagination_stmt_fn 
 (
  i_query_statement   IN   VARCHAR2,
  i_start_index       IN   NUMBER,
  i_end_index         IN   NUMBER
 )
 RETURN VARCHAR2
 IS
 o_query_statement   VARCHAR2 (32000);
 v_start_index       NUMBER           := 0;
 v_end_index         NUMBER           := 0;
BEGIN
 IF i_start_index = 0
 THEN
  v_start_index := 1;
 ELSE
  v_start_index := i_start_index;
 END IF;

 IF i_end_index = 0
 THEN
  v_end_index := 10000000;
 ELSE
  v_end_index := i_end_index;
 END IF;

 o_query_statement :=
  ' select  * from (  '
    || '  select result.*, rownum row_num  from ( '
    || i_query_statement
    || ' ) result ) '
  || ' where row_num between '
  || TO_CHAR (v_start_index)
  || ' and '
  || TO_CHAR (v_end_index);

RETURN o_query_statement;

EXCEPTION
 WHEN OTHERS
 THEN
 RETURN NULL;
END pagination_stmt_fn;
/

/************************************************************************************************

--In put parameters
-- i_query_statement:  "SELELCT * FROM EMP ORDER BY EMPLOYEE_ID"   
   (assumption:   Table EMP is exist and contains some rows)

-- i_start_index: 10
--i_end_index: 25


select pagination_stmt_fn( 'SELECT * FROM EMP ORDER BY EMPLOYEE_ID',10,25) "pagination statement" from dual;


--out put on SQL* Plus window

pagination statement
--------------------------------------------------------------------------------
 select  * from (    select result.*, rownum row_num  from ( SELECT * FROM EMP 
ORDER BY EMPLOYEE_ID ) result )  where row_num between 10 and 25


*************************************************************************************************/

[ Read More ]