SQL Developer

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Cannot Establish New Connection

999564Mar 27 2013 — edited Mar 28 2013

Hello all, I have quite a peculiar problem regarding the what I've come to find fairly common error, "Network Adapter could not establish the connection."

Now, before you close the tab/page/browser, hear me out -- I've looked through at least two dozen threads with this topic, but none share the same situation I have, and none have supplied any help whatsoever.

I'm a college student in a databasing class of 12 other students, where the instructor created a sql developer setup batch file and package, so that all copies of SQL Developer would be absolutely identical. As well, all machines within this classroom are absolutely identical as well, being imaged school harddrives in identical hardware computers. Within the classroom environment, we use database connections on localhost for the purpose of training. The instructor wrote the batch file to set the listener and start the service. Somehow, I receive the above error while no other person within the room has a problem.

I have verified that the listener is set to listen on localhost and port 1521, and that I am connecting to localhost and port 1521.
- I have attempted changing the listening port.
- I have attempted changing the listening IP to 127.0.01, this machine's network IP, this machine's internet IP, this machine's name, and this machine's qualified domain name.

I have verified that the listener service is operating.
- I have attempted stopping and starting the service.
- I have attempted changing the service to manual startup, rebooting the computer, and restarting manually.

I have verified that the network adapter is operational.
- Used Ipconfig /release and /renew
- Can ping self, as well as all other computers in room.
- Can ping the default gateway
- Can tracert the default gateway
- Have DNS connectivity

I also attempted a fresh copy downloaded from oracle on my personal laptop, and manually used the same settings as the instructor prescribed. I received the same error, where I attempted all items in the above list to no avail. I then moved to another machine in the room, which also did nothing, and finally I moved to a machine that another student had already used, which worked fine.

Working at my own workstation now, I finally attempted simply running as administrator, which attained the same error, and I rolled back my version of Java, which still received the same error.

At this point, I have exhausted everything I know to do, and am quickly becoming frustrated. Neither my workstation nor another unused computer within the room work. My personal laptop not connecting doesn't bother me. If anyone at all has any advice whatsoever, I'm all ears. I'd even make sure it's plugged in at this point.

BrendanP

Looks like you haven't got a well-defined problem as what you're looking for, 'nearest available ancestor' doesn't mean anything until you specify it, and your example doesn't do that.

Solomon Yakobson

You just need to traverse the hierarchy from leaf to root:

with qryData as (

select 1 as ID, 'Bert' as NAME, to_number(null) as PARENT_ID from dual union

select 2 as ID, 'Mark' , 1 from dual union

select 3 as ID, 'Brenda' , 1 from dual union

select 4 as ID, 'Mike' , 3 from dual union

select 5 as ID, 'Steve' , 4 from dual union

select 6 as ID, 'John' , 2 from dual union

select 7 as ID, 'Jo' , 6 from dual union

select 8 as ID, 'Jim' , 2 from dual union

select 9 as ID, 'Jane' , 7 from dual

)

select connect_by_root id id,

id ancestor_id

from qryData

connect by ID = prior PARENT_ID

order by id,

ancestor_id

ID ANCESTOR_ID
--- -----------
1           1
2           1
2           2
3           1
3           3
4           1
4           3
4           4
5           1
5           3
5           4

ID ANCESTOR_ID
--- -----------
5           5
6           1
6           2
6           6
7           1
7           2
7           6
7           7
8           1
8           2
8           8

ID ANCESTOR_ID
--- -----------
9           1
9           2
9           6
9           7
9           9

27 rows selected.

SQL>

SY.

Frank Kulash

Hi,

So you need to preserve the tree structure (in things like id_path) after you remove some of the rows from the output, is that the problem?
Here's one way to do that:

WITH connect_by_results AS

(

SELECT q.*

, SYS_CONNECT_BY_PATH (id, '/') AS id_path

, SYS_CONNECT_BY_PATH ( CASE

WHEN id IN (1, 3, 5, 6, 8, 9) -- or whatever

THEN id

END

, '/'

) AS good_id_path

, ROWNUM AS r_num

FROM qryData q

START WITH parent_id IS NULL

CONNECT BY PRIOR id = parent_id

)

