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@xxxxxxxxxxxxxxxx
(replace x with u to respond)
.

Relevant Pages

  • Re: Several question about select.
    ... I realy don't know what correct newsgroup, ... package mypackage is ... res number; ...
    (comp.databases.oracle.misc)
  • Re: Import multiple mdb files to SQL Server issue: Please Help...
    ... What is probably happening is that the step I mention DTSStep_DTSActiveScriptTask_3 is not the same in your package. ... "Allan Mitchell" wrote: ... I know the file extension .res doesn't look like MDB but it is. ...
    (microsoft.public.sqlserver.dts)
  • Re: alists and packages question
    ... I am trying to access elements of an alist from a "derived" package. ... (if (not res) ... the symbol H in the ATOMIC-PROCESSES package. ... package qualifier, just exporting the key symbols would remove the ...
    (comp.lang.lisp)
  • Re: alists and packages question
    ... I am trying to access elements of an alist from a "derived" package. ... I operate inside another package that uses:atomic-processes ... ;; (defun rel-polarizability (id) ... ;; (if (not res) ...
    (comp.lang.lisp)
  • Re: importing external class from inside package
    ... we write the statement - 'package mypackage' in each of those ... Then we complied all the java files: ... of MyInterface class in the First.java. ...
    (comp.lang.java.help)