How to correctly make a procedure in Pl/SQL in which I create a TABLE and use a CURSOR

Question

The assignment I am trying to do is "Create a procedure that places the names of all presidents who were born in one specific state, in a temporary table. Display the contents of this table."

The procedure complies but when I try to invoke it, it gives me:

942. 00000 - "table or view does not exist" Error(8,5): PLS-00103: Encountered the symbol "CREATE" when expecting one of the following: begin function pragma procedure subtype type current cursor delete exists prior

I have been stuck for a while now. Does anybody know what I am doing wrong?

My code so far is:

CREATE OR REPLACE PROCEDURE stateofpresident(p_state president.state_born%TYPE)
AS

    CURSOR c_state IS
    SELECT *
    FROM president;
                    
BEGIN
            execute immediate 'CREATE TABLE presidentFromState;
            (
                president_name VARCHAR2
            )';
            FOR r_state IN c_state LOOP
                IF(p_state = r_state.state_born) THEN
                    execute immediate 'INSERT INTO presidentFromState VALUES(r_state.pres_name)';
                    commit;
                END IF;
            END LOOP; 
            execute immediate 'DROP TABLE presidentFromState';              
END stateofpresident;
/
SET SERVEROUT ON
BEGIN
    stateofpresident('VIRGINIA');
END;
/
SELECT *
FROM presidentFromState;

Answer 1

The immediate cause of your error is the semi-colon (;) at "presidentFromState;" At run fhat terminates the statement and the SQL interpreter at that point does not know what is want, the create syntax is invalid. The statement compiles because at compile time it is a properly formatted string. That is why dynamic SQL should be avoid if at all possible. Your script also has an additional error. Your last select will fail as the table presidentFromState ws not only created but also dropped in the procedure. Finally, just an FYI, the entire FOR cursor and the cursor itself is entirely unnecessary, the entire operation can be completed is one statement: Look into the structure

Insert into table_name(columns)
   Select columns ... 

Since this obviously an assignment or tutorial I'll leave the exact for your research.