Hi all,
I want find out unused variables in procedure, function and package.
I have written below script for doing this ,but i am not getting the expected result.
Kindly help me to improve the below code ,so that it works as expected.
{code}
version details
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
"CORE 11.2.0.3.0 Production"
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
{code}
{code}
What i Have tried is This.
DECLARE
V_OBJECT_NAME VARCHAR2(30) :='PR_PRINT';
V_OBJECT_TYPE VARCHAR2(30) :='PROCEDURE';
CURSOR C1(CP_OBJECT_NAME VARCHAR2,CP_OBJECT_TYPE VARCHAR2)
IS
SELECT US.NAME,
US.TYPE,
US.LINE,
REGEXP_SUBSTR(US.TEXT,'.* ') AS var_name
FROM user_source US
WHERE name=CP_OBJECT_NAME
AND type =CP_OBJECT_TYPE
AND REGEXP_LIKE (TEXT,'(v_|g_|c_)','i')
AND REGEXP_LIKE (TEXT,'^[^ ]')
AND REGEXP_LIKE (TEXT,'^[^--;
v_count NUMBER ;
BEGIN
FOR i IN C1(V_OBJECT_NAME,V_OBJECT_TYPE)
LOOP
SELECT COUNT( *)
INTO V_COUNT
FROM USER_SOURCE US
WHERE US.NAME=I.NAME
AND REGEXP_LIKE(US.TEXT,i.var_name,'i' )
AND US.LINE<>I.LINE;
IF V_COUNT =0 THEN
DBMS_OUTPUT.PUT_LINE('variable '||I.VAR_NAME||'Is declared at line#'||I.LINE||' But no where used');
END IF ;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('p_err_code := '||SQLCODE||dbms_utility.format_Error_backtrace());
DBMS_OUTPUT.PUT_LINE('p_err_msg := '||sqlerrm);
END ;
{code}
Thanks,
P Prakash