Re: SQL*Plus question

Harry Boswell wrote:
> On 31 Jan 2005 07:44:13 -0800, "Andy Kent"
<andykent.bristol1095_at_virgin.net>
> wrote:
>
> >I've got a really simple requirement.
> >Basically all I want to do is:
> >
> >select * from table1 where key=value;
> >if found
> >display it;
> >else
> >select * from table2 where key=value;
> >display it;
> >end if;
> >
> >I want to do it in the simplest possible .sql script so I can just
> >invoke it from SQL*Plus. Should be standing-on-your-head stuff but
I'm
> >struggling to get my head around the interaction between SQL*Plus,
SQL
> >and PL/SQL in this.
> >What should the script look like?
> >
>
> Off the top of my head, PL/SQL - something like
>
> begin
> select * from table 1 where key = value;
> exception
> when no_data_found then
> select * from table 2 where key = value;
> end;
>
>
> Harry B

Without an INTO Clause the selects error out. Maybe this is what you had in mind:

declare
myrec1 table1%rowtype;
myrec2 table2%rowtype;
begin
select * into myrec1 from table1 where key = value; dbms_output.put_line(myrec1.col1||' '||myrec1.col2 ...); exception
when no_data_found then
select * into myrec2 from table2 where key = value; dbms_output.put_line(myreec2.col1||' '||myrec2.col2 ...); end;
/

Of course this isn't as elegant as the UNION example alread posted, which I prefer over PL/SQL as it doesn't requre gyrations to display the proper output.

David Fitzjarrell Received on Mon Jan 31 2005 - 11:46:13 CST