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.
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...