Re: PLEASE HELP with declaring variables in oracle DB

From: Jonathan Park <jpark_at_lims.co.kr>
Date: Mon, 17 Jun 2002 15:43:08 +0900
Message-ID: <aejvce$abr$1@news1.kornet.net>

thanks for the reply.. i figured it out..

i've developed a jsp web reservation system, and i am in the process of optimizing the code. my application used too many inefficient static sql queries which wasted lots of oracle DB resources.

for example,

select a,b,c from myTable where d=1, e=2, f=3;
select a,b,c from myTable where d=4, e=3, f=2;
select a,b,c from myTable where d=5, e=2, f=1;
select a,b,c from myTable where d=6, e=5, f=2;
..
..
..

..
..

what i wanted to do was convert all these into one dynamic statement as below.
oracle specialist recommended it, and he said by doing it this way, oracle will
save much time not having to check the syntax and parsing rules over and over again.

select a,b,c from myTable where d=&temp1, e=&temp2, f=&temp3

and in java, class called preparedStatement will just do that.

"Imprecise" <f_puhan_at_precise.com> wrote in message news:f_puhan-484E30.23093816062002_at_vienna7.his.com...
> In article <aejh6r$qvt$1_at_news1.kornet.net>,
> "Jonathan Park" <jpark_at_lims.co.kr> wrote:
>
> > hello all,
> >
> > i am having trouble declaring data in oracle DB.
> > i used
> >
> > define my-variable-name = 'hello oracle';
> >
> > but it seems to be bound to the session. ( data disappears when i log
out
> > and log in again ).
> >
> > how can i declare a variable to the DB table space permanantly?
> > does any one know?
>
> It's unclear as to what you are trying to do.
>
> If you want to declare a variable for use during script execution, then
> you have the correct basic approach. Your example could thus be
>
> variable my_variable_name varchar2(30)
>
> or
>
> define my_variable_name="hello oracle"
>
> However, as you have observed, the variable (thus the name "variable")'s
> scope is only for the duration of the session.
>
> If, on the other hand, you want to store data permanently in the
> database, you will need to first define and create the necessary objects
> (tables, indexes, views, triggers, etc.) and then INSERT the data,
> either programmatically or through the use of sqlldr.
>
> Hth
>
> --
> The underscore character does not belong in my address. You know the
drill...
> ***
> Anyone sufficiently smart enough to configure and use USEnet for research
should
> be smart enough to Read The Freakin' Documentation!
Received on Mon Jun 17 2002 - 01:43:08 CDT