Sorting with select * from

Recently I had a question from someone who was using a local function which took a delimited string as input and split it into values in a table that was defined as a type like

TYPE V_STRING_T IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER

The function used the PIPELINED attribute so the function could be used in an SQL select statement. The function declaration looked like

FUNCTION STR_TO_TAB_PIPELINED(

                        p_string        IN VARCHAR2,

                        p_delimiter     IN VARCHAR2)

RETURN string_array_t PIPELINED;

So an example SQL showing how it might be used is.

SELECT  * 

FROM TABLE(str_to_tab_pipelined('cat,dog,horse',','))

which was all good until there was requirement to have a set of numbers passed in and then sort them as if they were numbers not strings. So basically to add an 'order by to_number'

We can't use a table alias and you can't do something like

SELECT  * 

FROM TABLE(str_to_tab_pipelined('1,5,8,11,4,67,3',','))

ORDER BY to_number(1)

Looking at this I noticed some IDE tools called the column in the query 'column_value'. So I took a chance and tried

SELECT  * 

FROM TABLE(str_to_tab_pipelined('1,5,8,11,4,67,3',','))

ORDER BY to_number(column_value)

And it worked. I did some research and saw the pseudo column 'column_value' is used in XML processing but didn't find mention of using it in this sort of context. 

More standards checking with PL/Scope

After the earlier post about PL/Scope another use has come to mind with regard to standards checking.


A standard coding principle is to to avoid hard coding in code. Hard coding can take many forms but the one we are looking at here is the case where we define parameters or variables in PL/SQL using a static definition rather than using typing to a database columns.


For example we may have
v_surname VARCHAR2(40);  -- hard code length
instead of
v_surname person.surname%TYPE -- takes definition from the surname column in the person table


By using the type of the column we automatically pick up changes that happen to the table definition without the need to alter our code.


So where does PL/Scope help with this? Well you can pretty easily check all your code (that has been compiled with PL/Scope turned on) to find examples of where variables have been declared using a standard datatype rather than referencing a database column.


So here is a small function as an example.

FUNCTION f_agg_cost (p_event_no NUMBER)
RETURN NUMBER
AS
v_agg_cost   NUMBER;
BEGIN
SELECT SUM (bk.cost)
INTO v_agg_cost
FROM bookings bk
WHERE bk.event_no = p_event_no;


RETURN (v_agg_cost);
END;

If we compile using PL/Scope we can then run this query to find where straight datatypes have been used

SELECT ai2.line,
ai2.NAME,
ai2.TYPE usage,
ai.NAME datatype
FROM all_identifiers ai
INNER JOIN
all_identifiers ai2
USING
(owner, object_name, object_type)
where owner = 'SCHEMA_OWNER' AND
object_name = 'F_AGG_COST' AND
ai.TYPE LIKE '%DATATYPE' AND
ai.usage_context_id != 0 AND
ai2.usage_id = ai.usage_context_id AND
ai2.TYPE IN ('FORMAL IN', 'FORMAL OUT', 'VARIABLE')
ORDER BY ai.line

Which results in two records
LINE NAME       USAGE     DATATYPE
---- ---------- --------- --------
1   P_EVENT_NO FORMAL IN NUMBER
4   V_AGG_COST VARIABLE NUMBER

So we can see that we get entries corresponding to the declarations for the parameter and the variable. Of course this would be easy to pick up just by looking at it but in a 10,000 line package being able to do it via a query makes finding these much easier.

So if we change the function to be this
FUNCTION f_agg_cost (p_event_no bookings.event_no%TYPE)
RETURN NUMBER
AS
v_agg_cost   bookings.cost%TYPE;
BEGIN
SELECT SUM(bk.cost)
INTO v_agg_cost
FROM bookings bk
WHERE bk.event_no = p_event_no;


RETURN (v_agg_cost);
END;

And then run the query again we get no records returned.

Of course it does not always make sense to declare parameters and variables against database columns as there may not be a related column that can provide the typing details. But having this option to quickly check lots of code can help with code reviews and implementing standards.

