Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: New member / outer join problem

RE: New member / outer join problem

From: Stephane Faroult <>
Date: Thu, 06 Nov 2003 04:44:27 -0800
Message-ID: <>


  Review your logic. A row full of NULLs has no signification. If your statement is embedded into either some PL/SQL code or a 3rd generation language, Oracle will generate a 'no data found' error, and this is what you need to trap.   For instance in PL/SQL


     select c1, c2, c3
     into var1, var2, var3
     from some_table
     where some_condition;
     when no_data_found yhen
         var1 := null;
         var2 := null;
         var3 := null;


   At this point, you have what you want into var1 ... varn.  Assuming that the query may return several rows, you can do a BULK COLLECT into arrays.

  HTH,   SF

>----- ------- Original Message ------- -----
>From: <attila.mosolygo_at_GARANCIA.HU>
>To: Multiple recipients of list ORACLE-L
>Sent: Thu, 06 Nov 2003 04:04:38
>Hi All,
>I'm a new member on this list. I'm not too familiar
>with ORA, but I have some experience in other
>I had run into the following problem.
>My task is to write a query, which returns with an
>empty (full of NULLs) row, if the condition is
>I'm thinking in outer joins.
>I made a test. It returns all the matching rows (if
>there are such rows), but unfortunately returns
>with no row, if the condition is false.
>Could someone help me, what could be the problem?
>My oppinion is that it must be work fine... :-)
>I use Oracle9i Enterprise Edition Release
>My test is here:
>create table ATTILA_1 (m1 varchar2(10));
>insert into attila_1 values('a');
>insert into attila_1 values('a');
>insert into attila_1 values('b');
> dual left outer JOIN ATTILA_1 b
> ON (b.m1='c');
>It returns correctly (with 2 or 1 rows) if the
>string in the condition is 'a' or 'b', but returns
>with no row (I'd like to see one row, with full of
>NULLs) if I call it with 'c'... :-(
>Could you tell me, why is this? Or any idea to
>solve this task?
>Thanks in advance:
>Please see the official ORACLE-L FAQ:
>Author: <attila.mosolygo_at_GARANCIA.HU
> INET: attila.mosolygo_at_GARANCIA.HU

Please see the official ORACLE-L FAQ:
Author: Stephane Faroult

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message
to: (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Thu Nov 06 2003 - 06:44:27 CST

Original text of this message