By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,919 Members | 1,074 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,919 IT Pros & Developers. It's quick & easy.

merge statement

P: 1
Hello,

I'm trying to use MERGE statement in a procedure, please see the code below:

procedure gen_nou (p_data date) is
v_atribut_id number;

begin
v_atribut_id=100;

merge into TABLE1 x
using (select distinct id, 'D' nou from TABLE2
where data=p_data) a
on (x.atribut_id=v_atribut_id
and x.id=)
when matched then update set valoare=a.nou
when not matched then
INSERT (date_created, created_by, date_modified, modified_by, id, atribut_id, valoare)
VALUES( sysdate, user, sysdate, user, a.id, v_atribut_id, a.nou);
commit;

end;

If I try to execute the procedure
begin
gen_nou ('31-jan-2007');
end;

I get the following error:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER

If I modify the procedure as below the procedure can be executed without errors:

procedure gen_nou (p_data date) is
v_atribut_id number;

begin
v_atribut_id=100;

merge into TABLE1 x
using (select distinct id, 'D' nou from TABLE2
where data='31-jan-2007'
) a
on (x.atribut_id=v_atribut_id
and x.id=)
when matched then update set valoare=a.nou
when not matched then
INSERT (date_created, created_by, date_modified, modified_by, id, atribut_id, valoare)
VALUES( sysdate, user, sysdate, user, a.id, v_atribut_id, a.nou);
commit;

end;

Could you help me to understand why it fails when I try to set with parameter p_data in MERGE statement?

Thank you!!!
Maria
Feb 28 '07 #1
Share this Question
Share on Google+
5 Replies


100+
P: 153
Could you provide the table1 and table2 DDL.
Feb 28 '07 #2

100+
P: 153
Just as a followup. i suspect the issue between the 2 procedures is the following:
in the first statement you are comparing a date parameter to table2.data
and in the second you are comparing a string (varchar2) to table2.data

using (select distinct id, 'D' nou
from TABLE2
where data=p_data) a

using (select distinct id, 'D' nou
from TABLE2
where data='31-jan-2007' ) a

so if the second procedure works i suspect it is because table2.data is of type varchar2 or char. so you would need to use something like the following:

using (select distinct id, 'D' nou
from TABLE2
where data=to_char(p_data,'dd-mon-yyyy') a

assuming '31-jan-2007' is the correct format.
Feb 28 '07 #3

P: 16
Hello,

I'm trying to use MERGE statement in a procedure, please see the code below:

procedure gen_nou (p_data date) is
v_atribut_id number;

begin
v_atribut_id=100;

merge into TABLE1 x
using (select distinct id, 'D' nou from TABLE2
where data=p_data) a
on (x.atribut_id=v_atribut_id
and x.id=)
when matched then update set valoare=a.nou
when not matched then
INSERT (date_created, created_by, date_modified, modified_by, id, atribut_id, valoare)
VALUES( sysdate, user, sysdate, user, a.id, v_atribut_id, a.nou);
commit;

end;

If I try to execute the procedure
begin
gen_nou ('31-jan-2007');
end;

I get the following error:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER

If I modify the procedure as below the procedure can be executed without errors:

procedure gen_nou (p_data date) is
v_atribut_id number;

begin
v_atribut_id=100;

merge into TABLE1 x
using (select distinct id, 'D' nou from TABLE2
where data='31-jan-2007'
) a
on (x.atribut_id=v_atribut_id
and x.id=)
when matched then update set valoare=a.nou
when not matched then
INSERT (date_created, created_by, date_modified, modified_by, id, atribut_id, valoare)
VALUES( sysdate, user, sysdate, user, a.id, v_atribut_id, a.nou);
commit;

end;

Could you help me to understand why it fails when I try to set with parameter p_data in MERGE statement?

Thank you!!!
Maria

try this for your call


begin
gen_nou (to_date('31-jan-2007','yy-mon-yyyy'));
end;
Mar 2 '07 #4

P: 16
try this for your call


begin
gen_nou (to_date('31-jan-2007','yy-mon-yyyy'));
end;

sorry the call is

B]begin
gen_nou (to_date('31-jan-2007','dd-mon-yyyy'));
end;[/b][/quote]
Mar 2 '07 #5

100+
P: 153
sorry the call is

B]begin
gen_nou (to_date('31-jan-2007','dd-mon-yyyy'));
end;[/b]
[/quote]

the error message leads me to beleive that the statement was trying to compare to different data types.

usually if the call of a function or procedure is wrong you get the " Wrong number or types in call to..."
Mar 3 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.