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.