Deleting all the child records of the current record

Sometimes we want to delete a record from a table and we know, or suspect, that there are records in child tables that refer to the current record via a foreign key.
The script below will look at all foreign keys that refer to the primary key (called PT_PK in the script) of the desired parent table (called PARENT_TABLE in the script). It then gets the table name and key column name of the child table and builds a SQL statement to delete the records from the child table that match the key value (Called 'VALUE' in the example below). The SQL is then executed using dynamic SQL. 
Finally it deletes the parent record and commits.


This could also be adjusted to use a unique key instead of the primary key if that was used for foreign keys instead.


For testing, replace the execute statement with 
dbms_output.put_line(v_sql); 
to view the SQL that will be executed and comment out the final delete and commit until you are happy with the SQL it will run.

Note that this example is for a single column key only and only goes one level down. You could build a recursive function that repeatedly tracks down all child tables using the same method or extend it to allow for multi-column keys.


Note also that the '''VALUE''' example used below assumes the column is a literal string and hence needs the triple quotes so it can be inserted as such in the SQL statement. If you need a number then then it would be specified as 'VALUE'. E.g. '9'. Or use other data types as required.

DECLARE
  CURSOR  c_fk IS
  SELECT  constraint_name,
          table_name
  FROM    all_constraints
  WHERE   constraint_type = 'R' AND
          delete_rule     = 'NO ACTION' AND
          r_constraint_name IN  ( 
            SELECT  constraint_name
            FROM    all_constraints
            WHERE   constraint_type  = 'P' AND -- primary
                    table_name       = 'PARENT_TABLE' AND
                    owner            = 'SCHEMA_OWNER') AND
          table_name != 'PARENT_TABLE';
  CURSOR c_child_col (cp_child_cons_name IN      
                          all_cons_columns.constraint_name%TYPE,
                          cp_child_tab_name  IN         
                          all_cons_columns.table_name%TYPE) IS
  SELECT  cc1.column_name      AS child_col,
          cc1.table_name       AS child_table
  FROM    all_cons_columns     cc1,
          all_cons_columns     cc2
  WHERE   cc1.constraint_name  = cp_child_cons_name AND                          
          cc1.table_name       = cp_child_tab_name AND                           
          cc2.constraint_name  = 'PT_PK' AND --parent primary key
          cc1.position         = cc2.position
  ORDER BY cc1.position;
  v_sql   VARCHAR2(1000);
BEGIN
  FOR v_fk_rec IN c_fk LOOP
    FOR v_child_col_rec IN c_child_col(
                               v_fk_rec.constraint_name,
                               v_fk_rec.table_name) LOOP
      v_sql := 'DELETE FROM '||v_child_col_rec.child_table||
                            ' WHERE '||
                            v_child_col_rec.child_col ||
                            ' = ' || 
                            '''VALUE'''; -- the primary key value


      EXECUTE IMMEDIATE v_sql;
    END LOOP;                    
  END LOOP;
  DELETE FROM PARENT_TABLE WHERE PARENT_KEY = 'VALUE';
  COMMIT;
END;

New feature in 11gR2 - listagg

Although this is more straight SQL than PL/SQL it is something I dicovered recently and found interesting. With Release 2 of the 11g Oracle database they have added, amoungst other things, a new built in function to created a concatenated list of values from a query. The new function is listagg. So you can create output of the form.


Dept Code Employees ID's
10  213, 214, 216, 218
20  220, 230, 298
30  450, 451, 850, 021, 823


Which you need to do occasionally. I have seen a requirement like this to create a comma separated list of email addresses to create an email from a table.
For example if you wanted code to create a mailing list from the employee ID's above based on department so that you could an email per department.


Dept  Employees email
10  max@company.com, bob@company.com, jen@company.com
20    cat@company.com, tom@company.com


The way this could be done was to loop through a cursor and while the value of department code was the same value as the one for the previous loop keep adding to the string of addresses. When the department value changed then complete the previous string and start the next string for the new department code. Which is easy to do with PL/SQL.

