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!

on using connect by prior program not terminating - millions of child nodes

User_C1JMTApr 7 2020 — edited Apr 7 2020

CONNECTBY NOCYCLE - produces 30490986 children nodes for the below scenario.I want to process the child nodes and the program using this query doesnot terminate.

Create table temp_path(

from_node varchar(2),

to_node varchar(2));

INSERT INTO temp_path values('A','B');

INSERT INTO temp_path values('A','C');

INSERT INTO temp_path values('A','D');

INSERT INTO temp_path values('A','E');

INSERT INTO temp_path values('A','F');

INSERT INTO temp_path values('A','G');

INSERT INTO temp_path values('A','H');

INSERT INTO temp_path values('A','I');

INSERT INTO temp_path values('A','J');

INSERT INTO temp_path values('B','K');

INSERT INTO temp_path values('B','L');

INSERT INTO temp_path values('B','C');

INSERT INTO temp_path values('B','E');

INSERT INTO temp_path values('B','M');

INSERT INTO temp_path values('B','N');

INSERT INTO temp_path values('B','O');

INSERT INTO temp_path values('B','J');

INSERT INTO temp_path values('C','K');

INSERT INTO temp_path values('C','N');

INSERT INTO temp_path values('C','J');

INSERT INTO temp_path values('D','K');

INSERT INTO temp_path values('D','B');

INSERT INTO temp_path values('D','L');

INSERT INTO temp_path values('D','C');

INSERT INTO temp_path values('D','E');

INSERT INTO temp_path values('D','F');

INSERT INTO temp_path values('D','G');

INSERT INTO temp_path values('D','M');

INSERT INTO temp_path values('D','P');

INSERT INTO temp_path values('D','Q');

INSERT INTO temp_path values('D','N');

INSERT INTO temp_path values('D','R');

INSERT INTO temp_path values('D','O');

INSERT INTO temp_path values('D','J');

INSERT INTO temp_path values('E','K');

INSERT INTO temp_path values('E','L');

INSERT INTO temp_path values('E','S');

INSERT INTO temp_path values('E','M');

INSERT INTO temp_path values('E','N');

INSERT INTO temp_path values('E','O');

INSERT INTO temp_path values('E','J');

INSERT INTO temp_path values('F','K');

INSERT INTO temp_path values('F','B');

INSERT INTO temp_path values('F','L');

INSERT INTO temp_path values('F','C');

INSERT INTO temp_path values('F','G');

INSERT INTO temp_path values('F','S');

INSERT INTO temp_path values('F','M');

INSERT INTO temp_path values('F','N');

INSERT INTO temp_path values('E','O');

INSERT INTO temp_path values('E','J');

INSERT INTO temp_path values('G','K');

INSERT INTO temp_path values('G','B');

INSERT INTO temp_path values('G','L');

INSERT INTO temp_path values('G','E');

INSERT INTO temp_path values('G','M');

INSERT INTO temp_path values('G','N');

INSERT INTO temp_path values('G','O');

INSERT INTO temp_path values('G','J');

INSERT INTO temp_path values('H','K');

INSERT INTO temp_path values('H','B');

INSERT INTO temp_path values('H','L');

INSERT INTO temp_path values('H','C');

INSERT INTO temp_path values('H','D');

INSERT INTO temp_path values('H','E');

INSERT INTO temp_path values('H','F');

INSERT INTO temp_path values('H','G');

INSERT INTO temp_path values('H','M');

INSERT INTO temp_path values('H','P');

INSERT INTO temp_path values('H','Q');

INSERT INTO temp_path values('H','R');

INSERT INTO temp_path values('H','O');

INSERT INTO temp_path values('H','J');

INSERT INTO temp_path values('I','K');

INSERT INTO temp_path values('I','B');

INSERT INTO temp_path values('I','L');

INSERT INTO temp_path values('I','C');

INSERT INTO temp_path values('I','D');

INSERT INTO temp_path values('I','E');

