java.sql.SQLException: Exhausted Resultset

import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.Iterator; public class ReadUpdateDb { public void updateDb(Statement stmt) { ...
more »

2017-06-18 12:06 (1) Answers

Adding a user-defined constraint to an SQL table

So i'm working with 2 tables: Persons and Marriage. Persons hold many attributes and his PK is person_id. Marrige is defined like that: CREATE TABLE Marriage(Person_id NUMBER(6) PRIMARY KEY REFERENCES Persons(Person_id) , Relative_id NUMBER(4) REFER...
more »

2017-06-18 11:06 (2) Answers

ORACLE 12c - "not a single-group group function"

I have a table o employees that contains names, date of employment and some more information. I want to check which year the most employees were employed. I write a query which count employment for each year: SELECT EXTRACT (YEAR FROM e1.empl_date...
more »

2017-06-17 20:06 (4) Answers

SQL query that returns a date

My DB contains a period(month) and a year - I am trying to convert it to a date. I don't care for the actual day of the month so I have just made it "1" (the 1st of the month). In my code I had to convert the "13th" period to the 12th because of th...
more »

2017-06-17 18:06 (2) Answers

Oracle SQL Check constraint between 2 tables

I got 2 tables, Persons and Relationships. The Persons table got only 2 fields for now: ID and Age. Relationships have 3 fields: Person_ID, Relative_ID and Relation What I wanna do is simple: on insertion\update to Relationships I want to check th...
more »

2017-06-17 09:06 (3) Answers

Sqlplus formatting width, as specified

How can you execute the following Oracle/pl-sql code to display two columns side by side? SELECT table_name, user_cons_columns.column_name FROM user_cons_columns; Currently, this is my output formatting: This is the formatting I hope to see: ...
more »

2017-06-17 03:06 (2) Answers

Import Excel Data without Inserting Into Table

I am having some difficulty locating how to import data into Oracle SQL Developer without inserting the data into a table. It seems all functions that I come across require users to insert into tables. Does anyone know if there is a way to go about...
more »

2017-06-17 00:06 (1) Answers

Oracle Combine several columns into one

I have a question in regarding to Oracle Sql, If I have a data called A, with 8 columns: Spot| ID |Sunday|Monday|Tuesday|Wednesday|Thursday|Friday|Saturday ------------------------------------------------------------------------- A| 1 | 0.1 ...
more »

2017-06-16 21:06 (2) Answers

Difference between two lines, ignoring some lines

I have a workflow which runs the night, depending of the planning. Sometimes it succeeds, sometimes not. When it fails, I want to know for how many days it's been KO, but without counting the day where it is not expected I have the following input d...
more »

2017-06-16 20:06 (1) Answers

Python cx_Oracle invalid option

I'm trying to use Python to create a table and insert some values cur = con.cursor() remove_table = ''' BEGIN EXECUTE IMMEDIATE 'DROP TABLE {}'; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END; ''' c...
more »

2017-06-16 19:06 (1) Answers

Oracle - optimising SQL query

I have two tables - countries (id, name) and users (id, name, country_id). Each user belongs to one country. I want to select 10 random users from the same random country. However, there are countries that have less than 10 users, so I can't use them...
more »

2017-06-16 16:06 (3) Answers

PLSQL Error : component must be declared

It is probably a lame error but I do not find it. In a procedure I have this : PROCEDURE myProcedure(returnCode OUT NUMBER) IS CURSOR myCursor IS SELECT column1, column2, column3, column4 FROM MyTable WHERE columX IS NULL AND columnY = 'PS'; TYPE...
more »

2017-06-16 12:06 (1) Answers

Copying Data from Oracle Server to SQL Server

I'm quite new to coding in general and I'm looking to copy 47 columns with c300,000 rows of data, from an Oracle to an SQL database, on a daily basis. The code will be stored as a Windows Service, running at the same time every day (or more likely ni...
more »

2017-06-16 10:06 (1) Answers

Current Date till Hour in Oracle

I want to get the date with just the current hour. Using trunc(sysdate) gives us something like 2017-06-16 00:00:00 But, I want the current hour as well : 2017-06-16 10:00:00 2017-06-16 11:00:00 and so on. I want it to be a date (and not char)...
more »

2017-06-16 10:06 (1) Answers

oracle, join with special requests

I have three tables with articles, inventory and warehouse. CREATE TABLE "ARTICLE" ( "ID" NUMBER(9,0), "NAME" VARCHAR2(30 BYTE) ) CREATE TABLE "INVENTORY" ( "ID" NUMBER(9,0), "ARTICLE_ID" NUMBER(9,0), "QUANTITY" NUMBER(9,0), ...
more »

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

Fetch multiple row values as column values

I have two tables MASTER_TABLE and DOCUMENTS table. Both are related with a column reference_id. DOCUMENTS table has columns doc_id,doc_type and doc_creation_date We can have more than one entry in DOCUMENTS table for each doc_type with different doc...
more »

2017-06-16 08:06 (1) Answers

Greatest n per group while also returning content

I have looked at other answers but am having trouble applying the answers to my application. I have a query that returns something like the following: SELECT * FROM MYTABLE T; __________________________________________________________ | ID | AOR...
more »

2017-06-15 22:06 (5) Answers

Oracle Regex remove duplicates

I have a requirement to remove duplicate values from a comma separated string. Input String: a,a,a,b,c,a,b Expected output: a,b,c What I have tried: with ct(str) as( select 'a,a,a,b,c,a,b' from dual ) select REGEXP_REPLACE(str,'([^,]*)(,\1)+($|,)...
more »

2017-06-15 15:06 (0) Answers