I love that Oracle has these very specific syntax diagrams for SQL and PL/SQL constructs but the PL/SQL compiler isn't written with the same level of strictness.

Perfect example is the JSON_OBJECTAGG SQL function documented here: https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/JSON_OBJECTAGG.html#GUID-09422D4A-936C-4D38-9991-C64101283D98

You can see that the "KEY" keyword is optional but "VALUE" is not. Except the compiler also accepts "IS" instead of "VALUE" and I literally cannot find a single place that this is documented (and also how on earth did the person who wrote the code figure it out?).

Also interesting to note that the SQLDeveloper IDE's local syntax checking marks it as bad, but once you send it off to the compiler on an actual Oracle instance, the error goes away. ¯\_(ツ)_/¯

#Oracle #plsql #OracleSQL

It may be common knowledge but my mind was blown. :amaze:

We're using CentOS and my DBAD (Database Admin) instructor hinted at the option to make user input variables in some of the new dictionary views we learned this module and using WHERE name = UPPER('&NAME') in the select statement.

I had no idea you could prompt for user input... I was in the middle of making 3 profiles, 9 roles,
16 users, granting them roles and/or privileges as I went, editing a text file in gedit with the new user data and pasting it into the terminal.

It may not actually be faster but it feels faster (I may be biased because I love database). The &UNLIMITED_QUOTA_TABLESPACE variable name is not the best and very long, use &QUOTA_TABLESPACE or something shorter, and the size can be changed to &SIZE_APPEND_M so you can enter 20M but we were using unlimited for the lab.

#CentOS #Oracle #SQL #DBA #OracleSQL #Database #DBAdmin #DBAD #CentOS5 #Oracle12c