To do this with listagg it would look like this


SELECT emp.dept_id,
listagg(emp.email_addr, ', ') -- the column used in the
concatenated string and the delimiter
  within GROUP
  (ORDER BY emp.emp_id asc)  -- the order of values within the
concatenated string
FROM employee emp
WHERE emp.email_addr IS NOT NULL
GROUP BY emp.dept_id
ORDER BY emp.dept_id



If you omit the delimiter argument

listagg(emp.email_addr)

the default is a straight concatenation with no delimiter at all like this


mary@company.combob@company.comalice@company.commike@company.com

So if you want to have a space instead you need to say


listagg(emp.email_addr, ' ')

which would give you


mary@company.com bob@company.com alice@company.com mike@company.com

Of course there are limitations to how big this resultant string can be. In this case it is the VARCHAR2(4000) limit. If you exceed 4000 characters you will get the error


ORA-01489: result of string concatenation is too long

To get around this error you need to take a different approach. This isn't as convenient as using listagg and will result in a CLOB but it will work with longer data. Below is an example using xmlagg where it concatenates all employee last_name records for each department_id and orders within the concatenated records by the last_name.


SELECT emp.department_id,
rtrim(
         xmlserialize(content
           extract(
             xmlagg(xmlelement("big_string", 
                               emp.last_name||',') 
                               ORDER BY emp.last_name)
           , '//text()'
           )
         )
       , ','
       ) as list
FROM employees emp
GROUP BY emp.department_id

I've also got another post using xmlagg at multiple levels if that is needed. The same approach could be applied here if needed.

If the approach above does not do what you need you can also use an example that was shown on the Ask Tom site.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:2196162600402

If you know another solution then leave a comment.

Using CONTINUE in loops, new feature 11g

In ancient history I did a lot of programming in C (Cbreeze to be exact) which had embedded SQL inside C code. As with any database processing you tend to do a lot of processing using loops. One of the most used features in C was the continue statement. Which basically means stop processing this iteration of the loop and go to the next record. Which is very useful.

So when I became an Oracle PL/SQL programmer back with release 7 of the database I was a bit upset that there was no comparable continue statement.

Well eventually Oracle caught up and in release 11g we can now use continue and it even goes one step further than what C could do.

Below is a basic example of looping logic you might use without continue. I guess what you might do in a pre 11g database.
Set a variable if a condition is met and use that so that you don't execute the rest of the logic in a loop statement.

DECLARE
  v_skip_rest_of_loop BOOLEAN;
BEGIN
  FOR i IN 1 .. 10 LOOP
  v_skip_rest_of_loop := FALSE;
  IF mod(i, 3) = 0 THEN
  v_skip_rest_of_loop := TRUE;
    END IF;
  IF NOT v_skip_rest_of_loop THEN
  -- lots of other processing here we don't 
      -- want to do for this loop when mod 3 = 0
  dbms_output.put_line('On line:'||to_char(i));
  END IF;
  END LOOP;
END;

But with CONTINUE available you can simplify the logic as below. No need to set a variable, just say start the next record

BEGIN
  FOR i IN 1 .. 10 LOOP
IF MOD(i, 3) = 0 THEN
CONTINUE; -- start the next iteration of the loop
END IF;

  -- lots of other processing here we don't 

    -- want to do for this loop when mod 3 = 0

dbms_output.put_line('On line:'||to_char(i));
END LOOP;
END;

But Oracle has made it even more concise in 11g. You can specify the condition logic as part of the continue definition using CONTINUE WHEN so it includes the logic from the IF statement above.

BEGIN
FOR i IN 1 .. 10 LOOP
CONTINUE WHEN MOD(i, 3) = 0;

  -- lots of other processing here we don't 

    -- want to do for this loop when mod 3 = 0

dbms_output.put_line('On line:'||to_char(i));
END LOOP;
END;

Converting weeks, months and years

Recently there was a need to convert start and end dates to differing units of weeks, months and years in PL/SQL.
Although Oracle provides many date manipulation tools there are limited built in functions to do this sort of conversion. So here is a look at some approaches to do this sort of work.

