PLSQL Error PLS-00457: expressions have to be of SQL types

Question

I am really unable to figure out why I am unable to make the below code work.

I tried to replicate the scenario explained in the below answer

Trying to use a FORALL to insert data dynamically to a table specified to the procedure

CREATE TABLE VISION.TEMP_TEST_TABLE
(
  A  NUMBER(10),
  B  NUMBER(10)
)

CREATE OR REPLACE PROCEDURE VISION.PR_TEST_FORALL AUTHID CURRENT_USER Is

v_SQL1 varchar2(1000) := 'select rownum, rownum from dual connect by rownum <= 11000';
v_SQL varchar2(1000) := 'INSERT /*+ APPEND */ INTO TEMP_TEST_TABLE VALUES :1';

TYPE generic_Looper_CurType IS REF CURSOR;
generic_Looper_Cursor    generic_Looper_CurType;
        
TYPE TEST_FS_ARRAY_TYPE IS TABLE OF VISION.TEMP_TEST_TABLE%ROWTYPE INDEX BY BINARY_INTEGER;
TEST_FS_ARRAY_OBJ TEST_FS_ARRAY_TYPE;
        
FETCH_SIZE NUMBER := 10000;
BEGIN

open generic_Looper_Cursor for v_SQL1;

  loop
    FETCH generic_Looper_Cursor BULK COLLECT
      INTO TEST_FS_ARRAY_OBJ LIMIT fetch_size;
      
    execute immediate
    'insert into TEMP_TEST_TABLE select * from table(:TEST_FS_ARRAY_OBJ)'
    using TEST_FS_ARRAY_OBJ;
    commit;
      
    COMMIT;
    
   EXIT WHEN generic_Looper_Cursor%NOTFOUND;
   
  END LOOP;
  
  
End;
/


[Warning] ORA-24344: success with compilation error
23/11   PLS-00457: expressions have to be of SQL types
21/5    PL/SQL: Statement ignored
 (2: 0): Warning: compiled but with compilation errors

score 2 · Accepted Answer · answered Dec 04 '22 at 22:49

As the error message says, SQL cannot recognise a privately defined type. It needs to be defined publicly in order for SQL to recognise it.

Simplified test case (fails with "PLS-00457: expressions have to be of SQL types"):

declare
    type tt is table of varchar2(1);

    input tt := tt('X','Y','Z');
    output tt;
begin
    execute immediate 'select * from table(:b)'
        bulk collect into output
        using input;
    
    dbms_output.put_line(output.count||' row(s) selected'); 
end;

Fixed version using a public type:

create or replace type varchar2_tt as table of varchar2(50)

declare
    input varchar2_tt := varchar2_tt('X','Y','Z');
    output varchar2_tt;
begin
    execute immediate 'select * from table(:b)'
        bulk collect into output
        using input;
    
    dbms_output.put_line(output.count||' row(s) selected'); 
end;

score 0 · Answer 2 · answered Dec 04 '22 at 22:22

There are two problems with this line of code:

execute immediate
'insert into TEMP_TEST_TABLE select * from table(:TEST_FS_ARRAY_OBJ)'
using TEST_FS_ARRAY_OBJ;

You cannot pass table name as bind variable.
You cannot use locally defined nested table in SQL statement. It has to be defined in schema level to use in SQL statement.

PLSQL Error PLS-00457: expressions have to be of SQL types

2 Answers2