21st Century PL/SQL

April 04, 2005

Additional Dependency details

It would be nice that, as well as stating that a package is dependent on a table, it would show whether it performed selects/inserts/updates/deletes.

This would be a great improvement for impact analysis. For example if a new mandatory column was added to a table, it would be simple to find all the packages which inserted into the table.

Also add a PRAGMA or similar to state that a package is 'dependent' on an object that is accessed dynamically.

Posted by at 11:44 AM | Add your thoughts (0)

Permit DDL without recourse to Dynamic SQL

With some processes (such as large data loads), it is desirable to perform some DDLs (such as disabling/enabling constraints or indexes).

Coding these as dynamic statements means missing out on compile time validation.

Posted by at 11:45 AM | Add your thoughts (0)

April 25, 2005

Remove implementation restriction on FORALL collections of records

Ever since FORALL was introduced, it has been irksome that we cannot reference individual elements of a collection of records inside a FORALL construct, i.e:-

   FORALL i IN collection.FIRST .. collection.LAST
      UPDATE some_table
      SET    some_column = collection(i).some_attribute
      WHERE  pk_column = collection(i).pk_attribute;

...yields PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records.

Given that 9i introduced BULK COLLECT into collections/associative arrays of records, it should follow that the example use of FORALL is perfectly natural and needed to complete the implementation of bulk processing.

The SET ROW feature of FORALL in 9i is unsatisfactory as it requires a second array to target the WHERE clause correctly and also updates the primary key of the target table.

Posted by Adrian Billington at 04:37 PM | Add your thoughts (1)

October 06, 2005

Add named notation support to SQL INSERT statements in PL/SQL

Because you loose control in which columns you are inserting In insert statementes have more than 10 columns will be helpful this syntax insert into table column1=>1, column2=>2 and insert into table column1=>a1, column2=>a2 (select a1,a2 from yyy)

Posted by Juan Carlos Reyes at 02:23 PM | Add your thoughts (2)

synonyms for package functions

I would like a to create a synonym for dbms_stats.gather_stats a So I could execute a(\'asdfafds\') In this moment you only can create a synonym for the package alone and you are forced to execute a.gather_stats(\'asdfafds\')

Posted by Juan Carlos Reyes at 03:14 PM | Add your thoughts (0)

November 08, 2005

Group functions must NOT return a row when no rows are fetched

SELECT SUM(salary) FROM EMP WHERE 1 = 2

The above query returns a row, but the query as such without the Group function does not return any row. This can easlily lead to problems in PL/SQL like -

DECLARE
   CURSOR cur IS (SELECT SELECT SUM(salary) tot_sal FROM EMP WHERE SALARY > 5000);
cur_rec cur%ROWTYPE;
BEGIN
   OPEN cur;
   FETCH cur INTO cur_rec;
   IF (cur%FOUND) THEN
      process_salary(cur_rec.tot_sal);
   END IF;
END;

In the above case if the EMP table does not have any records with SALARY > 5000, the cursor will still return a row and it gets fetched and the process_salary() procedure will fail if it does any processing assuming that tot_sal will not be null. So to avoid this we always have to cautiously use -

IF (cur%FOUND) AND (cur.tot_sal IS NOT NULL) THEN
   process_salary(cur_rec.tot_sal);
END IF;

This can be avoided, if the Group function does not return any row, when no rows are actually returned by the SQL query.

Posted by Mukundhan Seshadri at 09:24 AM | Add your thoughts (2)

October 26, 2006

Allow the use of a function which returns boolean in a sql statement

Currently it\'s not possible to call a pl/sql function in an sql statement which returns a boolean. It\'s necessary to always write a wrapper function which converts the boolean to a number, so that the function can be used in a sql statement.

Posted by Patrick wolf at 01:29 PM | Add your thoughts (1)