PL/SQL: Pipelined Function

As with Friday's post, pipelined functions are something I use rarely, thus end up looking up how to do it.

So with a little help from Tim Hall [ blog | twitter ], I shall do a quick example.

I'm trying to integrate these 2 posts, PL/SQL: Split URL Parameters and PL/SQL: Split Key-Value Pairs. I don't want to store the parsed response or request string just yet, just the string. But in the off chance I want it parsed, I don't want to load it up into excel or something and begin the arduous task of breaking it down. I will (just remembered) have to parse the response string when I receive it though to figure out whether or not the transactions was Approved or Declined.

Let's start with the types:

CREATE OR REPLACE TYPE r_key_value_record IS OBJECT
(
  orderof NUMBER(5),
  key_string VARCHAR2(1000),
  value_string VARCHAR2(1000)
)
/

CREATE OR REPLACE TYPE t_key_value_table AS TABLE OF R_KEY_VALUE_RECORD
/

The simple function, just for demonstration purposes:

CREATE OR REPLACE
FUNCTION get_key_value_pairs RETURN T_KEY_VALUE_TABLE PIPELINED
IS
BEGIN
  FOR i IN 1..10 LOOP
    PIPE ROW ( R_KEY_VALUE_RECORD( i, i, i ) );
  END LOOP;
  RETURN;
END get_key_value_pairs;
/
show errors

This will definitely be expanded in the days to come to include those previous posts mentioned above. I will post the final result and link it up here as well.

Nested Tables

One concept of Oracle that I've always been intrigued by is Nested Tables. Nested tables are, basically, a table (dataset) stored in a single column. They are part of the object-relational features of Oracle. Now, I've never found a practical use for them as it's still relational data in reality.

Anyway, I've been playing around with them in regards to the test harness that I've mulling over.

I decided to try them out to store the arguments for each procedure/function. I can certainly string this together at runtime, but again, just trying to learn something new.

I start off with some SQL Objects:

CREATE TYPE r_arguments AS OBJECT
(
  argument_name VARCHAR2(30),
  variable_name VARCHAR2(30),
  data_type VARCHAR2(30),
  defaulted VARCHAR2(1),
  position INTEGER,
  sequence INTEGER,
  in_out VARCHAR2(9)
);
/
show errors

CREATE OR REPLACE
TYPE t_arguments AS TABLE OF R_ARGUMENTS
/

I'll use T_ARGUMENTS as a data type for my table definition:

CREATE TABLE procedures
(
  owner VARCHAR2(30)
    CONSTRAINT nn_owner_procedures NOT NULL,
  package_name VARCHAR2(30),
  procedure_name VARCHAR2(30)
    CONSTRAINT nn_procedurename_procedure NOT NULL,
  function_or_procedure VARCHAR2(1)
    CONSTRAINT forp_forp_procedures CHECK ( function_or_procedure IN ( 'F', 'P' ) )
    CONSTRAINT nn_forp_procedures NOT NULL,
  overload VARCHAR2(40),
  arguments T_ARGUMENTS
)
  NESTED TABLE arguments STORE AS args;

Thanks to Mr. Morgan and his always helpful library I was able to finally figure out the syntax. Specically, I was ommitting the NESTED TABLE clause there at the end.

When you describe the table and set the describe dept to all it looks like this:

CJUSTICE@ELEVEN>set describe depth all

@DESC PROCEDURES
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ----------------
 OWNER                                                 NOT NULL VARCHAR2(30)
 PACKAGE_NAME                                                   VARCHAR2(30)
 PROCEDURE_NAME                                        NOT NULL VARCHAR2(30)
 FUNCTION_OR_PROCEDURE                                 NOT NULL VARCHAR2(1)
 OVERLOAD                                                       VARCHAR2(40)
 ARGUMENTS                                                      T_ARGUMENTS
   ARGUMENT_NAME                                                VARCHAR2(30)
   VARIABLE_NAME                                                VARCHAR2(30)
   DATA_TYPE                                                    VARCHAR2(30)
   DEFAULTED                                                    VARCHAR2(1)
   POSITION                                                     NUMBER(38)
   SEQUENCE                                                     NUMBER(38)
   IN_OUT                                                       VARCHAR2(9)

I got that wrapped up, so how to INSERT?

DECLARE
  l_arguments T_ARGUMENTS;
