We have Oracle 11g so the following query didn’t work. select USERNAME,LAST_LOGIN from dba_users Then I searched and people suggested multiple ways and i got confused. What is the correct way to get …
Tag: oracle
How to insert from old table to new table using trigger in pl/sql
There is a column which calls as text (column’s name) inside a table. I want to create different columns from this column. How to insert to another table these columns using trigger. I tried few …
groupby to check each of their date in oracle sql
I have a table ISIN_TBL which has two fields IDENTIFIER and ALLT_DATE which are of datatype varchar and Date respectively. There are many ALLT_DATE for each IDENTIFIER in the table. Normally for each IDENTIFIER the ALLT_DATE should be the same. But just to confirm i want to write a sql query to list IDENTIFIE…
Group by expression with case statement in oracle?
I have a query with case statement in it. This query gives me error ORA-00979: not a GROUP BY expression When I remove my last case statement from select then it runs fine. Please help me in this Answer Columns that aren’t aggregated should be part of the GROUP BY clause. It means that “solution&#…
Use select inside ‘filename’ property of utl_file.fopen
I have a procedure that extracts data from a view and stores it on disc in CSV format. I’d like a name of the file to contain the most recent timestamp from table MY_TIMESTAMPS. For now it looks like this: The part with (select to_char(max(EVENT_TS)) from MY_TIMESTAMPS) throws an error. How can I set th…
Count the number of times word appears in a single column
I’m attempting to count the number of times apples and oranges appear in my fruit column. The table looks like this: Expected output: My code thus far. I’m not sure how to do it when both appear and how to add them to the totals. I’m sure there is an easier way that this. Cheers Answer If th…
Oracle | Update Long type | Error: Illegal use of LONG datatype
How to rectify issue on UPDATE? Following is related data and error Following is table structure: Not able to find answers in related questions. Not sure whether i need to modify table datatype but when i try to modify it says remove data first. Answer if ID is Primary Key – it should not be LONG. The L…
default rows from select query
I have a select query (Oracle database) with group by clause that needs to return 4 rows if all required data exist. It works perfectly. How can I write a select query that returns same 4 rows even if …
Adding column based on partition by – Oracle
Table: Col1 Col2 Col3 43 1234 abc 42 1234 abc 41 1234 abc 35 1234 abc 34 5678 def Objective is to create …
Promote a clerk if his salary is greater than 1000 and update his salary by 10%
Write a Parameter Cursor program that promotes CLERK who earn more than 1000 to SR CLERK and increase the salary by 10%. Pass CLERK as a parameter to the Cursor. Use cursor with Update Clause. My code: My code does the work but I don’t find it correct so can someone suggest an alternative? Answer You ca…