Using SELECT inside COALESCE
You should place parenthesis around the selects: coalesce( (SELECT ID FROM Stmt G WHERE G.CLAIMNO=C.CLNUMBER) , (select StmtSeq.nextval from dual) )
plsql
Ugly formatting in SQL*Plus
Increase the linesize, e.g SET LINESIZE 32000 or use SET WRAP OFF (but this will truncate long values)
What are some online compilers to compile and run PL/SQL? [closed]
Update 2024-01-17: SQL Fiddle no longer works for Oracle and seems to have lost many old fiddles. See the other answer for a more up-to-date list. SQL Fiddle Here is a trivial example of use: http://sqlfiddle.com/#!4/8cf15/5
What is the point of dbms_lob.createtemporary()?
In earlier times you were always forced to use DBMS_LOB package. Then in Oracle 10g a feature called “SQL semantics for LOB” was introduced and now you can use the simplified syntax. In CREATETEMPORARY you can specify the duration of the LOB in SQL semantic you cannot. In your case the LOB is freed in … Read more
When should database synonyms be used?
It is excellent for staging mock tables when testing. For example, if your source tables contain millions of records and you want to test a small subset of data, you can use synonyms to re-direct the source table to a smaller table you control so you can stage various scenarios. In this way, you can … Read more
UTL_FILE.FOPEN() procedure not accepting path for directory?
Since Oracle 9i there are two ways or declaring a directory for use with UTL_FILE. The older way is to set the INIT.ORA parameter UTL_FILE_DIR. We have to restart the database for a change to take affect. The value can like any other PATH variable; it accepts wildcards. Using this approach means passing the directory … Read more
Oracle “ORA-01008: not all variables bound” Error w/ Parameters
The ODP.Net provider from oracle uses bind by position as default. To change the behavior to bind by name. Set property BindByName to true. Than you can dismiss the double definition of parameters. using(OracleCommand cmd = con.CreateCommand()) { … cmd.BindByName = true; … }
How do I print output in new line in PL/SQL?
You can concatenate the CR and LF: chr(13)||chr(10) (on windows) or just: chr(10) (otherwise) dbms_output.put_line(‘Hi,’||chr(13)||chr(10) ||’good’ || chr(13)||chr(10)|| ‘morning’ ||chr(13)||chr(10) || ‘friends’);
get string from right hand side
If you want to list last 3 chars, simplest way is select substr(‘123456’,-3) from dual;
Is it possible to CONTINUE a loop from an exception?
The CONTINUE statement is a new feature in 11g. Here is a related question: ‘CONTINUE’ keyword in Oracle 10g PL/SQL