BEGIN
  l_arguments := T_ARGUMENTS();
  l_arguments.EXTEND(1);
  l_arguments(1) := R_ARGUMENTS
                      ( argument_name => 'TESTING',
                        variable_name => 'L_TESTING',
                        data_type => 'NUMBER',
                        defaulted => 'Y',
                        position => 1,
                        sequence => 1,
                        in_out => 'IN' );

  INSERT INTO procedures
    ( owner,
      package_name,
      procedure_name,
      function_or_procedure,
      overload,
      arguments )
  VALUES
    ( 'TESTING',
      'TESTING',
      'TESTING',
      'F',
      NULL,
      l_arguments );
END;
/

And it looks like this:

OWNER   PACKAGE PROCEDU F OVER ARGUMENTS(ARGUMENT_NAME, VARIABLE_NAME, DATA_TYPE, DEFAULTED, POSITION, SEQUENCE
------- ------- ------- - ---- --------------------------------------------------------------------------------
TESTING TESTING TESTING F      T_ARGUMENTS(R_ARGUMENTS('TESTING', 'L_TESTING', 'NUMBER', 'Y', 1, 1, 'IN'))

What if you want to be able to select a given value from inside the nested table? I swear in an earlier version of Oracle you just had to qualify, or maybe I'm just crazy, but here's how you do it now:

SELECT variable_name
FROM 
  procedures p,
  TABLE( p.arguments );

VARIABLE_NAME
------------------------------
L_TESTING

You need to CAST your nested table using the TABLE clause. I still haven't figured out how (or if) you ever need to use ARGS (from the NESTED TABLE clause above).

Alright, now I'm going to add 10 records into the nested table. In the relational world, you'd have 2 tables to do this. One for the procedures and one for the arguments. I'll do it in "1."

DECLARE
  l_arguments T_ARGUMENTS;
BEGIN
  l_arguments := T_ARGUMENTS();

  FOR i IN 1..10 LOOP
    l_arguments.EXTEND(1);
    l_arguments(i) := R_ARGUMENTS
                        ( argument_name => 'TESTING',
                          variable_name => 'L_TESTING',
                          data_type => 'NUMBER',
                          defaulted => 'Y',
                          position => 1,
                          sequence => i,
                          in_out => 'IN' );
  END LOOP;

  INSERT INTO procedures
    ( owner,
      package_name,
      procedure_name,
      function_or_procedure,
      overload,
      arguments )
  VALUES
    ( 'TESTING',
      'TESTING',
      'TESTING',
      'F',
      NULL,
      l_arguments );
END;
/

The result of a SQL statements looks like this:

OWNER   PACKAGE PROCEDU F OVER ARGUMENTS(ARGUMENT_NAME, VARIABLE_NAME, DATA_TYPE, DEFAULTED, POSITION, SEQUENCE
------- ------- ------- - ---- --------------------------------------------------------------------------------
TESTING TESTING TESTING F      T_ARGUMENTS(R_ARGUMENTS('TESTING', 'L_TESTING', 'NUMBER', 'Y', 1, 1, 'IN'), R_AR
                               GUMENTS('TESTING', 'L_TESTING', 'NUMBER', 'Y', 1, 2, 'IN'), R_ARGUMENTS('TESTING
                               ', 'L_TESTING', 'NUMBER', 'Y', 1, 3, 'IN'), R_ARGUMENTS('TESTING', 'L_TESTING',
                               'NUMBER', 'Y', 1, 4, 'IN'), R_ARGUMENTS('TESTING', 'L_TESTING', 'NUMBER', 'Y', 1
                               , 5, 'IN'), R_ARGUMENTS('TESTING', 'L_TESTING', 'NUMBER', 'Y', 1, 6, 'IN'), R_AR
                               GUMENTS('TESTING', 'L_TESTING', 'NUMBER', 'Y', 1, 7, 'IN'), R_ARGUMENTS('TESTING
                               ', 'L_TESTING', 'NUMBER', 'Y', 1, 8, 'IN'), R_ARGUMENTS('TESTING', 'L_TESTING',
                               'NUMBER', 'Y', 1, 9, 'IN'), R_ARGUMENTS('TESTING', 'L_TESTING', 'NUMBER', 'Y', 1
                               , 10, 'IN'))

Not too pretty.

I now want to know how many arguments (yes, I already know, it's 10, but humor me):

SELECT 
  owner, 
  package_name, 
  procedure_name, 
  overload,
  COUNT(*) c
FROM 
  procedures p,
  TABLE( p.arguments )
GROUP BY
  owner,
  package_name,
  procedure_name,
  overload;

OWNER   PACKAGE PROCEDU OVER          C
------- ------- ------- ---- ----------
TESTING TESTING TESTING              10

Voila!

I still don't have a practical application for this as this can be done on the fly using PL/SQL collections. Perhaps this in combination with your Middle Tier application (returning UDTs back to the calling application)...

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.