Re: pl/sql PROBLEM WITH VARIABLES IN EXECUTE IMMEDIATE

On Apr 18, 9:27 am, mlachajc..._at_gmail.com wrote:
> Hi,
>
> I have got litte problem with pl/sql and function:execute immediate .
>
> Example, it is a script:
>
> my variable:='2007-01-07'; <----- my variable
>
> --
> SQL_:= 'create table imago_test SELECT PART_NO,
> SUM(DECODE(DIRECTION,''+'',QUANTITY,''-'',-QUANTITY,0)) AS
> ILOSC,
> ROUND(SUM(DECODE(DIRECTION,''+'',QUANTITY*COST,''-'',-
> QUANTITY*COST,0)),4) AS WARTOSC
> FROM ifsapp.INVENTORY_TRANSACTION_HIST2
> WHERE LOCATION_NO in (''MG'',''MW'',''MG1'')
> AND PART_NO LIKE ''071-001-001%''
> AND DATE_APPLIED<=NVL(TO_DATE('''||my variable||''',''YYYY-
> MM-DD''),SYSDATE)
> GROUP BY PART_NO';
>
> execute immediate SQL_;
>
> and it gives me error message: error number or value
>
> pls let me now asap
>
> thx

You cannot have spaces in a variable name. And the variable needs to be declared with a known type. If this is some thrown together 'example' which doesn't reflect what you're actually doing then either provide a more realistic example or prepare yourself for useless 'answers'.

David Fitzjarrell Received on Wed Apr 18 2007 - 10:18:31 CDT