Creating tables in PL/SQL

table, not an instance of the PL/SQL data structure confusingly called a "table," which resembles an array in other programming languages.

EXECUTE IMMEDIATE can be used to issue any SQL statement (except SELECT) from PL/SQL. See the previous question on Dynamic SQL for details, including an example of CREATE TABLE.

Saving changes in PL/SQL is exactly like saving changes in SQL*Plus. Only Data Manipulation Language (DML) statements (that is, INSERT, UPDATE and DELETE) have a pending (or uncommitted) state. The results of a DML statement are available only to the session that made the statement until it issues a COMMIT statement.

That gives the issuing session a chance to ROLLBACK partially complete transactions in an all-or-nothing situation. For example:

CREATE OR REPLACE FUNCTION  transfer
(
    in_amt      NUMBER,
    in_from_id  NUMBER,
    in_to_id    NUMBER
)
RETURN  BOOLEAN
IS
--    ***********************
--    **  t r a n s f e r  **
--    ***********************

--  transfer deducts in_amt from the account identified by in_from_id
--  and deposits it in the account identified by in_to_id.  If there
--  is any error (including insufficient funds in in_from_id) no changes
--  are made to either account and the function returns FALSE.  Only
--  if both UPDATEs succeed does transfer return TRUE.

    new_from_amt  account.total_amt%TYPE;
    return_val    BOOLEAN := FALSE;  -- Default value, may change later
BEGIN
    IF  in_amt > 0    -- positive amounts only
    THEN
        SELECT  total_amt - in_amt
        INTO    new_from_amt
        FROM    account
        WHERE   account_id = in_from_id;

        IF  new_from_amt >= 0
        THEN    -- sufficient funds
            UPDATE  account          -- withdraw from in_from_id
            SET     total_amt  = new_from_amt
            WHERE   account_id = in_from_id;

            UPDATE  account          -- deposit to in_to_id
            SET     total_amt  = total_amt + in_amt
            WHERE   account_id = in_to_id;

            COMMIT;
            return_val := TRUE;
        END IF;
    END IF;

    RETURN  return_val;
EXCEPTION
    WHEN OTHERS     -- If there's any error at any point ...
    THEN            --    ... then ...
        ROLLBACK;   --    ... none of the transaction succeeds
        RETURN  FALSE;
END;

All Data Definition Language (DDL) statements (including CREATE TABLE) take effect immediately. You do not have to COMMIT them for other sessions to see the results, and you can not roll them back. (In fact, issuing a DDL statement COMMITs pending DML statements in the same session, if there are any.)