Re: Block based on a stored procedure

I see nothing wrong with your server side programs except one small point: the order of columns in the select list should be same as that in ref cursor definition. Whith what you written, it's very likely that CHANTIER value will be put into secteur ...

I guess the problem is in your client side setting. You can get more meaningful message by pressing Shift+F1 when you get the error message 'FRM-40505 Unable to perform query'

hth

In article <39217368.6F0C8A7E_at_sin-et-stes.fr>,   lbouyer_at_free.fr wrote:
> I always get the message 'FRM-40505 Unable to perform query' without
 any
> oracle message number when I try to query on a block based on this
> procedure :
>
> create or replace PACKAGE PK_RETRV_ARBO IS
> TYPE kimrec is RECORD (
> secteur r_dpmt.dpmtexcde%TYPE,
> district r_dpmt.dpmtexcde%TYPE,
> site r_dpmt.dpmtexcde%TYPE,
> chantier r_dpmt.dpmtexcde%TYPE);
> TYPE kimcur is REF CURSOR RETURN kimrec;
> PROCEDURE PROG_RETRV_KIMOCE (resultset IN OUT kimcur, v_request IN
> VARCHAR2);
> END;
>
> create or replace PACKAGE BODY PK_RETRV_ARBO IS
> PROCEDURE PROG_RETRV_KIMOCE (resultset IN OUT kimcur, v_request in
> VARCHAR2) IS
> v_recherche VARCHAR2(80) := v_request;
> BEGIN
> --message('Entrée dans la procédure');
> IF v_request IS NULL THEN v_recherche := '%';
> ELSE
> v_recherche := v_request || '%';
> END IF;
> --Message(v_recherche, ACKNOWLEDGE);
> OPEN resultset FOR
> SELECT ALL CHANTIER.DPMTEXCDE CHANTIER,
> SITE.DPMTEXCDE SITE, DISTRICT.DPMTEXCDE DISTRICT, SECTEUR.DPMTEXCDE
> SECTEUR
> FROM SINESTES.R_DPMT SECTEUR, SINESTES.R_DPMT DISTRICT,
> SINESTES.R_DPMT SITE, SINESTES.R_DPMT CHANTIER
> WHERE SECTEUR.DPMTINCDE<>0
> AND ((CHANTIER.MAINDPMTINCDE = SITE.DPMTINCDE)
> AND (SITE.MAINDPMTINCDE=DISTRICT.DPMTINCDE)
> AND (DISTRICT.MAINDPMTINCDE=SECTEUR.DPMTINCDE))
> AND CHANTIER.DPMTEXCDE LIKE v_recherche
> ORDER BY SECTEUR.DPMTEXCDE ASC;
> END;
> END;
>
> If someone could help me, I'd be very happy !
>
> Thanks
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed May 17 2000 - 00:00:00 CEST