CODEWITHSUNDEEP
oracle-databaseplsql
Random guy
Random guy

Reputation: 923

ORA-00932: inconsistent datatypes: expected - got - ORA-06512:

I have written a simple plsql block and it was executed successfully whenI compile it.

 declare

 begin
  EXECUTE IMMEDIATE 'SELECT  COLUMN_ID,
        COLUMN_NAME || '' '' || DATA_TYPE || DECODE(DATA_TYPE,''ROWID'','''',''DATE'','''',NULLIF(''('' || DECODE(DATA_TYPE,''NUMBER'',DATA_PRECISION||'',''||DATA_SCALE,DATA_LENGTH) || '')'',''(,)''))  AS COLS
          FROM ALL_TAB_COLS a WHERE OWNER=USER AND TABLE_NAME=''AQ_EVENT_TABLE''';
 end;
 /

But,when I try to add implicit cursor then I got this error: 

[Error] Execution (1: 2): ORA-00932: inconsistent datatypes: expected - got -
ORA-06512: at line 4

My code is:

 declare
 vSQLSlctString varchar2(255);
 begin
  EXECUTE IMMEDIATE 'SELECT  COLUMN_ID,
        COLUMN_NAME || '' '' || DATA_TYPE || DECODE(DATA_TYPE,''ROWID'','''',''DATE'','''',NULLIF(''('' || DECODE(DATA_TYPE,''NUMBER'',DATA_PRECISION||'',''||DATA_SCALE,DATA_LENGTH) || '')'',''(,)''))  AS COLS
          FROM ALL_TAB_COLS a WHERE OWNER=USER AND TABLE_NAME=''AQ_EVENT_TABLE''' INTO vSQLSlctString;
 end;
 /

Upvotes: 0

Views: 3920

Answers (1)

Popeye
Popeye

Reputation: 35930

Issue: There is multiple columns in selecet query but into clause has only one variable to hold them.

Resolution: Number and datatype* of the columns in the select clause must match with number and datatype of the variables in the into clause.

I am considering that your query will return single record as noraml INTO will throw error if the select query returns more than 1 record.

If you have query that is returning multiple records then you must have to use bulk collect into and some udt.

Please use following block.

declare
 vcolid number;
 vcoldesc varchar2(4000);
 begin
  EXECUTE IMMEDIATE 'SELECT  COLUMN_ID,
        COLUMN_NAME || '' '' || DATA_TYPE || DECODE(DATA_TYPE,''ROWID'','''',''DATE'','''',NULLIF(''('' || DECODE(DATA_TYPE,''NUMBER'',DATA_PRECISION||'',''||DATA_SCALE,DATA_LENGTH) || '')'',''(,)''))  AS COLS
          FROM ALL_TAB_COLS a WHERE OWNER=USER AND TABLE_NAME=''AQ_EVENT_TABLE''' INTO vcolid, vcoldesc;
 end;
 /

Now, you can use vcolid and vcoldesc in any other logic after that execute immediate.

Cheers!!

Upvotes: 1

Related Questions