Block based on a stored procedure
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