ORA-01036: illegal variable name/number

ORA-01036: illegal variable name/number

Hi all,

I am getting the 'ORA-01036: illegal variable name/number' when I test execute my query through an e-space. If I run it in SQL Developer, the same query runs without problems. The query has input parameters which I am supplying during run time.
I am supplying the same values when I test the query in the Advanced Query editor that I am supplying when I run the query in SQL Developer.
NB: The query has 13 input parameters and outputs to a structure that has 15 attribute variables.

Hello Shupi.

Can you show us your query and the values you are passing?

Hi Joao

I have attached the query in a text file as it is to long to paste here.



Hello Shupi

That's not a long query .. it's huge! :)  But no worries, we get to the bottom of this.

The Oracle erro ORA-01036 means that the query uses an undefined variable somewhere. From query, we can determine which variables are use: all that start with @. However, if you're inputting this into an advacned query, it's importante to confirm that all variavles have a matching input parameter, including the same case in the variable name, if your Oracle database is Case Sensitive.

Can you check that and let us know your findings?


Miguel Simões João

Hi Miguel,

Yes, our database is case sensitive.

Thanks for your reply.




Wow, What I would in this case is to split up the unions into seperate advanced queries to see if they work seperately.

So check that the case of the variables defined in the advanced query input parameters match exactly with the all variables defined in the query text.

Also consider Joost suggestion, since those type of long queries simply don't scale, and may cause more problems that it solves.


Miguel Simões João
That happens because you add a parameter in the Database class and didn't use it in the query.

It can also be because of the comment (-- )
I would remove that comment from the query and try without it.

João Rosado