SQL Objects vs. PL/SQL Tables So I was writing a small procedure to loop through a procedure's arguments (all_arguments).

After getting it working, I then began to port it to packaged code, which is where I ran into a little problem.

Here's the initial statement:

DECLARE
  TYPE r_records IS RECORD
  (
    owner VARCHAR2(30),
    package_name VARCHAR2(30),
    procedure_name VARCHAR2(30),
    overload VARCHAR2(40),
    argument_name VARCHAR2(30),
    sequence INTEGER,
    in_out VARCHAR2(9)
  );
  TYPE t_records IS TABLE OF R_RECORDS INDEX BY BINARY_INTEGER;
  l_records T_RECORDS;
BEGIN
  SELECT
    owner, 
    package_name, 
    object_name,
    overload,
    argument_name,
    sequence, 
    in_out 
  BULK COLLECT INTO l_records
  FROM dba_arguments
  WHERE package_name = 'DBMS_UTILITY'
    AND object_name IN ( 'COMPILE_SCHEMA', 'INVALIDATE' )
  ORDER BY owner, package_name, object_name, position;
END;
/

Runs fine.

My first step to refactor was to use the TYPE declaration in the package header. But you can't do that (little rusty on pl/sql tables). So I created SQL Objects or User Defined Types (UDT).

CREATE TYPE r_procedure_arguments AS OBJECT
(
  owner VARCHAR2(30),
  package_name VARCHAR2(30),
  procedure_name VARCHAR2(128),
  overload VARCHAR2(40),
  argument_name VARCHAR2(30),
  position INTEGER,
  sequence INTEGER,
  in_out VARCHAR2(9)
);
/
show errors

CREATE TYPE t_procedure_arguments AS TABLE OF R_PROCEDURE_ARGUMENTS;
/
show errors

Then I rewrote the anonymous block to use the UDT.

CJUSTICE@ELEVEN>DECLARE
  2    l_records T_PROCEDURE_ARGUMENTS;
  3  BEGIN
  4    SELECT
  5      owner,
  6      package_name,
  7      object_name,
  8      overload,
  9      argument_name,
 10      position,
 11      sequence,
 12      in_out 
 13    BULK COLLECT INTO l_records
 14    FROM all_arguments;
 15  END;
 16  /
  FROM all_arguments;
  *
ERROR at line 14:
ORA-06550: line 14, column 3:
PL/SQL: ORA-00947: not enough values
ORA-06550: line 4, column 3:
PL/SQL: SQL Statement ignored

not enough values? OK, let's go through it. There are 8 "columns" in the defined object and 8 in the SELECT clause. Hmmm...I tried adding some extra values at the end:

DECLARE
  l_records T_PROCEDURE_ARGUMENTS;
BEGIN
  SELECT
    owner,
    package_name,
    object_name,
    overload,
    argument_name,
    position,
    sequence,
    in_out, 1, 2, 3, 4
  BULK COLLECT INTO l_records
  FROM all_arguments;
END;
/

Same result, not enough values.

I spent the next hour toggling between gmail, facebook and meebo thinking about the problem.

Wait! You have to "cast" the values from the SELECT clause!

CJUSTICE@ELEVEN>DECLARE
  2    l_records T_PROCEDURE_ARGUMENTS;
  3  BEGIN
  4    SELECT
  5      R_PROCEDURE_ARGUMENTS( owner,
  6      package_name,
  7      object_name,
  8      overload,
  9      argument_name,
 10      position,
 11      sequence,
 12      in_out )
 13    BULK COLLECT INTO l_records
 14    FROM all_arguments;
 15  END;
 16  /

PL/SQL procedure successfully completed.

Duh.

So I post it here so next time I don't waste an hour trying to remember why I can't get it to work.

Labels: development, plsql, sql, udt

  ¶ 12/30/2008 01:32:00 AM