For many of these examples the requirement was to return value as whole numbers and round up to the next highest value. I.e. If something was 40 days that means it cross 2 months. This is done using CEIL. If you want to round to the nearest value use ROUND or to use the lower value use TRUNC.

Using a start and end date

For weeks you use:

SELECT CEIL((end_date - start_dt)/7) FROM dual

So if end_date - start_dt = 23, then 23 / 7 = 3.285.., CEIL(3.285..) = 4
23 days gives us 4 weeks which is what we want.

For months you use:


SELECT CEIL(months_between(end_date, start_dt)) FROM DUAL

So if end_date is 31/12/2011 and start_dt is 17/02/2011 then months_between gives 10.451.., CEIL(10.451..) = 11 months.
Looking at the start and end date in this example we can see that it covers 11 whole months so this makes sense.

For years it is the same sort of thing but we just need to divide by 12


SELECT CEIL(months_between(end_date, start_dt)/12) FROM DUAL

End date is 31/12/2014, start_dt is 17/02/2011, month_between = 46.451..., divide by 12 = 3.870.., CEIL(3,870..) = 4
Again based on the start and end date 4 years is what we expect.

When converting units without specific dates

But what if I want to convert straight from 32 weeks to a number of months?

We know that a week is 7 days so we can just use an end date of sysdate and start date of sysdate - (32 * 7).
SELECT CEIL(months_between(sysdate , sysdate - (32 * 7))) FROM DUAL


Convert months to years or years to months is simply CEIL(number_of_months/12) and CEIL(number_of_years * 12)

Convert months to days.
This is never going to be 100% accurate if it is a generic calculation. We know some months have 30 days, some 31 and of course February can have 28 or 29. So it really depends what specific months you want to convert. If we don't have specific months then you can get close to an accurate figure using.

SELECT SYSDATE - ADD_MONTHS(SYSDATE, -number_of_months) FROM dual


So for 7 months
SELECT SYSDATE - ADD_MONTHS(SYSDATE, -7)FROM dual
gives a result of 212 days

Convert years to days.
This also present problems because a leap year has an extra day. So again it depends which set of years you want to convert. For financial processing the number of days in a year is usually set as 365.25 so the extra day is distributed across the four years.

Another way to do it without using the 365.25 is use the add_month built_in as well.

SELECT SYSDATE - ADD_MONTHS(SYSDATE, (-12 * number_of_years)) FROM dual


which for 4 years = 1461 and 3 years = 1095 when run right now. If you want to make sure it always gets the same answer then work from a standard date instead of sysdate. E.g. set the date to to_date('01/01/2000') instead of sysdate.

There may be a more accurate way of doing these calculations but I have not come across it yet.

'INSERT RETURNING INTO' can clean up old coding methods

Just remembered in Oracle 11g there are two lots of  functionality that can help with existing code like this


SELECT ex_sequence_number.NEXTVAL
INTO v_ex_sequence_number
FROM dual;
p_batch_number := v_ex_sequence_number; -- out parameter


INSERT INTO upload_validated (
sequence_number,
widget_cd,
comments)
VALUES (
v_ex_sequence_number,
p_widget_cd,
'This is the old way to do it');


First we don't need to do the select any more as we can just say


INSERT INTO upload_validated (
sequence_number,
widget_cd,
comments)
VALUES (
ex_sequence_number.NEXTVAL,-- no need to do the old way
p_widget_cd,
'This is the old way to do it');

But because we also need to set the out parameter (p_batch_number using the same sequence value) we also need to use the returning into clause for the insert.

INSERT INTO upload_validated (

sequence_number,

widget_cd,

comments)

VALUES (

ex_sequence_number.NEXTVAL,-- no need to do the old way

p_widget_cd,

'This is the old way to do it')

RETURNING sequence_number

INTO p_batch_number

Which is a much nicer and concise way to do it.

Compile time warnings

There isn't really a Lint like tool for PL/SQL. The closest you can get is enabling compile time warnings.

