While trying to create user in Oracle Database 18c Express Edition I kept getting an “ORA-65096: invalid common user or role name” error, which didn’t make sense to me so after validating my command, that I was signed in as an admin user, and determining that my “CREATE USER” was formatted correctly. I did some additional research and determined that in the hidden parameter “_ORACLE_SCRIPT” needed to be set to “True” starting with the Oracle Version 12c and higher.
the “_ORACLE_SCRIPT” values
To set the “_ORACLE_SCRIPT” hidden variable to “True” you need to run an “Alter” command. Then you will be able to create the desired user and run your grants commands as usual.
Oracle doesn’t recognize the ’Limit’ command, however, there is any easy way to achieve the same result. Using the Oracle/function in the ‘where’ clause.
ROWNUM Pseudo Column Description
The Oracle/PLSQL ROWNUM pseudo column returns a number that represents the order that a row is selected by Oracle from a table or joined tables. The first row has a ROWNUM of 1, the second has a ROWNUM of 2, and so on.
Basic RUWNUM Syntax
WHERE ROWNUM <= number
Order by column_name(s);
Example SQL Limiting to One row
The one row limit has two accurate way to get the desired result:
· SQL Limiting to One row using equal to
where rownum = 1;
· SQL Limiting to One row using less than or equal to