Block based on a stored procedure

From: Laurent <lbouyer_at_sin-et-stes.fr>
Date: 2000/05/16
Message-ID: <39217368.6F0C8A7E_at_sin-et-stes.fr>#1/1


[Quoted] I always get the message 'FRM-40505 Unable to perform query' without any [Quoted] 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 Received on Tue May 16 2000 - 00:00:00 CEST

Original text of this message