21st Century PL/SQL

April 04, 2005

Expand the use of ROWTYPE construct

With 8i (I think) came the ability to insert a full row into a table using something like:

INSERT INTO TABLEA VALUES lr_TABLEA;

Where lr_TABLEA was a variable of TABLEA%ROWTYPE. Excellent expansion of the PL/SQL language I think everyone would agree...

I would like the ability to assign a full row to another row:

lr_TABLEA := lr_TABLEA_tmp;

Also I would like to be able to compare rows of data in one line:

IF lr_TABLEA = lr_TABLEA_tmp THEN
...

etc

Posted by David Torbett at 10:37 AM | Add your thoughts (4)

July 11, 2005

Private Attributes

You should be able to define private attributes of an Object in the TYPE BODY, rather than only public attributes defined in the TYPE specification.

Posted by John Flack at 01:08 PM | Add your thoughts (5)

October 02, 2005

Add advanced O-O features like those found in Eiffel

From Zlatko Sirotic: 1. References between transient objects (instances of objects types) and (then) garbage collector 2. Generic classes (templates, generics) like in Eiffel, C++, Java 1.5 (PL/SQL was modeled after ADA 83, and ADA 83 has generic packages) 3. Multiple inheritance like in Eiffel (inner classes like in Java - no, please) 4. Design By Contract like in Eiffel (C++ / Java 1.4 assert is not enough)

Posted by sf at 09:47 AM | Add your thoughts (0)

October 07, 2005

Make table functions even more like tables by providing %ROWTYPE\'s

Consider this code that creates two table functions and nests them in a query.

CREATE OR REPLACE TYPE type_o AS OBJECT ( col1 NUMBER,
                               col2 VARCHAR2(30) );
/
CREATE TYPE type_t AS TABLE OF type_o;
/

CREATE OR REPLACE FUNCTION first_in_chain
                  RETURN type_t IS
  v_ret_val type_t := type_t();
BEGIN
  v_ret_val.EXTEND(3);
  v_ret_val(1) := type_o(1,\'One\');
  v_ret_val(2) := type_o(2,\'Two\');
  v_ret_val(3) := type_o(3,\'Three\');
  RETURN(v_ret_val);
END;
/

CREATE OR REPLACE PACKAGE ref_cursors AS

  TYPE v_record IS RECORD ( col1 number, col2 varchar2(30) );
  TYPE v_rc IS REF CURSOR RETURN v_record;

END;
/

CREATE OR REPLACE FUNCTION second_in_chain ( p_curs ref_cursors.v_rc )
                  RETURN type_t IS
  v_ret_val type_t := type_t();
  v_rec ref_cursors.v_record;

BEGIN
  LOOP
    FETCH p_curs INTO v_rec;
    EXIT WHEN p_curs%NOTFOUND;
    v_ret_val.EXTEND(1);
    v_ret_val(v_ret_val.LAST) := type_o(v_rec.col1,v_rec.col2);
  END LOOP;
  RETURN(v_ret_val);
END;
/

SELECT *
  FROM TABLE(second_in_chain(CURSOR(SELECT *
                                      FROM TABLE(first_in_chain))))
/

It should be easier to fetch the values from the passed in REF Cursor. Right now a completely seperate PL/SQL record has to be defined. Something like the following would be easier:

CREATE OR REPLACE FUNCTION second_in_chain ( p_curs ref_cursors.v_rc )
                  RETURN type_t IS
  v_ret_val type_t := type_t();
  v_rec first_in_chain%ROWTYPE;  -- rowtype of the function
...
  FETCH p_curs INTO v_rec;

Or perhaps the object could have the rowtype like this?

CREATE OR REPLACE FUNCTION second_in_chain ( p_curs ref_cursors.v_rc )
                  RETURN type_t IS
  v_ret_val type_t := type_t();
  v_rec object_o%ROWTYPE;  -- rowtype of the function
...
  FETCH p_curs INTO v_rec;

Posted by Darryl Hurley at 04:25 PM | Add your thoughts (1)

October 10, 2005

Compile Time Sizing

Compile time item: CREATE TABLE T_COLDEF_DOC ( COLDEF_LABEL VARCHAR2(50) AREA_OF_RESP VARCHAR2(30) ); In Code: C_Len_ColDef_Label CONSTANT PLS_INTEGER := T_ColDef_Doc .ColDef_Label %SIZE; It would be nice if it evaluate back into 50. It would make it easier to handle string overflow conditions etc.

Posted by Steve Booth at 09:29 AM | Add your thoughts (2)