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.

Labels: plsql, sql, udt

  ¶ 8/09/2009 06:27:00 PM