Oracle sql constraint questions

I'm having problems with creating a oracle sql script. How do I create these two constraints? If VID is null then FID has to be null as well (VID = null -> FID = null) There has to be exactly one row where VID is null which also means that FID has ...
more »

2017-04-22 13:04 (2) Answers

Listener refused the connection

I am having a problem while configuring a hibernate application. Please could you help me with this. I searched a whole lot of forums but could not find the solution. I know this question has been asked in previously, but the solution didn't work for...
more »

2017-04-22 09:04 (0) Answers

Oracle SQL add foreign key

These are the two tables I currently have. CREATE TABLE WORK ( CODE varchar(255) PRIMARY KEY, TITLE varchar(255) ); CREATE TABLE TEACHER ( ID varchar(255) PRIMARY KEY, NAME varchar(255), GENDER varchar(255) ); And I'm trying to add CODE to the TE...
more »

2017-04-21 13:04 (2) Answers

Create view with spatial query and limit results

Oracle 12c DB with Locator... I have 2 tables- table1 has a point geometry column. Table2 has a polygon geometry column. I want to create a view with only the table2 polygons that contain exactly 1 point from table1. Is this possible? I have tried ...
more »

2017-04-21 12:04 (1) Answers

XML Parsing in Oracle

I am trying to parse an XML text. It is stored in a table t_testxml, in column xml_data which is CLOB type. The xml looks like: <?xml version="1.0" encoding="UTF-8"?> <defaultmpftest:defaultmpftest xmlns:defaultmpftest="http://test.com" t...
more »

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

sql is duplicating my results

I believe the problem is within my joins but i am unable to correct it. The SQL should return 3 rows however it is duplicating and returning 12 rows instead. Any help would be much appreciated! SELECT J.JOURNEY_NUMBER, L.DESCRIPTION, L.USE_CODE,...
more »

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

Oracle Regular Expression-To match an exact word

I need to search in a column named DESCRIPTION in a table which match the exact word ICE, Ice or ice. There may be other words before or after the keyword but it shouldn't be like Price, Mice, Nice. I want to take the help of Oracle regular expressio...
more »

2017-04-21 09:04 (2) Answers

Oracle SQL - Select First n Distinct Rows

In Oracle 12, we finally have a limit feature, so we can simply select distinct columnname from mytable fetch first n rows only; However, I am presently stuck with a previous version and would like to know how to achieve this result. Ideally the ...
more »

2017-04-21 01:04 (2) Answers

Oracle SQL - Query Time Limit

In a similar fashion to how select * from mytable where rownum <= 1000; will give me the first 1000 rows of results of a query, is there a way to select * from mytable where runtime <= 1000; which would return the results obtained in the ...
more »

2017-04-20 23:04 (1) Answers

Oracle Rank() within (sub)group

I am trying to get the correct rank within a subgroup and am having problems with the outcome. I need to get a rank of each time the group changes based on a timestamp. For example, using this table: create table syntrans ( transid number, ...
more »

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

Match specific words in comma delimited string

I have a list of value that are allowed such as: the cat, the mouse, the dog, and the parrot. Now I want to have the possibility to add many values from this list in a cell separated with semicolon (;). That means I can add: The cat; The dog; Th...
more »

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

How can I pass optional params to procedure?

I have 2 mrthods save() and edit() entity. When I save entity I check date overlap and if date period unique - save. For that I use oracle procedure. If procedure return >0 rows - this period not unique. When I edit entity - it already in db and I ne...
more »

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

SQL distinct count from distinct rows?

The following line of code gives me the time needed without duplicates, helping me avoid the actual dates. SELECT DISTINCT TO_CHAR(Start_Date_Time, 'HH:MI AM') AS StartTime FROM Section GROUP BY Start_Date_Time; However, when I try to ...
more »

2017-04-20 02:04 (1) Answers

Purpose of Self-Joins

I am learning to program with SQL and have just been introduced to self-joins. I understand how these work, but I don't understand what their purpose is besides a very specific usage, joining an employee table to itself to neatly display employees an...
more »

2017-04-19 19:04 (4) Answers

Python tnsnames.ora parser

Good afternoon. I need a dict containing all database connections from tnsnames.ora file. I need to go from this : (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=mydbserver.mydomain.com)(PORT=1521)))(CONNECT_DATA=(SID=CATAL)(SERVER=DEDIC...
more »

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