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) )
oracle-sqldeveloper
Oracle SQLDeveloper Table Name autocompletion setting
Though this post is old but still I see many people not able to get real Auto Complete intellisense feature because as soon as Auto Complete values exceeds 10 then autocomplete doesn’t work i.e you need to explicitly use CTRL+SPACE for displaying intellisense in that case. Please follow the steps so that you will be … Read more
ORA-01799: a column may not be outer-joined to a subquery
you can rewrite that by pushing the sub query so that its not outer joined: select Count(C.setid) from mycustomer C left outer join (select * from mycustoption co where co.effdt <= (select Max(COI.effdt) from mycustoption COI where COI.setid = co.setid and COI.cust_id = co.cust_id and COI.effdt <= sysdate)) co on ( C.setid = CO.setid and … Read more
Recover unsaved SQL query Scripts in Oracle SQL Developer
This has saved my butt several times. It is really a problem when you lose unsaved code. For about a month I have been working on a big procedure, and forgot to save the code in SVN. If you read this and remember have such unsaved code, commit it immediately! 🙂 Because everything could happen … Read more
Oracle SqlDeveloper JDK path
The message seems to be out of date. In version 4 that setting exists in two files, and you need to change it in the other one, which is: %APPDATA%\sqldeveloper\1.0.0.0.0\product.conf Which you might need to expand to your actual APPDATA, which will be something like C:\Users\cprasad\AppData\Roaming. In that file you will see the SetJavaHome is … Read more
How to change settings for SQL Developer to correctly recognize current version of SDK
In windows 10, I needed to go to following folder and change following product.conf file and set SetJavaHome directive. %APPDATA%\sqldeveloper\<product-version>\product.conf in my machine: C:\Users\ati_o\AppData\Roaming\sqldeveloper\17.4.0\product.conf with following line. SetJavaHome C:\Tools\oraclejdk8 Thanks to comment from @thatjeffsmith, in MacOS or Linux/Unix, go to: $HOME/.sqldeveloper/<product-version>/product.conf to set same SetJavaHome directive.
How do I declare and use variables in PL/SQL like I do in T-SQL?
Revised Answer If you’re not calling this code from another program, an option is to skip PL/SQL and do it strictly in SQL using bind variables: var myname varchar2(20); exec :myname := ‘Tom’; SELECT * FROM Customers WHERE Name = :myname; In many tools (such as Toad and SQL Developer), omitting the var and exec … Read more
In Oracle SQL Devleoper, when I copy the results, how can I copy the column headings too?
I found the answer on https://forums.oracle.com/forums/thread.jspa?threadID=2162606. Once you have selected the results (Ctrl +A) then use Ctrl + Shift + C to copy, this will copy the headers aswell. Regards
How to Query Database Name in Oracle SQL Developer?
Once I realized I was running an Oracle database, not MySQL, I found the answer select * from v$database; or select ora_database_name from dual; Try both. Credit and source goes to: http://www.perlmonks.org/?node_id=520376.
Oracle SQLDeveloper on MacOS won’t open after installation of correct Java
The post above is right: SQLDeveloper only runs under Java 8. This is counter-intuitive as other programs runs under at least X version of a software (not only X version). I kept downloading Java 11 with no luck. After 1 failed attempt a month ago and two hours of searching today, I found this easy … Read more