Scratch And PLSQL

My daughter showed me a site called Scratch she is using to learn coding. Its at https://scratch.mit.edu/

I decided to see if it could be used to teach PL/SQL as well.

Actually I was just having fun and decided to make slightly related to work…

Move PL the monkey from left to right to catch the correct PLSQL syntax. Press the green flag to start.

Enjoy

DML In A Select

It has long been a sort-of golden rule of Oracle database programming that Data Manipulation (Insert/Update/Delete) cannot be done by a SELECT statement.

Note that I am not referring to DML statements that include a query to determine rows to process.

Oracle Table Functions combined with Autonomous Transactions allow you to violate this rule as shown in this example.

SQL> CREATE TABLE demo
 2   ( col NUMBER );
Table created.

SQL> INSERT INTO demo
 2   VALUES(1);
1 row created.

SQL> INSERT INTO demo
 2   VALUES(2);
1 row created.

SQL> INSERT INTO demo
 2   VALUES(3);
1 row created.

SQL> COMMIT;
Commit complete.

SQL> CREATE TYPE v_n_t AS TABLE OF NUMBER;
 2   /
Type created.

SQL> CREATE OR REPLACE FUNCTION delete_demo
 2   RETURN v_n_t AS
 3     PRAGMA AUTONOMOUS_TRANSACTION;
 4     v_ret_val v_n_t := v_n_t();
 5   BEGIN
 6     v_ret_val.EXTEND;
 7     -- delete all records in the table and return the
 8     -- the count of deleted rows
 9     DELETE demo;
 10    v_ret_val(1) := SQL%ROWCOUNT;
 11    COMMIT;
 12    RETURN(v_ret_val);
 13 END;
 14 /
Function created.

SQL> -- Now SELECT from the TABLE FUNCTION
SQL> -- The rows will magically disappear
SQL> SELECT *
 2     FROM TABLE(delete_demo);
COLUMN_VALUE
------------
           3

SQL> SELECT *
 2     FROM demo;
no rows selected

They records were deleted when the function ran. The downside of this there is no ROLLBACK because the function was declared as AUTONOMOUS. But there is an upside to this as well. I’ll explain that in a future blog post.

Thanks for reading!

EBR – Fix The Old Data

EBR is great for implementing application and data structures upgraded with no downtime. But what about the data itself? There are two main areas where data requires fixing:

New Data From The Old Edition

This is the data created in the old edition (ORA$BASE in these blog entries) that is not aware of the data structure and code changes in the new edition (NEW_COL in these blog entries). The code running in ORA$BASE is happily creating records in the DEMO table but it is not putting anything the the NEW_COL column because it is not even aware it exists.

Old Data From The Old Edition

This is the data created before the new edition was even added. This blog entry shows how to handle that.

Fixing The Old Data

The data that was created before the new edition (NEW_COL) was even introduced needs to be updated to be synchronized with new records arriving. This ensures all records will have data in the newly added column. The simplest way to do this is with DBMS_SQL using the APPLY_CROSSEDITION_TRIGGER parameter as shown below.

Remember the cross edition trigger we defined fires for updates.

We’ll run this in the ORA$BASE edition.

SQL> DECLARE
 2     v_curs NUMBER;
 3     v_ret_val NUMBER;
 4   BEGIN
 5     v_curs := DBMS_SQL.OPEN_CURSOR;
 6     DBMS_SQL.PARSE( c => v_curs,
 7                     language_flag => DBMS_SQL.NATIVE,
 8                     statement => 'UPDATE demo SET ts = ts where new_col is null',
 9                     apply_crossedition_trigger => 'set_new_col' );
 10    v_ret_val := DBMS_SQL.EXECUTE(v_curs);
 11    DBMS_OUTPUT.PUT_LINE('Updated ' || v_ret_val || ' Rows');
 12    DBMS_SQL.CLOSE_CURSOR(v_curs);
 13  END;
 14 /
Updated 3572 Rows
PL/SQL procedure successfully completed.

SQL> COMMIT;
Commit complete.
SQL> SELECT COUNT(*)
 2     FROM demo
 3    WHERE new_col IS NULL;
COUNT(*)
----------
         0

The APPLY_CROSSEDITION_TRIGGER parameter made DBMS_SQL fire the cross edition trigger (SET_NEW_COL) for every record returned by the SQL statement provided. This gets the old data in sync with the new!

Thanks for reading.