In the session where you will do the compile issue the following
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';

And to switch it back off
ALTER SESSION SET PLSQL_WARNINGS='DISABLE:ALL';

If use the test function below as an example we can see a bunch of things that look wrong.

FUNCTION test_func
  ( p_emp_id IN emp.emp_id%TYPE)
RETURN varchar2 IS
CURSOR c_emp IS
SELECT surname
FROM emp
WHERE emp_id = p_emp_id;
v_surname emp.surname%TYPE;
v_not_used NUMBER;
BEGIN 
IF 1 = 2 THEN
OPEN c_emp;
FETCH c_emp INTO v_surname;
RETURN v_surname;
ELSE
dbms_output.put_line('This will never appear');
END IF;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;

This is the feedback we get from the compile time warnings

[1:1] PLW-05018: unit TEST_FUNC omitted optional AUTHID clause; default value DEFINER used
[4:2] PLW-06006: uncalled procedure "C_EMP" is removed.
[12:3] PLW-06002: Unreachable code
[1:1] PLW-05005: subprogram TEST_FUNC returns without value at line 21


We get 4 things returned from the compile time warnings. 
line 1 - We didn't define AUTHID and Oracle thinks it is good practice to always explicitly define it. By default, if it is not set explicitly, it will be set to DEFINER rights. If you want to remove this warning then either switch the warning off or define the AUTHID in the code.
line 4 - the cursor c_emp is never used so it is removed
line 12 - there is code that is never executed inside the if 1 = 2
line 21 - no return value and it is a function. The line number can't really make sense for this one so it uses the last line.


These are all good things to know but there seems to be a few other things it would be nice to be told too.
If we use a simpler function then these issues stand out a bit more

FUNCTION test_func2
  ( p_emp_id IN emp.emp_id%TYPE)
RETURN varchar2 IS
CURSOR c_emp IS
SELECT surname
FROM emp
WHERE emp_id = p_emp_id;
v_surname emp.surname%TYPE;
v_not_used NUMBER;
BEGIN
OPEN c_emp;
FETCH c_emp INTO v_surname;
RETURN v_surname;
dbms_output.put_line('Never to be seen');
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;


Gives us these warnings


[1:1] PLW-05018: unit TEST_FUNC omitted optional AUTHID clause; default value DEFINER used
[14:2] PLW-06002: Unreachable code

So we again see unreachable code as expected for the dbms_output line that will never be hit, as it is after a return, but it would also be nice to know.

1. The variable v_not_used is declared but never used
2. The cursor c_emp is opened but never closed

Number 1 is not a big deal but a Lint checker would pick this up. There are other ways to do this using PL/SCOPE

Number 2 would be nice to know as leaving cursors open can cause lots of issues like exceeding max cursors.

It would be super nice if it told you also that the c_emp should be closed in the exception section so that it is caught if something goes wrong. I've been working on my own PL/SQL code to try and pick this up in source code checks but that I will leave that to another post when it gets fleshed out a bit more.

Foreign key indexes come back to bite in Oracle 11g

When I started out doing database design, in Oracle 7, I was told that a foreign key always needed an associated index to help with locking issues. I never really questioned it as I was the new guy and did what I was told by the experienced people. Ever since it has been the standard to do this and things were going along quite well until we got to 11g.
While I was still doing database design and including FK indexes as I went (most of the time) other people doing design were a little less careful and some foreign keys were being created without indexes or were created with indexes that had their columns in a different order to the FK (which means the index is not used).
Oracle 11g seems to be a bit more rigid than previous versions and soon we had production locking problems that were locking up parts of the system in a nasty way.

So I had to find all the foreign key indexes that have columns in a different sequence to the columns in the actual foreign key. Fortunately the naming standards mean that all the foreign keys have the name in the format that ends with FK and all the related indexes have the same name but with '_I' appended to the end.
For example a foreign key might be called DEPT_EMP_FK and the index will be called DEPT_EMP_FK_I.

So to find sequence issues I can use