INSERT INTO temp_path values('I','F');

INSERT INTO temp_path values('I','G');

INSERT INTO temp_path values('I','H');

INSERT INTO temp_path values('I','M');

INSERT INTO temp_path values('I','P');

INSERT INTO temp_path values('I','Q');

INSERT INTO temp_path values('I','N');

INSERT INTO temp_path values('I','R');

INSERT INTO temp_path values('I','O');

INSERT INTO temp_path values('I','J');

INSERT INTO temp_path values('J','K');

INSERT INTO temp_path values('J','L');

INSERT INTO temp_path values('J','N');

INSERT INTO temp_path values('J','O');

INSERT INTO temp_path values('K','N');

INSERT INTO temp_path values('K','J');

INSERT INTO temp_path values('L','K');

INSERT INTO temp_path values('L','N');

INSERT INTO temp_path values('L','J');

INSERT INTO temp_path values('M','K');

INSERT INTO temp_path values('M','B');

INSERT INTO temp_path values('M','L');

INSERT INTO temp_path values('M','C');

INSERT INTO temp_path values('M','E');

INSERT INTO temp_path values('M','F');

INSERT INTO temp_path values('M','G');

INSERT INTO temp_path values('M','N');

INSERT INTO temp_path values('M','O');

INSERT INTO temp_path values('M','J');

INSERT INTO temp_path values('M','F');

INSERT INTO temp_path values('N','J');

INSERT INTO temp_path values('O','K');

INSERT INTO temp_path values('O','L');

INSERT INTO temp_path values('O','N');

INSERT INTO temp_path values('O','J');

INSERT INTO temp_path values('P','K');

INSERT INTO temp_path values('P','A');

INSERT INTO temp_path values('P','B');

INSERT INTO temp_path values('P','L');

INSERT INTO temp_path values('P','T');

INSERT INTO temp_path values('P','C');

INSERT INTO temp_path values('P','D');

INSERT INTO temp_path values('P','E');

INSERT INTO temp_path values('P','F');

INSERT INTO temp_path values('P','G');

INSERT INTO temp_path values('P','H');

INSERT INTO temp_path values('P','I');

INSERT INTO temp_path values('P','N');

INSERT INTO temp_path values('P','R');

INSERT INTO temp_path values('P','O');

INSERT INTO temp_path values('P','J');

INSERT INTO temp_path values('Q','H');

INSERT INTO temp_path values('Q','I');

INSERT INTO temp_path values('Q','D');

INSERT INTO temp_path values('Q','E');

INSERT INTO temp_path values('Q','F');

INSERT INTO temp_path values('Q','G');

INSERT INTO temp_path values('Q','N');

INSERT INTO temp_path values('Q','L');

INSERT INTO temp_path values('Q','K');

INSERT INTO temp_path values('Q','R');

INSERT INTO temp_path values('Q','T');

INSERT INTO temp_path values('Q','A');

INSERT INTO temp_path values('Q','C');

INSERT INTO temp_path values('Q','B');

INSERT INTO temp_path values('Q','O');

INSERT INTO temp_path values('Q','J');

INSERT INTO temp_path values('R','K');

INSERT INTO temp_path values('R','B');

INSERT INTO temp_path values('R','L');

INSERT INTO temp_path values('R','C');

INSERT INTO temp_path values('R','G');

INSERT INTO temp_path values('R','M');

INSERT INTO temp_path values('R','N');

INSERT INTO temp_path values('R','O');

INSERT INTO temp_path values('R','J');

INSERT INTO temp_path values('S','K');

INSERT INTO temp_path values('S','B');

INSERT INTO temp_path values('S','L');

INSERT INTO temp_path values('S','C');

INSERT INTO temp_path values('S','E');

INSERT INTO temp_path values('S','F');

INSERT INTO temp_path values('S','G');

INSERT INTO temp_path values('S','N');

INSERT INTO temp_path values('S','O');

