Ideas for Enhancements to
Oracle PL/SQL

Magnum Opus

 Home    PL/SQL     Witness     Code Generation    Everything Else

I hope to launch a worldwide survey of PL/SQL developers in order to do a comprehensive assessment of the user community needs for enhancements to PL/SQL. For now, however, I will offer this very rough log of ideas I have and those I receive from others:

I have one (of many) pertaining to collections/PL/SQL tables or even just records. How about a KEYS method which would return an array of the fields in the associative array. Perl has this and it's indispensable. So instead of having to explicitly know what all my fields on the collection are I could programmatically process them.

type t_rec is record (
fld1 varchar2(20)
,fld2 number
,fld3 date
, ....
);

function compareRecs(
p_rec1 t_rec
p_rec2 t_rec
) return Boolean
is
bResult Boolean := true;
begin

--
-- Now to compare them I'd like to write something like this
-- The KEYS method would return a list/array of (fld1, fld2, fld3, ...)
--
for fld in p_rec1.KEYS
loop
if ( p_rec1.value(fld) != p_rec2.value(fld) )
then
bResult := false;
exit;
end if;
end loop;

..... 
return bResult;
end;

It doesn't seem that difficult and they must be storing that in some array in order to retrieve it the way it is now. It certainly would make ref cursors and collections much more dynamic allowing some powerful functions to be written regardless of the record type.

Topic: Support "fine grained access permissions" for packaged programs
From: Chris Rimmer, November 2002

Finer grained access permissions to packages would be good. At the moment, it is all or nothing. Maybe it would be possible to define "interfaces" to packages? So if you create a package to encapsulate a table, you could mark some procedures as being in the "select" interface, some in the "update" interface and others in the "insert" interface. You could then do something along the lines of GRANT EXECUTE ON MYPACKAGE.SELECT to grant execute privileges only on the procedures in the "select" interface. The problem at the moment is that in this situation it is necessary to split up the package just to control access.

Topic: Improve error information returned by SAVE EXCEPTIONS
From: Steven Feuerstein, November 2002

Updated 3 rows.

Error 1 occurred during iteration 2 updating name to
Oracle error is ORA-01407 cannot update () to NULL

Error 2 occurred during iteration 4 updating name to BIGBIGGERBIGGESTABCABCABCABCABCABCABCABCABCABCABC
Oracle error is ORA-01401 inserted value too large for column

ORA-01407 cannot update ("SCOTT"."EMPLOYEE"."LAST_NAME") to NULL

Copyright 1999-2002 Steven Feuerstein. All rights reserved.

Incredibly Handy

For Beginners