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: oradb, plsql, sql, udt
