Execute Non Query : ORA-01036: illegal variable name/number

Hi
Today when i was trying to call a procedure with EXECUTE NON QUERY activity it was throwing error like Execute Non Query : ORA-01036: illegal variable name/number
i saw related topics in forum, but even though on trying them, the error still persists,
I cross checked these details
–the procedure has one IN parameter and one OUT parameter
IN parameter is of type varchar2 and out parameter is of type integer,
–with respect to that in our activity have set IN argument with type String and out with int32

Code used:
create or replace procedure rpa_num_conv (p_name IN varchar2,p_value_n out Integer) is
begin
if p_name=‘Naresh’ then
p_value_n:=1;
else
p_value_n:=0;
end if;
end rpa_num_conv;

version is 2018.2
enterprise edition
database is oracle server

Cheers

2 Likes

Hey @Palaniyappan

I think this is the first time I’m seeing a question from you… :slight_smile:

Bro, As you know I have a little background on databases… I checked this error and this error occur when you are trying to bind variables in places it is not allowed. So in oracle, the statement you have used “Create or replace procedure” is considered as a DDL (Data Definition Language) which actually modifies the structure of an object in the database. So for these, you don’t really need to pass any variables.

You only need to pass any arguments through the activity only when you are executing the procedure…

Make sense?

This link gives a little bit of detail on what I just told…

1 Like

yes buddy i have passed the same here while calling the procedure with EXECUTE NON QUERY ACTIVITY
like this
image

and i saw this post at initial stage and even referred with oracle docs… but i m still facing this error, and the most interesting thing is even same structure of procedure was used in some other process and it was working with same set of parameters

i m sure its not the issue with Execute non query activity and is purely with the procedure used…kindly correct me if i m wrong

Cheers @Lahiru.Fernando

1 Like

hmmm… That’s weird… :thinking:

Bro, can you show me a screenshot of the code you used in the execute non query activity? Just wanna see how you have called the procedure…

1 Like

Sure
image

schemaname.procedurename
Cheers @Lahiru.Fernando

1 Like

Hi @Palaniyappan,
Do we need the stored procedure name in the last line, I think a simple END is enough.

1 Like

yah as single procedure is called within this block that would be fine either
but even with that name it got compiled and the code was working fine when ran manually from server with values passed

Cheers @shankm

2 Likes

Have you referred to this link bro

1 Like

aaah,
i wonder why i missed the property CommandType, i think my right eyes didnt work :smile:
it was set to text, it should be StoredProcedure

now working fine
thanks buddy @shankm @Lahiru.Fernando
Cheers

3 Likes

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.