Re: Several question about select.

From: Eitan M <nospam_at_nospam_please.com>
Date: Sun, 29 Jan 2006 10:24:12 +0200
Message-ID: <drhu12$39h$1@news2.netvision.net.il>

OK.
For the newsgroup - you right.
I realy don't know what correct newsgroup, Now I sent only to comp.database.oracle.misc I hop this right.

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 :) Received on Sun Jan 29 2006 - 02:24:12 CST