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

It didn't work, unfortunateley. I couldn't even create the view. That generated the same error. You're right, there is no such function in the remote dastabase. The good news is that, it turns out that I won't be needing the function after all. So problem solved, sort of...

Thanks for your help,

Disa

Rüdiger J. Schulz <johannes.schulz_at_web.de> wrote in message news:<3bc1b06c$1_at_netnews.web.de>...
> 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
Received on Wed Oct 10 2001 - 07:21:10 CDT