SELECT id, name, parent_id, id_path

, REGEXP_SUBSTR ( RTRIM ( SUBSTR ( good_id_path

, 1

, INSTR (good_id_path, '/', -1)

)

, '/'

)

, '[^/]+$'

) AS avail_ancestor_id

, good_id_path -- Only for debugging

FROM connect_by_results

WHERE SUBSTR (good_id_path, -1) != '/'

ORDER BY r_num

;

This doesn't involve a second CONNECT BY, a self-join, or any analytic functions, all of which can be slow.

Whatever criteia determine whether a row is in the result set or not are put in the CASE expression that produces good_id_path, and not in a WHERE clause, so good_id_path will include only ids from the final result set. If good_id_path does not end with a '/', then it will be included. Finding the available_ancestor_id is simply a matter of finding the next-to-last id (if any) in good_id_path.

Output:

AVAIL_

PARENT ANCESTOR

ID NAME _ID ID_PATH _ID GOOD_ID_PATH

--- ------ ------ ------------ ---------- ------------

1 Bert /1 /1

6 John 2 /1/2/6 1 /1//6

9 Jane 7 /1/2/6/7/9 6 /1//6//9

8 Jim 2 /1/2/8 1 /1//8

3 Brenda 1 /1/3 1 /1/3

5 Steve 4 /1/3/4/5 3 /1/3//5

1 person found this helpful

paul zip

"Nearest available ancestor" is closest ancestor relation in your family tree that exists in the list starting from parent, grandparent, great-grandparent .... nth great grandparent .

For example. Who is Steve's nearest available ancestor in the filtered list?

Steve's family tree is : /Bert/Brenda/Mike/Steve

Mike isn't in the list, so we move onto Brenda, who is. Brenda, ID = 3, So ANCESTOR_ID = 3

Solomon Yakobson

It looks like I didn't understand the requirements. Based on

ID NAME PARENT_ID ID_PATH AVAIL_ANCESTOR_ID

---------- ------ ---------- ------------- -----------------

1 Bert /1

6 John 2 /1/2/6 1

8 Jim 2 /1/2/8 1

9 Jane 7 /1/2/6/7/9 6

3 Brenda 1 /1/3 1

5 Steve 4 /1/3/4/5 3

You want a grandparent id, or parent id if there is no grandparent. If so:

with qryData as (

select 1 as ID, 'Bert' as NAME, to_number(null) as PARENT_ID from dual union

select 2 as ID, 'Mark' , 1 from dual union

select 3 as ID, 'Brenda' , 1 from dual union

select 4 as ID, 'Mike' , 3 from dual union

select 5 as ID, 'Steve' , 4 from dual union

select 6 as ID, 'John' , 2 from dual union

select 7 as ID, 'Jo' , 6 from dual union

select 8 as ID, 'Jim' , 2 from dual union

select 9 as ID, 'Jane' , 7 from dual

)

select q.*,

sys_connect_by_path(ID, '/') ID_PATH,

nvl(prior parent_id,parent_id) AVAIL_ANCESTOR_ID

from qryData q

where id IN (1,3,5,6,8,9)

start with parent_id is null

connect by prior ID = PARENT_ID

ID NAME    PARENT_ID ID_PATH               AVAIL_ANCESTOR_ID
--- ------ ---------- -------------------- ------------------
1 Bert              /1
6 John            2 /1/2/6                                1
9 Jane            7 /1/2/6/7/9                            6
8 Jim             2 /1/2/8                                1
3 Brenda          1 /1/3                                  1
5 Steve           4 /1/3/4/5                              3

6 rows selected.

SQL>

SY.

BrendanP

Well, perhaps I'm missing something obvious, but surely you don't have the family tree as such, only id, parent_id and have to deduce the tree, and as the parent of Steve doesn't exist, how do you know Brenda is the one? If you actually have the id_path - your qryData test subquery doesn't - then ok.

Message was edited by: BrendanP Looks like OP has updated the subquery so it now does have the 'missing' ids, but still seems underspecified - where are the missing ids identified as such?

