Execute Immediate n .. [message #37593] |
Thu, 14 February 2002 23:48 |
diaz
Messages: 58 Registered: October 2001
|
Member |
|
|
hi, i was trying to do this :
DECLARE
/* eop dynamically declared outside of the code */
/* payment is the sum of payment done by the customer */
/* mysaldo is the deposit owned by the customer */
thiseop VARCHAR(6) := &1;
regional varchar(2):= &2;
paydate date;
tab_tg varchar(10);
tab_pb varchar(12);
type sqlcur is REF cursor;
sqlstmt varchar(200);
id varchar(12);
eop varchar(6);
jumlah number(9);
mypayment number(9);
sql_payment varchar(200);
c1 sqlcur;
--
function rtn_cursor (sqlstmt varchar) return types.sqlcur as
mycursor types.sqlcur;
begin
open mycursor for sqlstmt;
return mycursor;
end;
--
BEGIN
tab_tg := 'tagihan' || regional;
sqlstmt := 'select * from '||tab_tg || ' where eop='''||thiseop ||'''' ;
c1 := rtn_cursor(sqlstmt);
loop
fetch c1 into id,eop,jumlah,paydate;
EXIT WHEN c1%NOTFOUND;
dbms_output.put_line(id);
sqlpayment:= 'select sum(jumbayar) into mypayment from ' ||tab_pb|| ' where
to_char(tglbayar,'YYYYMM')=thiseop and msisdn=id';
execute immediate sqlpayment;
dbms_output.put_line (mypayment);
end loop;
END;
/
... i don't know how to fix this error :
to_char(tglbayar,'YYYYMM')=thiseop and msisdn=id';
*
ERROR at line 37:
ORA-06550: line 37, column 21:
PLS-00103: Encountered the symbol "YYYYMM" when expecting one of the following:
. ( * @ % & = - + ; < / > at in mod not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like
between is null is not || is dangling
The symbol ". was inserted before "YYYYMM" to continue.
can somebody help me ?
|
|
|
Re: Execute Immediate n .. [message #37607 is a reply to message #37593] |
Fri, 15 February 2002 08:22 |
Todd Barry
Messages: 4819 Registered: August 2001
|
Senior Member |
|
|
Change those single quotes around YYYYMM to dual single quotes:
''YYYYMM'' (note these are not quote marks)
This is needed because you are embedding this string within another string.
|
|
|