99.9% of the time the error ORA-65096: invalid common user or role name means you are logged into the CDB when you should be logged into a PDB. For example, if you used the default 19c installation settings, you should login to ORCLPDB (the PDB) instead of ORCL (the CDB). DANGER – If you insist … Read more
Whilst you can use the old “select 1 from dual” trick, the downside with this is that it issues an extra query each and every time you borrow a connection from the pool. For high volumes, this is wasteful. JBoss provides a special connection validator which should be used for Oracle: <valid-connection-checker-class-name> org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker </valid-connection-checker-class-name> This … Read more
You can do this: select * from user_source where upper(text) like upper(‘%SOMETEXT%’); Alternatively, SQL Developer has a built-in report to do this under: View > Reports > Data Dictionary Reports > PLSQL > Search Source Code The 11G docs for USER_SOURCE are here
I think you should not rely on the implicit conversion. It is a bad practice. Instead you should try like this: datenum >= to_date(’11/26/2013′,’mm/dd/yyyy’) or like datenum >= date ‘2013-09-01’
SELECT INTO DECLARE the_variable NUMBER; BEGIN SELECT my_column INTO the_variable FROM my_table; END; Make sure that the query only returns a single row: By default, a SELECT INTO statement must return only one row. Otherwise, PL/SQL raises the predefined exception TOO_MANY_ROWS and the values of the variables in the INTO clause are undefined. Make sure … Read more
I’m able to run an SQL query by piping it to SQL*Plus: @echo select count(*) from table; | sqlplus username/password@database Give @echo execute some_procedure | sqlplus username/password@databasename a try.
The difference is- A function must return a value (of any type) by default definition of it, whereas in case of a procedure you need to use parameters like OUT or IN OUT parameters to get the results. You can use a function in a normal SQL where as you cannot use a procedure in … Read more
You can use the SYS_GUID() function to generate a GUID in your insert statement: insert into mytable (guid_col, data) values (sys_guid(), ‘xxx’); The preferred datatype for storing GUIDs is RAW(16). As Gopinath answer: select sys_guid() from dual union all select sys_guid() from dual union all select sys_guid() from dual You get 88FDC68C75DDF955E040449808B55601 88FDC68C75DEF955E040449808B55601 88FDC68C75DFF955E040449808B55601 As … Read more
Precision 4, scale 2: 99.99 Precision 10, scale 0: 9999999999 Precision 8, scale 3: 99999.999 Precision 5, scale -3: 99999000
I believe it’s the ANSI standard. EDIT: Actually, I think it’s the SQL-92 standard. A later version of the standard appears to optionally allow for 128 character names, but Oracle doesn’t yet support this (or has partial support for it, insofar as it allows 30 characters. Hmmm.) Search for “F391, Long identifiers” on this page… … Read more