Skip to Main Content
  • Questions

  • How to get FULL Result Set in a Variable

Breadcrumb

Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions

Question and Answer

Tom Kyte

Thanks for the question, Pramod.

Asked: November 25, 2014 - 12:11 pm UTC

Last updated: November 26, 2014 - 7:05 pm UTC

Version: 11.2.0.3

Viewed 1000+ times

You Asked

Hello Tom,

We are developing an application internally, for which I am using Oracle PL-SQL. As part of a specific function, I am trying to execute a SELECT * command using PL-SQL, store the output of this command in a variable, and display it.

The code is below, which is failing!

CREATE OR REPLACE
PROCEDURE MULTIPLE_CURSORS_PROC
IS
  v_mview_owner VARCHAR2(40);
  v_mview_name  VARCHAR2(40);
  v_mview_data  VARCHAR2(4000);
  v_mview_raw_data CLOB;
  CURSOR get_mview_data
  IS
    SELECT 'select * from '
      ||object_name SQL_TEXT
    FROM user_objects
    WHERE OBJECT_TYPE='MATERIALIZED VIEW'
    AND object_name LIKE '%CUMULATIVE%';
BEGIN
  FOR x IN get_mview_data
  LOOP
    v_mview_data := x.SQL_TEXT;
    dbms_output.put_line(v_mview_data);
    EXECUTE immediate v_mview_data
    into v_mview_raw_data;
  END LOOP;
END MULTIPLE_CURSORS_PROC;


The error I am getting is:

Error starting at line 2 in command:
BEGIN
  MULTIPLE_CURSORS_PROC();
END;
Error report:
ORA-00932: inconsistent datatypes: expected - got -
ORA-06512: at "GORR_1410.MULTIPLE_CURSORS_PROC", line 35
ORA-06512: at line 2
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
*Cause:    
*Action:
select * from CUMULATIVE_SPEN_GRP_PARTNER_W


Kindly advise how I can resolve this.

Thanks,
Pramod.

and Tom said...

you would need to know the names and number of columns in the query you put into v_mview_data to use native dynamic sql (execute immediate).

You are trying to (in effect) do this:


execute immediate 'select ename, hiredate from emp' into some_string


that doesn't work because

a) select into can deal with a single row only
b) select into needs a host variable for each selected column


You would have to use DBMS_SQL and procedurally process the data. DBMS_SQL will allow you to prepare a statement, ask it how many columns it has and then fetch a row - access the i'th column (you can loop over the columns) and process the data.

here is an example procedure:

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:68212348056

that does just this. It uses UTL_FILE, you would use dbms_output...



Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here