Skip to Main Content

ODP.NET

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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

ODP.Net Download : After Installation have Problem

287726Jun 21 2002
HI
I have downloaded the ODB.net software and installed the software.
MY OS : Windows 2000
My DB : Oracle 9i Release 1

While installation, a wizard asked me to stop my local machines database and TNS Service, HTTP service etc... SO i have stopped all oracle services, and again i started the setup. it went through fine. While finishing the installation the installation wizard asked me configure the net service. During this process i selected the option to manually setup the net service. And wizard took me to window where by it asked netservice name i have provided my local machines net service name. and host name to be local machines name but it failed to connect with the server, so alternatively i gave another net service which is available in my office network. Finally it succeded the installation.
And now i tried to restart my local database service name and while starting the service it gives me the following error message.

Window Name : ORACLE.EXE ENTRY POINT NOT FOUND
Window Description : The Procedure Entry Point kdrgtl could not be located in the dynamic link library OraCommon9.dll

and after this message another message is poping up

Window Description : " Could not start the OracleServiceVenkat service in the local computer
Error Number 1053 : The service did not respond to the start or control request in a timely fashion

Could any one tell me how to access my local database.

Comments

Frank Kulash
Answer

Hi, User_TDDEP
In the query you posted, there is no relationship between departments and jobs, that is, there is a cross-join; then employees is outer-joined to that result set. Here's one way to do it in ANSI notation:

SELECT	   e.*, d.*, j.*
FROM	   departments  d
CROSS JOIN jobs		j
LEFT JOIN  employees    e ON  e.department_id = d.department_id
   	 	    	  AND e.job_id	      = 'HR REP'
			  AND j.job_id	      = e.job_id
ORDER BY   e.department_id
;
Marked as Answer by User_TDDEP · Jun 30 2022
User_TDDEP

image.pngAs you wrote - it is a cartesian product.
ok - now it is clear for me.
and in case of simple condition
SELECT E.*, D.*, J.*
FROM EMPLOYEES E, DEPARTMENTS D, JOBS J
WHERE E.DEPARTMENT_ID(+)=D.DEPARTMENT_ID
AND E.JOB_ID(+)=J.JOB_ID
AND E.JOB_ID(+)='FI_ACCOUNT'
AND E.LAST_NAME='Lorentz' --added cond.
ORDER BY E.DEPARTMENT_ID;
;
the AND E.LAST_NAME='Lorentz' --added cond.
will be rewrite to the separeted WHERE clause like this one true?

SELECT e.*, d.*, j.*
FROM departments d
CROSS JOIN jobs j
LEFT JOIN employees e
ON (e.department_id = d.department_id AND e.job_id = 'FI_ACCOUNT' AND j.job_id = e.job_id)
WHERE E.LAST_NAME='Lorentz'
ORDER BY e.department_id
;

Frank Kulash

Hi, User_TDDEP
The two queries you posted half an hour ago are equivalent. The conditions
AND E.JOB_ID(+)='FI_ACCOUNT'
AND E.LAST_NAME='Lorentz' 
are fundamentally different. The former, using (+), means "don't exclude rows just because of the job_id", but the latter means "do exclude rows if they don't have the right last_name". Another way to get the same results is

SELECT   e.*, d.*, j.*
FROM 	 departments d
JOIN	 employees   e   ON e.department_id = d.department_id
JOIN 	 jobs 	     j	 ON j.job_id 	    = e.job_id
WHERE 	 e.job_id    = 'FI_ACCOUNT'
AND	 e.last_name = 'Lorentz'
ORDER BY e.department_id
;

using inner joins

Stax

Hi @User_TDDEP
As you wrote - it is a cartesian product.
so it should be (the script needs to be changed/improved if necessary)
DEPARTMENTS and JOBS are not related, so Cartesian
magic data E.JOB_ID='HR REP' one row
in the older oracle version we will get an error

SQL> /
WHERE E.DEPARTMENT_ID(+)=D.DEPARTMENT_ID
                        *
ERROR at line 12:
ORA-01417: a table may be outer joined to at most one other table

see example (DEPARTMENTS and JOBS have only 2 rows)

with EMPLOYEES (DEPARTMENT_ID,JOB_ID,ENAME)  as
  (select 10,'HR REP','JONES' from dual 
  )
,DEPARTMENTS (DEPARTMENT_ID,DNAME) as
  (select 10,'Administration' from dual union all
   select 210,'IT Support' from dual
  )
,JOBS (JOB_ID,JOB_TITLE) as (
  select 'HR REP','HRR'  from dual union all
  select 'IT_PROG','Programmer'  from dual 
  )
SELECT J.*,E.*, D.*
FROM EMPLOYEES E, DEPARTMENTS D, JOBS J
WHERE E.DEPARTMENT_ID(+)=D.DEPARTMENT_ID
AND E.JOB_ID(+)=J.JOB_ID
AND E.JOB_ID(+)='HR REP'
ORDER BY E.DEPARTMENT_ID
/

JOB_ID	JOB_TITLE	DEPARTMENT_ID	JOB_ID	ENAME	DEPARTMENT_ID	DNAME
HR REP	HRR		10		HR REP	JONES	10		Administration
IT_PROG	Programmer	-		-	-	210		IT Support
HR REP	HRR		-		-	-	210		IT Support
IT_PROG	Programmer	-		-	-	10		Administration


4 rows returned in 0.00 seconds	        	Download


User_TDDEP

What about rewriting this one to ANSI JOIN e.g.
select ts.employee_id from JOB_HISTORY jh,
(SELECT E.*, D.*, J.*
FROM EMPLOYEES E, DEPARTMENTS D, JOBS J
WHERE E.DEPARTMENT_ID(+)=D.DEPARTMENT_ID
AND E.JOB_ID(+)=J.JOB_ID
AND E.JOB_ID(+)='FI_ACCOUNT'
AND E.LAST_NAME='Lorentz' --added cond.
) ts
where jh.employee_id(+)=ts.employee_id;

Frank Kulash

Hi, User_TDDEP
What about rewriting this one to ANSI JOIN e.g.
Here's one way, that matches the query you posted very closely:

SELECT    ts.employee_id
FROM	  (
	   SELECT   e.*
	   --	   ,		... add columns from tables d and j if wanted
	   FROM   	departments d
	   CROSS JOIN	jobs	    j
	   LEFT JOIN employees      e   ON  e.department_id = d.department_id
	   	    		        AND e.job_id  	    = 'FI_ACCOUNT'
	   			        AND e.job_id        = j.job_id
	   WHERE   e.last_name = 'Lorentz' --added cond.
	  ) ts
LEFT JOIN job_history jh ON jh.employee_id = ts.employee_id
;

It's interesting that the query you posted runs at all, since ts duplicate column names. For some reason, the query I posted raises a "ORA-00918: column ambiguously defined" error if ts has duplicate column names, even though those columns are not used anywhere in the query. If you really want to, you can add all the columns from d and j to the result set of ts, just make sure they have distinct aliases.
Again, since you're essentially doing an inner join with the employees table, the query above is just a convoluted way of saying:

SELECT   e.employee_id
FROM	 departments  d
JOIN	 employees    e  ON  e.department_id  = d.department_id
	   	         AND e.job_id	      = 'FI_ACCOUNT'
JOIN   jobs           j  ON  j.job_id         = e.job_id
LEFT JOIN job_history jh ON  jh.employee_id   = e.employee_id
WHERE   e.last_name = 'Lorentz' --added cond.
;
1 - 6
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jul 19 2002
Added on Jun 21 2002
2 comments
250 views