Home » SQL & PL/SQL » SQL & PL/SQL » Exec Immediate question
Exec Immediate question [message #335482] Tue, 22 July 2008 05:23 Go to next message
saberfang
Messages: 11
Registered: June 2008
Location: M'sia
Junior Member
i m writing pl sql to handle dynamic condition for my insertion by comparing date.. but i got problem here cant be solved. need help from experts.

if something then
paymentWhere:=' and payment_date>'||pay_date;
else
paymentWhere:=' and payment_date>'||firstbilldate;
end if;

execute immediate 'insert into tempreconcilpayment(account_num,payment_date,payment_amt) select account,payment_date,payment_amt from trans where account=:1 '||paymentWhere||' order by payment_date' using account;

the error will be something like:

18:09:57 Error: ORA-00904: "JUL": invalid identifier
ORA-06512: at line 60
line 60 refer to the exec immediate line.
issit because i comparing date in a wrong way?
Re: Exec Immediate question [message #335484 is a reply to message #335482] Tue, 22 July 2008 05:35 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> issit because i comparing date in a wrong way?
Dates? Although you did not posted the type of PAY_DATE and FIRSTBILLDATE, it does not matter as they are converted into string during concatenation with WHERE condition. Moreover, as you did not enclose it between single quotes, it is interpreted as identifier.

When working dynamic SQL: always print the command string before execution, so you will be able to find the error.

In this example, why do you not bind it as ACCOUNT parameter (create a new variable, which is assigned either PAY_DATE or FIRSTBILLDATE depending on SOMETHING).
Re: Exec Immediate question [message #335489 is a reply to message #335484] Tue, 22 July 2008 05:43 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Your date format is not correct.Try using TO_CHAR,TO_DATE
while comparing.

Regards,
Rajat
Re: Exec Immediate question [message #335501 is a reply to message #335489] Tue, 22 July 2008 06:19 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Also, why the hell would you use the dynamic SQL when

declare
v_payment_cut date;

.....

if <something> then
  v_payment_cut := pay_date;
else
  v_payment_cut := firstbilldate;
end if;

insert into tempreconcilpayment(
         account_num, payment_date, payment_amt
) select account    , payment_date, payment_amt 
    from trans 
   where account= account 
    and  payment_date > v_payment_cut;

.....


would work also without the headaches that WILL come to haunt you.

OH, and using an order by in an insert is pointless.

[Updated on: Tue, 22 July 2008 06:20]

Report message to a moderator

Re: Exec Immediate question [message #335531 is a reply to message #335482] Tue, 22 July 2008 08:19 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
where account= account

This condition will be always true, which probably is not intended.
It will be necessary to rename ACCOUNT variable (e.g. add prefix V_).
But, definitely, this approach is much better than dynamic SQL.
Re: Exec Immediate question [message #335908 is a reply to message #335482] Thu, 24 July 2008 02:46 Go to previous messageGo to next message
saberfang
Messages: 11
Registered: June 2008
Location: M'sia
Junior Member
sry i dint interpret my problem clearly.

anyway i took the suggest that dont use the dynamic SQL already.
Save it to a variable is easier.

but i just wonder how i m going to compare the date if i do it in exec immediate?

i tried include also to_date() to the dynamic string but it still cant work.
Any idea how if i wanna compare date in exec immediate?an short example will be appreciated

Re: Exec Immediate question [message #335917 is a reply to message #335908] Thu, 24 July 2008 02:56 Go to previous message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't concatenate your constant in SQL string, use bind variables.
Read EXECUTE IMMEDIATE section in PL/SQL guide.

Regards
Michel
Previous Topic: Need help on using join
Next Topic: Flag field based on max(date)
Goto Forum:
  


Current Time: Tue Dec 06 02:33:25 CST 2016

Total time taken to generate the page: 0.15542 seconds