Since 10g Oracle doesn’t immediately drop tables when we issue a DROP TABLE statement. Instead it renames them like this BIN$IN1vjtqhTEKcWfn9PshHYg==$0 and puts them in the recycle bin. This allows us to recover tables we didn’t mean to drop. Find out more. Tables in the recycle bin are still tables, so they show up in … Read more
The same effect can be replicated in Oracle either by using the first_value() function or by using one of the rank() or row_number() functions. Both variants also work in Postgres. first_value() select distinct col1, first_value(col2) over (partition by col1 order by col2 asc) from tmp first_value gives the first value for the partition, but repeats … Read more
The key word here is FIRST. You can use analytic function FIRST_VALUE or aggregate construct FIRST. For FIRST or LAST the performance is never worse and frequently better than the equivalent FIRST_VALUE or LAST_VALUE construct because we don’t have a superfluous window sort and as a consequence a lower execution cost: select table_A.id, table_A.name, firstFromB.city … Read more
You should place parenthesis around the selects: coalesce( (SELECT ID FROM Stmt G WHERE G.CLAIMNO=C.CLNUMBER) , (select StmtSeq.nextval from dual) )
You could do it in two ways. Method 1 Using LPAD. For example, SQL> WITH DATA(num) AS( 2 SELECT 540 FROM dual UNION ALL 3 SELECT 60 FROM dual UNION ALL 4 SELECT 2 FROM dual 5 ) 6 SELECT num, lpad(num, 5, ‘0’) num_pad FROM DATA; NUM NUM_P ———- —– 540 00540 60 00060 … Read more
12cR2 (available in the Oracle Cloud) supports this natively. SQL> select JSON_ARRAY(EMPLOYEE_ID, FIRST_NAME,LAST_NAME) from HR.EMPLOYEES; JSON_ARRAY(EMPLOYEE_ID,FIRST_NAME,LAST_NAME) ——————————————————————————– [100,”Steven”,”King”] [101,”Neena”,”Kochhar”] or SQL> select JSON_OBJECT(‘ID’ is EMPLOYEE_ID , ‘FirstName’ is FIRST_NAME,’LastName’ is LAST_NAME) from HR.EMPLOYEES; JSON_OBJECT(‘ID’ISEMPLOYEE_ID,’FIRSTNAME’ISFIRST_NAME,’LASTNAME’ISLAST_NAME) —————————————————————————- {“ID”:100,”FirstName”:”Steven”,”LastName”:”King”} {“ID”:101,”FirstName”:”Neena”,”LastName”:”Kochhar”}
Increase the linesize, e.g SET LINESIZE 32000 or use SET WRAP OFF (but this will truncate long values)
Have you tried the solutions presented here: Convert JS date time to MySQL datetime The title should be called “Convert JS date to SQL DateTime” I happened to need to do the same thing as you just now and I ran across this after your question. This is from the other post by Gajus Kuizinas … Read more
What would be useful here would be a LIKE ANY predicate as is available in PostgreSQL SELECT * FROM tbl WHERE my_col LIKE ANY (ARRAY[‘%val1%’, ‘%val2%’, ‘%val3%’, …]) Unfortunately, that syntax is not available in Oracle. You can expand the quantified comparison predicate using OR, however: SELECT * FROM tbl WHERE my_col LIKE ‘%val1%’ OR … Read more
I never installed Oracle on my system and don’t know why these directories kept being created, but I finally managed to stop them from reappearing. Here’s how it’s done. First, head over to: cd ~/oradiag_<username>/diag/clients/user_<username>/host_*/trace/ and then head sqlnet.log You should see an error message complaining about a directory not existing for r/w. For me, … Read more