How to select data out of an Oracle collection/array

oracleplsql

Due to needing some complex data manipulation and not being able to use PHP, I crafted a procedure (not stored, it's just run when we run the query) in Oracle to manipulate the data and store it in an associative array:

TYPE changedData IS RECORD (id int, name varchar2(255), vendor_id int);
TYPE changedDataArray IS TABLE OF changedData INDEX BY varchar2(255);

And I've populated it and it has all the data, awesome.

So how do I get that sent back to the client resembling rows? I could do DBMS_OUTPUT but that doesn't help when I'm trying to run this by a program expecting a tabular response. I've looked all over the internet and I'm at a loss.

Is making a temporary table and inserting the data into that and then selecting the only method? I'd do that except I don't have access to create a temporary table on the server, and getting permissions here is pulling teeth so if I can avoid that, it'd be awesome. I mean, I have the array right there – changedDataArray. And yet there seems to be no way to actually output that in a tabular fashion to the client?

Best Answer

You can create a pipelined function. Some examples here.

Related Solutions

Retrieve Indices of Associative Array as Collection

Is there some operator or function that can give me the indices of the array

No, you have to loop through the associative array:

declare 
       type a_arr_t is table of PLS_INTEGER index by PLS_INTEGER;
       type keys_t is table of PLS_INTEGER;
       a_arr a_arr_t;
       keys_ keys_t := keys_t();
       l_index integer;
begin
      a_arr(1)   := 2;
      a_arr(10)  := 4;
      a_arr(100) := 6;

      l_index := a_arr.first;
      while (l_index is not null)
      loop
          keys_.extend();
          keys_(keys_.count):=l_index;
          dbms_output.put_line(keys_(keys_.count));
          l_index := a_arr.next(l_index);
      end loop;
end;
/

/*
1
10
100
*/

How to create a function(table_name) that return a collection in Oracle

I haven't syntax checked this but it should put you on the right path.

This should be put in a package but for demonstration purposes...

PROCEDURE GET_COLUMNS(table_name_in IN VARCHAR2, v_query_out OUT sys_refcursor)
IS
v_count NUMBER(9);
CURSOR the_columns IS 
Select COLUMN_NAME 
from all_tab_columns 
where table_name='table_name_in';
v_query VARCHAR2(4000):= 'Select  ';
BEGIN
IF table_name IS NOT NULL THEN
--requires permission to access dba_tables, if not available use ALL_TABLES
--or USER_TABLES
   SELECT count(*)
   INTO v_count  
   FROM dba_tables
   where table_name = table_name_in;
--might need the schema name too in a multi schema environment
   IF v_count <> 1 THEN
      RAISE bad_table_name;
   END IF;
ELSE
     RAISE bad_table_name;
END IF;

for all_columns in the_columns LOOP
v_query := vquery || all_columns.COLUMN_NAME ||',';
END LOOP;
--trim off the last comma
v_query := SUBSTR(v_query, 1, LENGTH(v_query) - 1);
v_query := v_query ||' from ' || table_name_in;

OPEN v_query_out for v_query;

EXCEPTION
when bad_table_name THEN
--log an error message
DBMS_OUTPUT.PUT_line('Table name is not accessible or does not exist');
RAISE; --could raise an custom exception if more detailed logging required
END;

This should work on Oracle 9,10 and 11 and could be simplified for 12 by using RETURN_RESULT.

Related Question