CASE in sub query with SELECT

This is the query that I've written SELECT id, name, (SELECT is_enable FROM customers WHERE id=table_one.id) AS some_tag FROM table_one; The above query returns the date like this ID NAME SOME_TAG 4 name 1 ...
more »

2017-08-17 22:08 (2) Answers

Select query by filtering multiple rows

I have a table like this: col | status ----+------- 1 | 0 1 | 1 2 | 1 2 | 2 3 | 1 3 | 0 I want to select only the row with having max status value. But also want to ignore if there is any 0 in status. So expected rows would be as...
more »

2017-08-17 14:08 (2) Answers

Deleting multiple rows in oracle

DELETE FROM MYTABLE WHERE ID = 1 and NAME ='xyz'; DELETE FROM MYTABLE WHERE ID = 2 and NAME ='abc'; DELETE FROM MYTABLE WHERE ID = 3 and NAME ='abc'; I have multiple delete statements mentioned above. How can I delete them in less statements. Will ...
more »

2017-08-17 14:08 (3) Answers

Oracle audit logs

We are sending Oracle 12c audit logs to OS files but its generating huge amount of logs. How can we decrease generation of audit logs? These are the audit log settings: NAME TYPE VALUE ------------------------...
more »

2017-08-17 13:08 (1) Answers

Committing every x rows in table insert

I'm writing a huge number of rows (~20 million) to a table from a JDBC connection to a static Oracle 12c database. I want to commit every 10,000 rows to prevent rollback and so that I can effectively restart the insert command from the last commit if...
more »

2017-08-17 12:08 (0) Answers

Rename not possible using procedure

--Using Sql developer Create or replace procedure rename_table is begin rename emp to emp1; end; Compiled with error: Error(7,1): PLS-00103: Encountered the symbol "emp" when expecting one of the following: . ( * @ % & = - + ; < / > at...
more »

2017-08-17 07:08 (1) Answers

Converting Integer into time

I am using Oracle 12c version. I have a column which is integer data type and it represents total minutes. I need to convert it into HOURS:MINUTES format. Could someone suggest how to do that? Thanks, Venu ...
more »

2017-08-16 19:08 (2) Answers

aspnetcore 2.0 with EF6 and dotConnect for oracle

I am trying to use Oracle using dotConnect for Oracle(v 9.4). Since aspnet core 2.0 has full support to reference older .net framework, I am trying to connect to database using a regular .net assembly(i.e assembly containing my DbContext is in target...
more »

2017-08-16 18:08 (0) Answers

spool for insert in sql developer not working

I have below select query for which the result of this query i want to create insert scripts and saved it in files. I have used spool. set long 10000 set lines 100000 set sqlformat insert spool c:\temp\Insert_TEST_GRP.sql select ID,NAME,TEST...
more »

2017-08-16 18:08 (3) Answers

ORACLE - regexp_substr to return null values

I have used the function regexp_substr(STR,'[^|]+',1,2), it works well at some cases but for null scenarios, it gives the third value, 1|CAT|DOG 3|HARRY|GOAT|STACK 6||LION|TIGER --this row should return null, but the above function gives me LION ...
more »

2017-08-16 09:08 (2) Answers

FROM keyword not found where expected Error in SQL

I've been working with our coder from another office over email, and we've hit a bit of a stand still with this code. I keep getting oracle errors whenever I try and execute the query. Below is the code I'm using: SELECT TALLY_TRAN_MSTR.SHIFT_DT, TA...
more »

2017-08-15 20:08 (1) Answers

Jpa create native query from form input

Usually during my work hours i spend a lot of time querying the db(oracle) and parsing blob from various table where the streams that we receive are stored. There are various type of stream so i was trying to made a simple webapp where i write the se...
more »

2017-08-15 20:08 (1) Answers

Dummy values from a list as a dummy column

I would like to provide a list of ids to a dummy column. This will help me in identifying those ids that are not present in a table. Example: I would like to generate hardcoded dataset below with 'WITH' clause ID 12345 23456 34567 Is this possib...
more »

2017-08-15 17:08 (2) Answers

Filtering Data from a Query with SQL

I'm really running into issues with this SQL code I'm using. I've been posying about this, but not really getting answers. I tried to adapt this code to what I am trying to do, but I keep getting the screen shot of the error message screen grabbed be...
more »

2017-08-15 16:08 (2) Answers

Using AND condition in decode

I have a scenario where I need to check the date and a value to determine the result. Ex- case when my_date > '10-01-2011' and my_value = 0 then 'Do this' else 'Do that' end I'm trying to use this in oracle SQL query, I was wondering if there...
more »

2017-08-15 11:08 (1) Answers