Re: PL/SQL

"Richard Kuhler" <noone_at_nowhere.com> wrote in message news:UcKm8.630$pZ6.903848_at_twister.socal.rr.com...
> I think Laurent is referring to the fact that you can't create the
> synonym in the same piece of code that refers to it (because the
> executing code has it locked). However, you could have a different
> routine create the synonym before the routine that references it is run.
>
> Richard
>
> Thomas Kyte wrote:
> >
> > In article <a7eoh1$efc$1_at_reader1.imaginet.fr>, "Laurent says...
> > >
> > >ho! ho!
> > >Your solution might interest me....
> > >Would you mind giving an exemple please ????
> >
> > I assume the ho ho it because you know that what is suggested is not
possible...
> >
> > anyway, you want to use a dynamically opened ref cursor if you truly
need this
> > level of genericity.
> >
> > declare
> > type rc is ref cursor;
> >
> > l_cursor rc;
> > l_string varchar2(30000);
> > begin
> >
> > l_string := .....;
> > open l_cursor for l_string;
> > loop
> > fetch l_cursor into l_variables....;
> > exit when l_cursor%notfound;
> >
> > >
> > >Thanks very much in advance....
> > >Laurent
> > >
> > >"Richard Kuhler" <noone_at_nowhere.com> a écrit dans le message de news:
> > >6Zom8.53$Q2.165197_at_twister.socal.rr.com...
> > >> It's difficult to say what the best solution is without seeing the
whole
> > >> picture. However, you might consider dynamically creating a private
> > >> synonym at the beginning of the code to point to the correct table.
> > >> Then just use the synonym everywhere in the rest of the code.
> > >>
> > >> Richard Kuhler
> > >>
> > >> Laurent Boutet wrote:
> > >> >
> > >> > Hi,
> > >> >
> > >> > Here is my question :
> > >> >
> > >> > In my stored proc, I copy 2 times likely the same code. I mean, the
code
> > >is
> > >> > just a bit different for instance :
> > >> >
> > >> > if param=1 then
> > >> > select * from table1;
> > >> > else
> > >> > select * from table2;
> > >> >
> > >> > This is a very basic code...but imagine I have hundred lines !!!
> > >> > If only I could do such a code :
> > >> > select * from table<param>
> > >> >
> > >> > So I would just have to set the param value...for instance, param=2
> > >then, I
> > >> > would have select * from table2;
> > >> >
> > >> > How can I do such a thing ??????
> > >> >
> > >> > I hope you understand my problem...
> > >> >
> > >> > Thanks in advance,
> > >> > Laurent
> > >>
> > >
> > >
> >
> > --
> > Thomas Kyte (tkyte_at_us.oracle.com) http://asktom.oracle.com/
> > Expert one on one Oracle, programming techniques and solutions for
Oracle.
> > http://www.amazon.com/exec/obidos/ASIN/1861004826/
> > Opinions are mine and do not necessarily reflect those of Oracle Corp
>

I suggest you read Thomas's answer again. With dynamic SQL you don't need to play around with synonyms.

Paul Received on Fri Mar 22 2002 - 22:03:53 CET