Re: pl/sql-function not working in select statement - generates PLS-907: cannot load library unit

I'm not really sure, but try this:

   create view view_test
   as

      SELECT forskola_sid, 
             get_arskurs(person_sid) as getarskurs
      from buv_owner.fskinskrivning_at_buv;

and then:

   select * from view_test where getarskurs = 2;

I think the problem is, that your original select-statement is trying to get the function "get_arskurs()" from the remote database, but there is no function like this, right?!.

let me know, if it works or not...
greetings
Rüdiger
(eMail: rjs_at_berlin.de)

disa1066_at_hotmail.com (disa) wrote:
>I have a pl/sql function - get_arskurs(xperson_sid number) - that
>works when I call it from a procedure and send in a hardcoded value
>like this...
>
>procedure functest is
> arskurs number := 0;
> stmt dbms_sql.varchar2s;
>
>begin
> arskurs := get_arskurs(5514);
> dbms_output.put_line('Value: ' || arskurs);
>end;
>
>SQL> exec functest
>Value: 2
>
>PL/SQL procedure successfully completed.
>
> ... but when I use the function in a select statement like this (the
>way that I want to use it)...
>
>SELECT forskola_sid
>from buv_owner.fskinskrivning_at_buv
>where get_arskurs(person_sid) = 2;
>
> ...it generates the error message PLS-907: cannot load library unit
>BUV_OWNER.KODARSKURS_at_BUV.WORLD (referenced by B20_OWNER.GET_ARSKURS)
>
>The function itself looks like this:
>
>FUNCTION get_arskurs (xperson_sid number) RETURN number IS
> xarskurs varchar2(2) := null;
> narskurs number;
> cursor carskurs is
> select arskurs from vgskelevuppgiftutdrag e
> where e.person_sid=xperson_sid
> and e.kodarskurs_sid=(select sid from buv_owner.kodarskurs_at_buv
>where nvl(pagaende,'N')='J')
> and e.arskurs in ('0','1','2','3','4','5','6','7','8','9','10');
>BEGIN
> open carskurs;
> fetch carskurs into xarskurs;
> if carskurs%NOTFOUND then
> xarskurs := null;
> narskurs :=2;
> end if;
> close carskurs;
> if xarskurs = 0 then
> narskurs := 3;
> end if;
> if xarskurs > 0 then
> narskurs := 4;
> end if;
> return(narskurs);
>END;
>
>The database link obviously works. I have tried a "select count(*)..."
>against BUV_OWNER.KODARSKURS_at_BUV.WORLD, which also works. The function
>has worked before and it is valid now. The table it references on the
>remote database has not been altered as far as I can tell. I can't
>figure out what the problem is. I'd be grateful for any help.
>
>Thanks,
>
>Disa

-- 
__________________________________________________________
News suchen, lesen, schreiben mit http://newsgroups.web.de