How to create an Oracle Procedure which includes a Subquery for an SQL Insert Into clause?

I have a problem to declare an Database Procedure, which includes an SQL Insert Into Statement with a subquery. I use an Oracle database. I get an errormessage, that tells me the From keyword was not found where expected (ORA-00923). Could somebody explain me, how I create this syntax error in the following example ?

DECLARE

    PROCEDURE myProcedure(schema in varchar2) AS
        BEGIN
            EXECUTE IMMEDIATE 'INSERT INTO ' || schema || '.myTable(column_1, column_2, column_3)
            (
              SELECT extern_column_1,
                     extern_column_2,
                     extern_column 3
              FROM ' || schema || '.myExternTable
            )';
        END;

BEGINN

    FOR S IN (SELECT * FROM ROOT_SCHEMA.myTableWithSchema)
      LOOP
        myProcedure(S.mySchemata);
      END LOOP
      COMMIT;

END;
/

Is there a problem with the sign ‘ in this code?. Thanks for helping me !

Answer

Remove the opening and closing braces around the select and use the below

         PROCEDURE myProcedure(p_schema in varchar2) AS
            BEGIN
                EXECUTE IMMEDIATE 'INSERT INTO ' || p_schema || '.myTable(column_1, column_2, column_3)
                  SELECT extern_column_1,
                         extern_column_2,
                         extern_column 3
                  FROM ' || p_schema ||'.myExternTable
                ';
            END;