CODEWITHSUNDEEP
sqloracle-databaseplsql
D.J.
D.J.

Reputation: 386

How to select all rows from the oracle PL/SQL collection into SYS_REFCURSOR

Note: I have seen many solution and all says I can not use SQL with a PL/SQL type. I must have to use CREATE or REPLACE, but my restriction is I can not use system object for this task.

What I have tried the below example returns only last row.

create or replace PROCEDURE SP_TEST (TEST_cursor OUT SYS_REFCURSOR)IS
 TYPE TEMP_RECORD IS RECORD(
                    entries   NUMBER,
                    name      VARCHAR2(50),
                    update    VARCHAR2(200)
                );
TYPE TEMP_TABLE IS TABLE OF TEMP_RECORD INDEX BY PLS_INTEGER;
VAR_TEMP       TEMP_TABLE;
IDX            PLS_INTEGER := 0;
BEGIN 
VAR_TEMP(IDX).cur_entries := 1;
VAR_TEMP(IDX).cur_entries := 2;
 OPEN TEST_cursor FOR
 SELECT VAR_TEMP(idx).cur_entries from dual;
END SP_TEST;

Another way tried.

OPEN TEST_cursor FOR
  SELECT * FROM TABLE(VAR_TEMP)
--- It gives compilation error ora-

Upvotes: 0

Views: 3097

Answers (2)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

Given that you can't create an object in the database, the only solution I can think of is to use dynamic SQL:

CREATE TYPE temp_record AS OBJECT
(
    entries NUMBER,
    entry_name VARCHAR2 (50),
    update_value VARCHAR2 (200)
);

CREATE TYPE temp_table IS TABLE OF temp_record;

CREATE OR REPLACE PROCEDURE sp_test (test_cursor OUT SYS_REFCURSOR) IS
    var_temp temp_table := temp_table ();
    strSql  VARCHAR2(32767);
BEGIN
    -- Populate the temp table, or pass it in from elsewhere

    var_temp.EXTEND();
    var_temp (var_temp.LAST).entries := 1;
    var_temp (var_temp.LAST).entry_name := 'test';

    FOR i IN 1..var_temp.COUNT LOOP
      strSql := strSql ||
                  CASE
                    WHEN LENGTH(strSql) > 0 THEN ' UNION ALL '
                    ELSE NULL
                  END ||
                  'SELECT ' || var_temp.ENTRIES || ' ENTRIES,' ||
                            '''' || var_temp.ENTRY_NAME || ''' ENTRY_NAME FROM DUAL';
    END LOOP;

    OPEN test_cursor FOR strSql;
END sp_test;

Now, I may have messed up the string concatenation logic there a bit, but the objective is to end up with an SQL string which looks something like

SELECT 1 ENTRIES,'test' ENTRY_NAME FROM DUAL
UNION ALL
SELECT 2 ENTRIES,'test 2' ENTRY_NAME FROM DUAL
UNION ALL
SELECT 3 ENTRIES,'test_3' ENTRY_NAME FROM DUAL

but, of course, without the nice white space and etc.

The 32K limit on dynamic SQL may bite you eventually, but if push comes to shove you can the DBMS_SQL package to handle arbitrarily large SQL text, although that presents its own challenges.

Best of luck.

Upvotes: 1

Allan
Allan

Reputation: 17429

In order to reference types in SQL (as opposed to PL/SQL), they must be created as objects in the database. This is effectively a scope issue: when you run SQL you are shifting to a different context. Any structures that you have created locally are not available there.

CREATE TYPE temp_record AS OBJECT
(
    entries NUMBER,
    entry_name VARCHAR2 (50),
    update_value VARCHAR2 (200)
);

CREATE TYPE temp_table IS TABLE OF temp_record;

CREATE OR REPLACE PROCEDURE sp_test (test_cursor OUT SYS_REFCURSOR) IS
    var_temp temp_table := temp_table ();
BEGIN
    var_temp.EXTEND ();
    var_temp (var_temp.LAST).entries := 1;
    var_temp (var_temp.LAST).entry_name := 'test';

    OPEN test_cursor FOR SELECT * FROM TABLE (var_temp);
END sp_test;

Upvotes: 0

Related Questions