Tuesday, December 30, 2008

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.

9 comments:

Crisatunity said...

Why can't you declare the record type in the package header?

oraclenerd said...

I probably mis-stated. You can't used a declared (PLSQL) type as a RETURN object in a package. Well, actually, I might still be doing it wrong, but this is what I did (apologies for the lack of formatting):<pre class="code" >
CREATE OR REPLACE
PACKAGE foo
AS
TYPE r_records IS RECORD
(
owner VARCHAR2(30),
package_name VARCHAR2(30)
);
TYPE t_records IS TABLE OF R_RECORDS INDEX BY BINARY_INTEGER;
l_records T_RECORDS;
FUNCTION foo RETURN L_RECORDS;
END foo;
/
show errors

Errors for PACKAGE FOO:

LINE/COL ERROR
-------- ---------------------------------------------------
10/3 PL/SQL: Declaration ignored
10/23 PLS-00488: 'L_RECORDS' must be a type</pre>

Crisatunity said...

Why can't you do something like this?

SQL> create or replace package rectest is
2
3 type aa_lite_record_type is record (
4 owner all_arguments.owner%type,
5 package_name all_arguments.package_name%type );
6
7 type aa_lite_table_type is table of aa_lite_record_type index by binary_integer;
8 type aa_table_type is table of all_arguments%rowtype index by binary_integer;
9
10 procedure p ( p1 in aa_lite_table_type );
11 function f return aa_lite_table_type;
12
13 procedure p ( p1 in all_arguments%rowtype );
14 function f return all_arguments%rowtype;
15
16 procedure p ( p1 in aa_table_type );
17 function f return aa_table_type;
18
19 end rectest;
20 /

Package created.

SQL> show errors
No errors.

DomBrooks said...

RETURN T_RECORDS;

DomBrooks said...

Actually, in these circumstances, if I wanted to use and return a pl/sql type, I would probably declare the cursor in the header, declare the table type of being the cursor%rowtype and then go from there.

That way, if you need to add or take away a column you just do it to the cursor and everything else sorts itself out.

Chris said...

When working with the %_ARGUMENTS views, you probably also want to bear the DATA_LEVEL column in mind. If your procedures have record type or object type arguments, %_ARGUMENTS contains records for both the formal argument (DATA_LEVEL=0) and the type's columns (DATA_LEVEL>0). Maybe you already knew, but I often forget it when dealing with these views.

oraclenerd said...

@chris

Thanks for that tip. I am actively learning more and more about that table and it's column's meanings.

My first step was going to be just use the basics, VARCHAR2, NUMBER and DATE. Looming large right behind that are the PL/SQL Tables, Records and other more advanced data types.

empraptor said...

Looks like you didn't have to use objects at all.

The following line

"FUNCTION foo RETURN L_RECORDS;"

in your code should have been

"FUNCTION foo RETURN T_RECORDS;"

You are giving the compiler a variable when it's expecting you to specify the return type.

empraptor said...

i just realized dombrooks made the same point. and that this blog entry is over four months old.