Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: plsql dynamic sql - weird error in repeated execution where contents differ in each loop

Re: plsql dynamic sql - weird error in repeated execution where contents differ in each loop

From: Jan Gelbrich <j_gelbrich_at_westfalen-blatt.de>
Date: Thu, 31 Jul 2003 13:58:44 +0200
Message-ID: <bgb0di$mfmt4$1@ID-152732.news.uni-berlin.de>


suggestion ...

/* original
execute immediate 'select '

   || sFunc || sThisColumn || sEndFunc || ' '
   || 'from ' || sThisTable  || ' '
   || 'where ' || sThisColumn || ' ' || ' = :b1 '
into sThisColumnValue
using recContactAudit.LENTITYINSTANCEKEY ; */

--You use sThisColumn 2 times, so You have to apply the function 2 times also
--comments inline
execute immediate 'select '

   || sFunc || sThisColumn || sEndFunc || ' '
   || 'from ' || sThisTable  || ' '
   || 'where ' || sFunc || sThisColumn || sEndFunc || ' ' || ' = :b1
  --and take care of the type of :b1, too ... into sThisColumnValue --... and what data type is this ? another possibility of type mismatch ...
using recContactAudit.LENTITYINSTANCEKEY ;

hth, Jan

"M Buckle" <markbuckle30_at_hotmail.com> schrieb im Newsbeitrag news:d3868cb5.0307310152.d1056c8_at_posting.google.com...
> I have a feeling i might know whats going on, but can any-one answer this
anyway ?

>

> Code :-
>

> I'm trying to select a different column ( may be char or non-char )
> into a char variable each time around the loop. When the actual database
column
> is a char type i get a :-
>

> ORA-01722: invalid number
>

> error.
>
>

> code extract is :-
>

> loop
>

> ....................
>

> if lThisDataType = 2 /* numeric */
> then
> sFunc := ' ' ;
> sEndFunc := ' ' ;
> else
> sFunc := 'to_char(' ;
> sEndFunc := ')' ;
> end if ;
>

> execute immediate 'select ' || sFunc ||
> sThisColumn
> || sEndFunc || ' ' ||
> 'from ' || sThisTable || ' ' ||
> 'where ' || sThisColumn || ' ' || ' = :b1 '
> into sThisColumnValue
> using recContactAudit.LENTITYINSTANCEKEY ;
>

> ...........
>

> end loop
>
>

> Thanks, Mark.
Received on Thu Jul 31 2003 - 06:58:44 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US