Skip to Main Content
Forums

DevOps, CI/CD and Automation

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!

Oracle Provider not Found

K-Oct 10 2012 — edited Nov 26 2012
Good afternoon Oracle Forum members,

I'm having trouble on connecting a OraOLEDB.Oracle application to my Oracle Database 11g R2 (11.2.0.1.0), I have a Windows Server 2008 Enterprise machine running it.
Each time I try to connect it says: Oracle provider not found. It may be not properly installed.
I've searched many site and tried many things like:

-Registering DLLs manually using:
REGSVR32 "C:\Program Files\Common Files\System\Ole DB\sqloledb.dll"
REGSVR32 "C:\Program Files\Common Files\System\Ole DB\Oledb32.dll"
REGSVR32 "C:\Program Files\Common Files\System\Ole DB\Msdasql.dll"
REGSVR32 "C:\Program Files\Common Files\System\msadc\Msadce.dll"
REGSVR32 "C:\oracle\product\11.2.0\dbhome_1\BIN\oraoledb11.dll"

-Installing the client with ODAC112012 pack that comes with 32 bit features (but after it the database stops working properly.

-Installed the client with ODTwithODAC112012 (but after it the database stops working properly)

-Formatting and re-installing Win 2008 Server Enterprise, Oracle Database 11g R2 and all applications (did it more than 4 times)

-Changing the provider from OraOLEDB.Oracle to MSDAORA (results in "Unknown Provider" message)

Is there something I did wrong? Is there something else I have to do to make it work properly?



Thank you very much!

Edited by: K- on Oct 10, 2012 3:44 PM

Comments

AndrewSayer
Answer

You need to get the ip address of the parent for each row, you can use an analytic function here max(decode(is_parent,1,ip_address)) over (partition by parent_id)

And then it is trivial to order by is_parent desc:

with ctrl as (
select 100 rec_id,       'Record 1    ' description ,    1000 parent_id, '80.160.4.5  ' ip_address,  1 is_parent from dual union all
select 101 rec_id,       'child       ' description ,    1000 parent_id, '169.60.5.20 ' ip_address,  0 is_parent from dual union all
select 105 rec_id,       'child       ' description ,    1000 parent_id, '169.60.5.20 ' ip_address,  0 is_parent from dual union all
select 103 rec_id,       'Record 2    ' description ,    1002 parent_id, '80.160.4.3  ' ip_address,  1 is_parent from dual union all
select 104 rec_id,       'child       ' description ,    1002 parent_id, '169.60.2.5  ' ip_address,  0 is_parent from dual union all
select 102 rec_id,       'Record 3    ' description ,    1001 parent_id, '80.160.4.2  ' ip_address,  1 is_parent from dual)
select *
From ctrl order by max(decode(is_parent,1,ip_address)) over (partition by parent_id), is_parent desc
/

    REC_ID DESCRIPTION   PARENT_ID IP_ADDRESS    IS_PARENT

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

       102 Record 3           1001 80.160.4.2            1

       103 Record 2           1002 80.160.4.3            1

       104 child              1002 169.60.2.5            0

       100 Record 1           1000 80.160.4.5            1

       101 child              1000 169.60.5.20           0

       105 child              1000 169.60.5.20           0

Marked as Answer by 3288275 · Sep 27 2020
2 people found this helpful
unknown-7404

If you have hierarchical data use a hierarchical query. The result tree will have the parents and children together.

See the examples queries in the Oracle documentation

https://docs.oracle.com/database/121/SQLRF/queries003.htm#SQLRF52335

Hierarchical Query Examples
CONNECT BY Example The following hierarchical query uses 
the CONNECT BY clause to define the relationship between employees and managers:
SELECT employee_id, last_name, manager_id
   FROM employees
   CONNECT BY PRIOR employee_id = manager_id;
EMPLOYEE_ID LAST_NAME                 MANAGER_ID
----------- ------------------------- ----------
        101 Kochhar                          100
        108 Greenberg                        101
        109 Faviet                           108
        110 Chen                             108
        111 Sciarra                          108
        112 Urman                            108
        113 Popp                             108
        200 Whalen                           101
        203 Mavris                           101
        204 Baer                             101
. . .
1 person found this helpful
Barbara Boehmer 
SCOTT@orcl_12.1.0.2.0> select rec_id, description, parent_id, ip_address, is_parent
  2  from  database_table
  3  start  with is_parent=1
  4  connect by prior parent_id = parent_id
  5          and prior is_parent = is_parent + 1
  6  order  siblings by ip_address
  7  /
    REC_ID DESCRIPTION  PARENT_ID IP_ADDRESS  IS_PARENT
---------- ----------- ---------- ----------- ----------
      102 Record 3          1001 80.160.4.2          1
      103 Record 2          1002 80.160.4.3          1
      104 child            1002 169.60.2.5          0
      100 Record 1          1000 80.160.4.5          1
      101 child            1000 169.60.5.20          0
      105 child            1000 169.60.5.20          0
6 rows selected.
1 person found this helpful
3288275

Thank you Andrew Sayer ....  That was an awesome solution!

1 person found this helpful
Pablolee

@3288275

Whilst your sample data does  not show it, is it possible that in a scenario where you have >1 child record of a specific parent that the IP addresses are different (rather than your current data scenario where both IP addresses for this situation are the same: 169.60.5.20  )     

If so, then you'll need to add another sort to the list (or take a closer look at Barbara's solution)

1 - 5
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Dec 24 2012
Added on Oct 10 2012
#oracle-provider-for-ole-db, #windows-and-net
4 comments
4,408 views