I have a source table I would like to copy data into another table. As part of my code, I would like to create the table and insert using FORALL. But I'm seeing below error. How can I address this? (I have to create the table in the block and use dynamic statement to insert records).
CREATE TABLE TEST_SRC (COL1 NUMBER, COL2 NUMBER);
BEGIN
FOR i IN 1 .. 10 LOOP
INSERT INTO test_src (col1, col2) VALUES (i, i);
END LOOP;
COMMIT;
END;
/
DECLARE
TYPE test_src_type IS TABLE OF test_src%rowtype INDEX BY BINARY_INTEGER;
test_src_object test_src_type;
fetch_size NUMBER := 2;
CURSOR test_src_cursor IS
SELECT * FROM test_src;
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE test_tgt AS SELECT 1 FROM test_src WHERE 1 = 2';
OPEN test_src_cursor;
LOOP
FETCH test_src_cursor BULK COLLECT
INTO test_src_object LIMIT fetch_size;
EXIT WHEN test_src_cursor%notfound;
FORALL I IN INDICES OF test_src_object SAVE EXCEPTIONS
EXECUTE IMMEDIATE 'INSERT INTO test_tgt VALUES (:1) ' USING test_src_object(I);
COMMIT;
END LOOP;
CLOSE test_src_cursor;
END;
/
Error:
Error report -
ORA-06550: line 18, column 71:
PLS-00457: expressions have to be of SQL types
ORA-06550: line 18, column 11:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
I'm working on :
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Windows 11 Enterprise