Max function in Oracle 12

Hope all is well. I have two records below. I was trying to pull the latest record based from the Sold_DT, which would give me the record with 7 in Item column. Store Sold_DT Item CVS 1/22/2017 12:05:00 AM...
more »

2017-10-04 21:10 (3) Answers

Equivalent to SQLERRM when no error?

Does anyone knows if there is an equivalent to SQLERRM when no error occurred? I know I can count the affected rows like here, but can I automatically get the message I would get from SQLPLUS e.g. role granted. after an execute immediate in PL/SQL? ...
more »

2017-10-04 17:10 (2) Answers

SESSIONS_PER_USER limit with nodejs

I've been struggling with this issue for a couple of weeks: For some reason, our nodejs script which is connecting to a oracle database in another machine started erroring with SESSIONS_PER_USER limit, but we don't have any other connection opened t...
more »

2017-10-04 17:10 (1) Answers

Merging multiple sql update statements(Oracle)

I'm not very good at sql and I've tried a few things. What would be the best way to combine these 5 update statements into a single statement considering performance of the code? Would be a great help. Thank you so much! Code: ----------------1 Up...
more »

2017-10-04 12:10 (3) Answers

Reading Oracle htp.p output into Java app

We have built a dynamic stored proc reader due to the nature of our system. The only issue I have left is I am unable to read the htp.p output back into my application after calling the proc. Due to the reader building the proc dynamical from a requ...
more »

2017-10-04 11:10 (0) Answers

NVL function in Oracle

I want to select all the data from the table employee and make an inner join with an other table for example: SELECT * FROM EMPLOYEE INNER JOIN Deparment ON Employee.Id_Department = Deparment.Deparment_Id AND NVL('Mathematics', Deparment.Name)...
more »

2017-10-04 08:10 (2) Answers

How to Select a substring in Oracle SQL

say I have a table customer with columns Firstname and lastname I would like to be able to write a query that selects all the customers which has exact lastname and the fuzzy firstname The Substring is returning the values as expected when I run t...
more »

2017-10-03 19:10 (2) Answers

ansi SQL date function in Oracle

I realize I could use a combination of to_char and to_date and what not as a work-around, but I'm trying to figure out why this doesn't work. I am using Oracle 12.1 select '2016-10-01' from dual union all select to_char(2016)||'-10-01' from dua...
more »

2017-10-03 18:10 (1) Answers

Filter by numeric column start with a given number

I'd like to filter my rows based on a numeric column. Problem is that the input is a pattern - for e.g. - a number starting with 2045 or something. I could do this: select * from my_table where num_column like '2045%'; It works but that doesn't le...
more »

2017-10-03 13:10 (2) Answers

Storing a Query In A Variable

I've been looking into storing a query into a variable but I am having trouble doing so. I keep getting the error wrong number or types of arguments in call to '||' This is what I tried DECLARE addresses VARCHAR(200); CURSOR allAddresses IS ...
more »

2017-10-03 12:10 (4) Answers

Compare multiple values in a single column oracle

I want to compare multiple values of a column with a single value of another column.I had tried a lot but it gives me "No row selected" here's my scenario... I have a table Bus_stop_table: Bus_ID(FK) ------STOP_ID 201 -------------------401 201...
more »

2017-10-03 05:10 (1) Answers

Grouping data by name and date ranges

I have data in my oracle table where I have names and date rages as following: Name From To Lopes, Janine 07-Jun-17 16-Jul-17 Lopes, Janine 17-Jul-17 23-Jul-17 Lopes, Janine 24-Jul-17 31-Aug-17 Baptista,...
more »

2017-10-02 17:10 (2) Answers

What is causing this date comparison error?

I've been struggling to understand why this query doesn't work. All of the partition names for this owner have values in the format 'R_DATE_20170831'. But the query below always returns an error ORA-01841 (full) year must be between -4713 and +9999...
more »

2017-10-02 17:10 (1) Answers