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) )
You should place parenthesis around the selects: coalesce( (SELECT ID FROM Stmt G WHERE G.CLAIMNO=C.CLNUMBER) , (select StmtSeq.nextval from dual) )
Increase the linesize, e.g SET LINESIZE 32000 or use SET WRAP OFF (but this will truncate long values)
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
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
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
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
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; … }
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’);
If you want to list last 3 chars, simplest way is select substr(‘123456’,-3) from dual;
The CONTINUE statement is a new feature in 11g. Here is a related question: ‘CONTINUE’ keyword in Oracle 10g PL/SQL