Re: Several question about select.

Eitan M wrote:

> For anyone who like to know how I use stored proc / packages.
> for mypackage :
> package mypackage is
> function x(a in number) return number;
> pragma restrict_references(x,WNDS,WNPS);
> end package;
> ...
> package body mypackage is
> function x(a in number) return number is
> res number;
> cursor c is
> select 1 from dual;
> begin
> res:= null;
> open c;
> fetch c into res;
> if res is null then
> res:= 0;
> end if;
> close c;
> return res;
>
> end;
> end package
>
> of course :
> create public synonym mypackage FOR myinstance.mypackage
> and
> grant execute on mypackage to use_role.
>
> And in sql statement I do :
> select mypackage.x(1) from dual;
>
> I know also that package can return a table - I don't know how using it at
> select statment
> (maybe this is the solution - the result is a view with parameters).
>
> The major problem is,
> that I have a fixed select statement (which is built in one program)
> and another program which uses the select statement as is,
> but can put only a select statement at the end.
> I don't have the source of the other program (that put the where statment).
>
> but here is the major problem :
> when I use the main select statement, I have also some packages, which I
> have written to be used in the sql statement.
> The package use some parameters (clients).
> I cannot send the parameters just as they are, because, the parameter I sent
> should be in the query.
> and the only way to send parameters (the end user) is by a where statement.
>
> I need some tricky way to solve the problem :
>
> Select a as x, mypackage.fun1(x)
> from myTable
>
> ...
> the above is fixed.
> The user add :
> where x = 123
> ...
>
> and the select statment will be as I did :
> Select a as x, mypackage.fun1(123)
> from myTable
> where x = 123
>
> I didn't find any way to do so.
>
> Need an example, or tricky way to do the above.
>
> Thanks :)

I have no idea what you are trying to do or what question you are asking. I also can't see any point in any of the code you provided. For example look at this code you supplied:

 >function  x(a in number) return number is
 > res number;
 > cursor c is select 1 from dual;
 > begin
 >   res:= null;  -- this does precisely nothing.
 >   open c;
 >  fetch c into res;
 >  if res is null then
 >     res:= 0;
 >  end if;
 >  close c;
 >  return res;
 >
 >   end;

Let me rewrite your function for you in one line: res := 1;
you pass in a parameter "a" and then ignore it. you define a cursor and open it without purpose. you have an IF for an impossible condition.

Do you have any qualifications to write PL/SQL?

If one of my students, on their midterm, wrote this I would suggest they leave the program.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)