SELECT dbac.table_name,
dbacc.constraint_name,
dbaic.index_name,
dbacc.column_name,
dbacc.position fk_pos,
dbaic.column_position index_pos
FROM dba_constraints dbac,
dba_cons_columns dbacc,
dba_indexes dbai,
dba_ind_columns dbaic
WHERE dbac.owner = 'SCHEMA_OWNER' AND
dbac.constraint_type = 'R' AND
dbac.constraint_name LIKE '%FK' AND
dbacc.constraint_name = dbac.constraint_name AND
dbai.owner = dbac.owner AND
dbai.index_name = dbac.constraint_name || '_I' AND
dbaic.index_name = dbai.index_name AND
dbaic.column_name = dbacc.column_name AND
dbaic.column_position != dbacc.position
ORDER BY 1,2,3,4,5,6


and to find foreign keys that don't have any indexes at all I can use

SELECT dbac.table_name,
dbac.constraint_name
FROM dba_constraints dbac
WHERE dbac.owner = 'SIS_OWNER' AND
dbac.constraint_type = 'R' AND
dbac.constraint_name LIKE '%FK' AND
NOT EXISTS(
SELECT 'x'
FROM dba_indexes dbai
WHERE dbai.owner = dbac.owner AND
dbai.index_name = dbac.constraint_name || '_I')
ORDER BY 1,2


But this will bring back some instances that don't need to have a foreign key index. This is because if all the columns in the foreign key are in the primary key and are the first columns in the primary key (and in the correct order) then Oracle will use the primary key index. So any foreign key index will not be used.
So we can refine the query above to cope with finding only those that are not already in the primary key in the correct order.

SELECT dbac.table_name,
dbac.constraint_name
FROM dba_constraints dbac
WHERE dbac.owner = 'SCHEMA_OWNER' AND
dbac.constraint_type = 'R' AND
dbac.constraint_name LIKE '%FK' AND
NOT EXISTS(
SELECT 'x'
FROM dba_indexes dbai
WHERE dbai.owner = dbac.owner AND
dbai.index_name = dbac.constraint_name || '_I') AND
EXISTS(
SELECT 'x'
FROM dba_constraints dbac1,
dba_cons_columns dbacc1,
dba_constraints dbac2,
dba_cons_columns dbacc2
WHERE dbac1.owner = dbac.owner AND
dbac1.table_name = dbac.table_name AND
dbac1.constraint_name = dbac.constraint_name AND
dbacc1.constraint_name = dbac1.constraint_name AND
dbacc1.owner = dbac1.owner AND
dbac2.owner = dbac1.owner AND
dbac2.table_name = dbac1.table_name AND
dbac2.constraint_type = 'P' AND
dbacc2.constraint_name = dbac2.constraint_name AND
dbacc2.owner = dbac2.owner AND
dbacc2.column_name = dbacc1.column_name AND
dbacc2.position != dbacc1.position)
ORDER BY 1,2

Finding all the child tables of a table in Oracle

When doing schema changes there are often times when I want to know all the child tables of the current table. Also when doing a delete and I want to find child tables to make sure the data is cleaned up correctly.

The SQL to do this is pretty easy.

select table_name, constraint_name 

from all_constraints

where constraint_type = 'R'

and r_constraint_name in

    (select constraint_name

    from all_constraints

    where upper(table_name) = upper(:OBJECT_NAME)

    and constraint_type IN ('P', 'U'))

    order by table_name

It would be nice if some of the IDEs for Oracle development had this as standard information about a table.

I created an XML extension for Oracle SQL Developer to give this information as a new tab. Originally posted here.

It is reproduced below as that link needs a login to get to.

Following on from Sue Harper's blog entry about adding your own tabs to SQL Developer this example adds a tab to display all child tables that have a foreign key pointing to the Primary Key or a Unique Key in the context table.

Following the directions on Sue's blog about how to create your own tab http://sueharper.blogspot.com/2007/04/how-to-add-tabs-to-sql-developer.html you can create your own tabs as required.

I wanted a tab that would show me all the child tables of the current table.

The structure of the XML file needed for this is as below

