Re: INSTR problem into procedure

On 6 Jun 2006 06:10:38 -0700, "Alen Cappelletti" <cocker_at_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