Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

FORALL and EXECUTE IMMEDIATE to insert records

vpolasaDec 13 2024

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

This post has been answered by James Su on Dec 13 2024
Jump to Answer
Comments
Post Details
Added on Dec 13 2024
8 comments
675 views