INSERT INTO temp_path values('S','J');

INSERT INTO temp_path values('T','K');

INSERT INTO temp_path values('T','A');

INSERT INTO temp_path values('T','B');

INSERT INTO temp_path values('T','L');

INSERT INTO temp_path values('T','C');

INSERT INTO temp_path values('T','D');

INSERT INTO temp_path values('T','E');

INSERT INTO temp_path values('T','F');

INSERT INTO temp_path values('T','G');

INSERT INTO temp_path values('T','H');

INSERT INTO temp_path values('T','I');

INSERT INTO temp_path values('T','M');

INSERT INTO temp_path values('T','P');

INSERT INTO temp_path values('T','Q');

INSERT INTO temp_path values('T','R');

INSERT INTO temp_path values('T','O');

INSERT INTO temp_path values('T','J');

Number of nodes:

SELECT COUNT(*) FROM( SELECT

from_node,

to_node,

level

FROM

temp_path

START WITH

from_node = 'A'

CONNECT BY NOCYCLE

PRIOR to_node = from_node);

nodes:

SELECT

from_node,

to_node,

level

FROM

temp_path

START WITH

from_node = 'A'

CONNECT BY NOCYCLE

PRIOR to_node = from_node;

Can someone please help

Frank Kulash

Hi, Suman,

You don't seem to have a problem using SQL or PLSQL, so the SQL/PLSQL forum might not be the best place for this thread.

I'll move it to the forum for you.

3514477

Ok. No problem. Thank you!

ddf_dba

dfb12658-ba33-44b9-98a1-2b5f3a584262 wrote:
Hi,
I have written excel macro to connect Oracle Database to get the data. It is perfectly working fine.
I have tried below connection strings. 
 1. con.Open ("User ID=xxx;Password=yyy;Data Source=zz;Provider=oraOLEDB.Oracle")
 2. 
 strCon = "Driver={Microsoft ODBC for Oracle}; " & _
 "CONNECTSTRING=(DESCRIPTION=" & _
 "(ADDRESS=(PROTOCOL=TCP)" & _
 "(HOST=<servername>)(PORT=pno))" & _
 "(CONNECT_DATA=(SID=sid))); uid=xxx; pwd=yyy;"
 con.Open (strCon)
When I copy this excel file in end user machine it is throwing below exception:
Run-time error '3706':
Provider cannot be found. It may not be properly installed.
So, an end user must have the "Oracle Client" in his machine to run this macro on his machine? or is there any way we can connect to oracle database from Excel without installing oracle client?
Thanks,
Suman

WHERE is it 'perfectly working fine'? What is apparent is you simply put the Excel workbook on a different computer that doesn't have that OLEDB source configured; just because it's configured on your machine doesn't make it configured on any other computer.

You should try to configure the datasource on the computer you simply dropped that Excel workbook on and try again.

David Fitzjarrell

jgarry

I've gotten good examples from the hoopercharles blog, every time I forget how to do what David said.

Edit: You may also want to use the Oracle drivers.

3514477

Thanks David! Oracle client has been installed on my machine that's why it's working fine.

Can you please let me know how to configure OLEDB Source in users machine? or Please share the link if you have any.

-Regards

Suman

3514477

Thank you! Do you mean I have to install Oracle drivers in users machine? Could you please share more details on the same?

Regards

Suman

jgarry

Like I said, I can never remember how to do it, I just google odbc hoopercharles plus some more words describing exactly what I want. ORA - 12560 with ODBC

ddf_dba

Yes, to make this easy on you the Oracle client should be installed on the users machine. Microsoft makes Oracle drivers but the Oracle client is more reliable. Without some sort of OLEDB/ODBC driver for Oracle this won't work.

David Fitzjarrell

1 - 8

Added on Apr 7 2020

#sql-developer, #sql-pl-sql

1 comment

128 views

SQL Developer

on using connect by prior program not terminating - millions of child nodes

Comments

Post Details