fails with an ORA-01036: illegal variable name/number on 18c PRO*C

Noel McCusker

Hi,

We recently upgraded to Oracle 18c. We are getting problems running pro*c code now that used to work fine at 12c.

The errors seem to be around the use of indicator variables.

We are getting the error:

ORA-01036: illegal variable name/number

Oracle Database 18c Standard Edition 2 Release 18.0.0.0.0 - Production Version 18.7.0.0.0

Solaris 11.3 SPARC

Thanks for any help

Gaz in Oz

The full error text is:

SQL> @oerr ORA-0103601036, 00000, "illegal variable name/number"// *Cause: Unable to find bind context on user side// *Action: Make sure that the variable being bound is in the sql statement.

Did your recompile program using the 18c proc program and the 18c libraries?

User_ZJC9T

@Noel McCusker : Hi we are migrating from 11.2g to Oracle Cloud 19c. I got same error ORA-01036 error Illegal; variable name/number on 19c Proc*c during runtime. The code was working fine without any errors logged while run on 11.2g.

Could you please let me know if you are able to fix it. In my case we are passing host variables along with indicator variables to a stored procedure.

Noel McCusker

Hi, this is a know Oracle bug which will be fixed in a later release of the Database/Precompiler.

If you have access to the Oracle support forums the answer is the following doc_id

Proc Fails With ORA 1036 At Run time While Executing PLSQL With Indicator (Doc ID 2560421.1)

There are 2 solutions:

1. Install the patch mentioned in the above doc_id. This restores the behaviour of pro*c to 11.2g
2. Remove indicator variables - as much as you can from your  EXEC SQL EXECUTE block

In our case we did number 2 sucessfully. We created a load more local variables

so instead of

        IF :p_rco_points:p_rco_points_ind = 'Y'

        THEN

         :pts_val := :p_rul_points_value;

         IF :p_prq_result_type = 'M'

         THEN

           :pts_val := :pts_val * current_num_aan_value ;

         END IF ;

        /* Apply Factors if they exist */

        ELSIF :p_rco_factors = 'Y'

        THEN

We used

        IF l_rco_points = 'Y'

        THEN

         l_pts_val := l_points_value;

         IF l_result_type = 'M'

         THEN

           l_pts_val := l_pts_val * current_num_aan_value ;

         END IF ;

        /* Apply Factors if they exist */

        ELSIF l_rco_factors = 'Y'

        THEN

         OPEN get_rule_factor(l_que_refno,l_rul_refno);

         FETCH get_rule_factor

User_ZJC9T

Thank You so much @Noel McCusker.

User_ZJC9T

@Noel McCusker We got the patch from oracle and we installed it on our application server where we make (build) files. However, we still see the Error. We recompiled all our proc code after the patch is applied to our 32-bit Oracle Home on the app server.. Are there any other pointers which we have to check after the patch is applied?

Noel McCusker

I'd say log a P2 bug with Oracle support. This patch should return the functionality of the indicator variables back to the behaviour Oracle 11 precompiler. We did NOT use the patch, so we cannot comment on that. We removed (as much as was possible) most of the indicator variables from our code and used local variables in their place. This worked for us.

User_F0TWL

I have a probem like this

After migration from oracle 12.2 to 19.3 my application stopped working. The scenario is:

We have a TADOQuery component that is used by the CodeGear IDE (C ++), in this component we do an SQL search passing 3 different parameters, these parameters are inserted via the runtime:

QueryLicFulfil-> Close ();

QueryLicFulfil-> Parameters-> ParseSQL (QueryLicFulfil-> SQL-> Text, true);

QueryLicFulfil-> Parameters-> ParamByName ("train") -> DataType = ftInteger;

QueryLicFulfil-> Parameters-> ParamByName ("train") -> Value = IdTrem;

QueryLicFulfil-> Parameters-> ParamByName ("tremB") -> DataType = ftInteger;

QueryLicFulfil-> Parameters-> ParamByName ("tremB") -> Value = IdTrem;

QueryLicFulfil-> Parameters-> ParamByName ("tremC") -> DataType = ftInteger;

QueryLicFulfil-> Parameters-> ParamByName ("tremC") -> Value = IdTrem;

QueryLicFulfil-> Open ();

However, when it arrives at Open(), oracle returns an error "ORA-01036: invalid variable name / number". Within QueryLicFulfil we have a search using UNION, JOIN, DECODE operators.

In version 12.2 this problem does not appear, only in version 19 (tested in 19.3 and 19.10)

user11997318

If using a connection string "Provider=......." try adding the parameter MetaDataCacheSize=0;

Seems to work normally after that when using TADOQuery.