<items>

<item type="editor" node="TableNode" vertical="true">

<title><![CDATA[Child Tables]]></title>

<query>

<sql>

<![CDATA[

select  table_name,

constraint_name 

from all_constraints

where  constraint_type = 'R'

and r_constraint_name in

(select constraint_name

from all_constraints

where upper(table_name) = upper(:OBJECT_NAME)

and constraint_type IN ('P', 'U'))

order by table_name]]>

</sql>

</query>

</item>

</items>

Might be an enhancement I can submit to Quest for SQL Navigator.

Oracle PL/Scope could be useful

Background

PL/Scope is functionality in PL/SQL that collects details of identifiers like variables, parameters, function calls, etc from compiled PL/SQL packages and then stores the details in the database. These details can then be viewed using the ALL_IDENTIFIERS view.


For further information on PL/Scope see this article by Steven Feuerstein and the Oracle Documentation. It can be enabled for a session by executing the following:

ALTER SESSION SET plscope_settings='IDENTIFIERS:ALL' /

To disable PL/Scope use:

ALTER SESSION SET  plscope_settings='IDENTIFIERS:NONE' /

The data in the view can then be used for various analysis and quality checks.

Sample usage - Variable declared but never used

To find if any variables have been created in PL/SQL code but are never used. For some large packages it is easy to lose track of variables that may have been removed from functionality but their declarations remain. The following SQL example will identify any occurrences in the package MY_BIG_PACKAGE where a variable has been declared but not used on any other line in the package.

select *
from all_identifiers ai
where ai.owner = 'SCHEMA_OWNER' and
ai.type = 'VARIABLE' and
ai.object_name = 'MY_BIG_PACKAGE' and
not exists
(select 'x'
from all_identifiers ai2
where ai2.signature = ai.signature and
ai2.line != ai.line)

Sample usage - Conforming to naming standards

This example will find any variables in the package MY_BIG_PACKAGE that do not start with either of my standard prefixes v_ for standard variables or gv_ for global variables.

Note that the even though the code has the value in lower-case the value will be stored as upper-case when selecting ALL_IDENTIFIERS

select *
from all_identifiers ai
where ai.owner = 'SCHEMA_OWNER' and
ai.object_name = 'MY_BIG_PACKAGE' and;
ai.type = 'VARIABLE' and
ai.usage = 'DECLARATION' and
(ai.name not like 'V/_%' ESCAPE '/' and
ai.name not like 'GV/_%' ESCAPE '/')

This example can then be extended to look at many other standards like constants, parameters, cursors and even the procedure names themselves within the same package.

SELECT *
FROM all_identifiers ai
WHERE ai.owner = 'SCHEMA_OWNER' AND
ai.object_name = 'MY_BIG_PACKAGE' AND
((ai.type = 'VARIABLE' AND
ai.usage = 'DECLARATION' AND
-- variables start with v_ or gv_
(ai.name NOT LIKE 'V/_%' ESCAPE '/' AND 
ai.name NOT LIKE 'GV/_%' ESCAPE '/')) OR
-- cursor names start with c_
(ai.type = 'CURSOR' AND
ai.usage = 'DECLARATION' AND
ai.name NOT LIKE 'C/_%' ESCAPE '/') OR
(ai.type = 'FORMAL IN' AND -- parameters
ai.usage = 'DECLARATION' AND
-- parameters start with p_
(ai.name NOT LIKE 'P/_%' ESCAPE '/' AND
-- cursor parameters start with cp_
ai.name NOT LIKE 'CP/_%' ESCAPE '/' ))OR
-- constants start with cst_
(ai.type = 'CONSTANT' AND
ai.usage = 'DECLARATION' AND
ai.name NOT LIKE 'CST/_%' ESCAPE '/') OR
-- procedure names start with 3 letters then p_
(ai.type = 'PROCEDURE' AND
ai.usage = 'DECLARATION' AND
ai.name NOT LIKE '___P/_%' ESCAPE '/'))


Of course these are my standards and would need to be customised for your own application.