paul zip

This is a clever solution! The only problem I have is I wouldn't be able to generate "good_id_path" as part of the filtered list, as filtering is not done by me and is done after the id_path is generated. I'd have to join filtered list back to the full list to generate it, unless there's a tweak to do it?

paul zip

This only works for missing parents, I need to continue from parent => grandparent => great grandparent....... => great (x n) grandparent

if I use "where id IN (1,3,5,8,9)" it says Jane's ANCESTOR_ID = 6 (John) but he isn't in the list, so it is incorrect.

Solomon Yakobson

OK. It looks like now I undestand your requirements. As I showed in my first reply, you can traverse hierarchy from leaf to root:

with qryData as (

select 1 as ID, 'Bert' as NAME, to_number(null) as PARENT_ID from dual union

select 2 as ID, 'Mark' , 1 from dual union

select 3 as ID, 'Brenda' , 1 from dual union

select 4 as ID, 'Mike' , 3 from dual union

select 5 as ID, 'Steve' , 4 from dual union

select 6 as ID, 'John' , 2 from dual union

select 7 as ID, 'Jo' , 6 from dual union

select 8 as ID, 'Jim' , 2 from dual union

select 9 as ID, 'Jane' , 7 from dual

ancestors as (

select level lvl,

connect_by_root id id,

connect_by_root name name,

connect_by_root parent_id parent_id,

parent_id ancestor_id

from qryData

start with id in (1,3,5,6,8,9)

connect by ID = prior PARENT_ID

)

select id,

name,

parent_id,

min(ancestor_id) keep(dense_rank first order by lvl) avail_ancestor_id

from ancestors

where ancestor_id in (1,3,5,6,8,9)

or ancestor_id is null

group by id,

name,

parent_id

order by id,

avail_ancestor_id

ID NAME    PARENT_ID AVAIL_ANCESTOR_ID
--- ------ ---------- ------------------
1 Bert
3 Brenda          1                  1
5 Steve           4                  3
6 John            2                  1
8 Jim             2                  1
9 Jane            7                  6

6 rows selected.

SQL>

SY.

Frank Kulash

Answer

Hi,

paulzip wrote:

I see; you're given connect_by_results (with id_path), and that's all you have to work with, besides the original table.

Here's one way:

SELECT d.id, d.name, d.parent_id, d.id_path

, MIN (a.id) KEEP (DENSE_RANK LAST ORDER BY a.id_path) AS avail_ancestor_id

FROM connect_by_results d

LEFT OUTER JOIN connect_by_results a ON d.id_path LIKE a.id_path || '/%'

GROUP BY d.id, d.name, d.parent_id, d.id_path

ORDER BY d.id_path

;

The fastest way depends on lots of things, some of which can't be known until run-time. The query above might be best if the number of rows in the result set is small, but you don't know anything else about them (e.g., whether they all have a common ancestor).

Marked as Answer by paul zip · Sep 27 2020

Solomon Yakobson

Actually, it can be optimized even more:

with qryData as (

select 1 as ID, 'Bert' as NAME, to_number(null) as PARENT_ID from dual union

select 2 as ID, 'Mark' , 1 from dual union

select 3 as ID, 'Brenda' , 1 from dual union

select 4 as ID, 'Mike' , 3 from dual union

select 5 as ID, 'Steve' , 4 from dual union

select 6 as ID, 'John' , 2 from dual union

select 7 as ID, 'Jo' , 6 from dual union

select 8 as ID, 'Jim' , 2 from dual union

select 9 as ID, 'Jane' , 7 from dual

ancestors as (

select level lvl,

connect_by_root id id,

connect_by_root name name,

connect_by_root parent_id parent_id,

parent_id ancestor_id

from qryData

start with id in (1,3,5,6,8,9)

connect by id = prior parent_id

and id not in (1,3,5,6,8,9)

)

select id,

name,

parent_id,

min(ancestor_id) keep(dense_rank first order by case when parent_id in (1,3,5,6,8,9) then lvl end nulls last) avail_ancestor_id

from ancestors

group by id,

name,

parent_id

order by id,

avail_ancestor_id

6 rows selected.

SQL>

SY.

paul zip

I was very impressed with this approach. Yes, it can see may be slow with large datasets, but with smaller sets it'll be fine. What impressed me is it was extremely clever in the way it only uses the filtered dataset to yield the answer, Quite genius.

paul zip

I can see what you are doing, but my list could contain 100s / 1000s of items, how would this transpose to your "in (1,3,5,6,8,9)" situations, without multiple table scans?

Solomon Yakobson

paulzip wrote:

I can see what you are doing, but my list could contain 100s / 1000s of items, how would this transpose to your "in (1,3,5,6,8,9)" situations, without multiple table scans?

So what prevents you from using nested table/varray or a driver table:

SQL> select *
2 from qryData
3 /

ID NAME    PARENT_ID
--- ------ ----------
1 Bert
2 Mark            1
3 Brenda          1
4 Mike            3
5 Steve           4
6 John            2
7 Jo              6
8 Jim             2
9 Jane            7

9 rows selected.

SQL> explain plan for
2 with ancestors as (
3                   select level lvl,
4                           connect_by_root id id,
5                           connect_by_root name name,
6                           connect_by_root parent_id parent_id,
7                           parent_id ancestor_id
8                     from qryData
9                     start with id in (select * from table(sys.OdciNumberList(1,3,5,6,8,9)))
10                     connect by id = prior parent_id
11                            and id not in (select * from table(sys.OdciNumberList(1,3,5,6,8,9)))
12                  )
13 select id,
14          name,
15          parent_id,
16          min(ancestor_id) keep(dense_rank first order by case when parent_id in (select * from table(sys.OdciNumberList(1,3,5,6,8,9))) then lvl end nulls last) avail_ancestor_id
17    from ancestors
18    group by id,
19             name,
20             parent_id
21    order by id,
22             avail_ancestor_id
23 /

Explained.

SQL> select *
2 from table(dbms_xplan.display)
3 /

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3643621826

------------------------------------------------------------------------------------------------------
| Id | Operation                                  | Name    | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |         |     9 |   513 |     5 (40)| 00:00:01 |
|* 1 | COLLECTION ITERATOR CONSTRUCTOR FETCH     |         |     1 |     2 |     2   (0)| 00:00:01 |
|   2 | SORT ORDER BY                             |         |     9 |   513 |     5 (40)| 00:00:01 |
|   3 |   SORT GROUP BY                            |         |     9 |   513 |     5 (40)| 00:00:01 |
|   4 |    VIEW                                    |         |     9 |   513 |    22 (87)| 00:00:01 |
|* 5 |     CONNECT BY NO FILTERING WITH START-WITH|         |       |       |            |          |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
|   6 |      TABLE ACCESS FULL                     | QRYDATA |     9 |   279 |     3   (0)| 00:00:01 |
|* 7 |      COLLECTION ITERATOR CONSTRUCTOR FETCH |         |     2 |     4 |     2   (0)| 00:00:01 |
|* 8 |      COLLECTION ITERATOR CONSTRUCTOR FETCH |         |     1 |     2 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(VALUE(KOKBF$)=:B1)
   5 - access("ID"=PRIOR "PARENT_ID")
       filter( NOT EXISTS (SELECT 0 FROM TABLE() "KOKBF$3" WHERE LNNVL(VALUE(KOKBF$)<>:B1))

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
              AND EXISTS (SELECT 0 FROM TABLE() "KOKBF$0" WHERE VALUE(KOKBF$)=:B2))
   7 - filter(LNNVL(VALUE(KOKBF$)<>:B1))
   8 - filter(VALUE(KOKBF$)=:B1)

Note
-----
- dynamic sampling used for this statement (level=2)

29 rows selected.

SQL>

SY.

1 person found this helpful

1 - 14

Locked Post

New comments cannot be posted to this locked post.

Locked on Apr 25 2013

Added on Mar 27 2013

#connection, #error, #java, #sql-developer

3 comments

502 views

SQL Developer

Cannot Establish New Connection

Comments

Post Details