Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!newsfeed.media.kyoto-u.ac.jp!newsfeed.gamma.ru!Gamma.RU!newsfeed.icl.net!newsfeed.fjserv.net!newsfeed.cw.net!cw.net!news-FFM2.ecrc.de!news.germany.com!feed.xsnews.nl!feeder.xsnews.nl!sn-ams-06!sn-xt-ams-05!sn-post-ams-01!supernews.com!corp.supernews.com!not-for-mail
From: Sybrand Bakker <postbus@sybrandb.demon.nl>
Newsgroups: comp.databases.oracle.misc
Subject: Re: INSTR problem into procedure
Date: Tue, 06 Jun 2006 17:44:25 +0200
Organization: Posted via Supernews, http://www.supernews.com
Message-ID: <nk8b829gssefqo6kikhbr1m8e8dtm72458@4ax.com>
Reply-To: postbus@sybrandb.demon.nl
References: <1149599438.178910.210290@j55g2000cwa.googlegroups.com>
X-Newsreader: Forte Agent 3.3/32.846
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Complaints-To: abuse@supernews.com
Lines: 70
Xref: dp-news.maxwell.syr.edu comp.databases.oracle.misc:127779

On 6 Jun 2006 06:10:38 -0700, "Alen Cappelletti" <cocker@libero.it>
wrote:

>In this procedure if there is the word "&ID=" into strQueryString
>variable IN must be recall an external procedure.
>
>I try to compile this code and always have an PLS error...:
>PLS-00382 exprerssion is of wrong type at line 59
>---------
>IF INSTR(strQueryString,'&ID=') THEN
>---------
>
>I dont't understand the problem 'cause in my SQL software a simple
>query with INSTR is perfectly on.
>Thaks Alen
>------------------
>PROCEDURE HaiVisto(
>		strCookieMD5 varchar2,
>		strUrl varchar2,
>		strQueryString varchar2,
>		strTitoloPagina varchar2,
>		intArea number,
>		OBJ_CUR_HaiVisto OUT CUR_HaiVisto
>		)
>        AS
>        BEGIN
>        	OPEN OBJ_CUR_HaiVisto FOR
>			SELECT
>			*
>			FROM
>			(
>			SELECT
>				MAX(un.IDUTENTENAVIGAZIONE) ID,
>				CASE TO_CHAR(NVL(MAX(un.QSTRING),1))
>					WHEN '1' THEN MAX(un.URLPATH)
>					ELSE MAX(un.URLPATH)||'?'||MAX(un.QSTRING)
>				END QS,
>				CASE SIGN(LENGTH(MAX(un.TITOLOPAGINA))-20)
>					WHEN -1 THEN MAX(un.TITOLOPAGINA)
>					WHEN 1 THEN SUBSTR(MAX(un.TITOLOPAGINA),1,17)||'...'
>					ELSE MAX(un.TITOLOPAGINA)
>				END Titolo,
>				MAX(a.ICONA) Icona
>			FROM UTENTI u
>			JOIN UTENTIAPPOGGIONAVIGAZIONE uan ON (u.IDUTENTE = uan.IDUTENTE)
>			JOIN UTENTINAVIGAZIONE un ON (u.IDUTENTE = un.IDUTENTE)
>			JOIN AREE a USING (IDAREA)
>			WHERE (u.STATO = 1 AND u.SOSPESO = 0)
>			AND
>TO_CHAR(CriptaMD5(uan.IDUTENTEAPPOGGIONAVIGAZIONE||uan.IDUTENTE||uan.WEBSESSIONID))
>= strCookieMD5
>			GROUP BY un.URLPATH,un.QSTRING
>			ORDER BY ID DESC
>			)
>			WHERE rownum <= 10;
>
>			--Se c'è un ID....
>        	IF INSTR(strQueryString,'&ID=') THEN
>
>		UTENTENAVIGAZIONE(strCookieMD5,strUrl,strQueryString,strTitoloPagina,intArea);
>        	END IF;
> 		END HaiVisto;

set define off 

or replace the & by chr(<whatever the numerical value of & is>, can be
obtained by select asc('&') from dual provided define is off)

--
Sybrand Bakker, Senior Oracle DBA