Execute PL/SQL with oracledb won't take affect

Question

given the below PL/SQL statement:

BEGIN
    BEGIN
        EXECUTE IMMEDIATE 'DROP TABLE special';
    EXCEPTION
    WHEN OTHERS THEN
        IF SQLCODE != -942 THEN
            RAISE;
        END IF;
    END;
    EXECUTE IMMEDIATE 'CREATE TABLE special(
        id int DEFAULT NULL,
        col1 varchar(255) DEFAULT NULL,
        " Name " varchar(255) DEFAULT NULL,
        specchar varchar(50) DEFAULT NULL
    )';
    EXECUTE IMMEDIATE 'INSERT INTO ADMIN.special VALUES (1, ''Customer Name'', ''MOL'', ''normal'')';
    EXECUTE IMMEDIATE 'INSERT INTO ADMIN.special VALUES (2, ''Customer Name'', ''INA'', ''[blockq] text'')';
    EXECUTE IMMEDIATE 'INSERT INTO ADMIN.special VALUES (3, ''Customer Name'', ''LUKOIL'', ''"quoted" text1'')';
    EXECUTE IMMEDIATE 'INSERT INTO ADMIN.special VALUES (4, ''Customer Code'', ''ABC'', ''questionmark ?'')';
    EXECUTE IMMEDIATE 'INSERT INTO ADMIN.special VALUES (5, ''Customer Code'', ''CDE'', ''back\slash'')';
    EXECUTE IMMEDIATE 'INSERT INTO ADMIN.special VALUES (6, ''Customer Code'', ''FGH'', ''hej   haj'')';
    EXECUTE IMMEDIATE 'INSERT INTO ADMIN.special VALUES (100, ''Customer Code'', ''F\x00apple'', ''hej  haj'')';
    EXECUTE IMMEDIATE 'INSERT INTO ADMIN.special VALUES (101, ''Customer Code'', ''F\x7Fapple'', ''hej  haj'')';
    EXECUTE IMMEDIATE 'INSERT INTO ADMIN.special VALUES (102, ''Customer Code'', ''F\rapp\nle'', ''hej  ha'')';
END;

This should re-create and populate a simple test table in OCI (I suppose, behave same as on-premise PL/SQL database)

The below code read the statement from a file and try to execute it:

    with oracledb.connect(user=auth["username"], password=auth["password"], dsn=dsn) as conn:
        with conn.cursor() as cursor:
            cursor.execute(file.read())

After executing this statement and when I go to the OCI database console (browser), a select statement from this table won't result any rows.

Issuing this query from the web console will populate the table.

Can anybody help me, what did I wrong?

Answer 1

You will need to issue a commit after inserting the records. Try adding conn.commit() after cursor.execute