Forum Stats

  • 3,757,930 Users
  • 2,251,293 Discussions
  • 7,869,971 Comments

Discussions

Categories

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

Noel McCusker
Noel McCusker Member Posts: 18 Blue Ribbon
edited Sep 24, 2019 10:07AM in Oracle Call Interface (OCI)

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

· Share on TwitterShare on Facebook

Answers

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,782 Bronze Crown
    edited Sep 24, 2019 10:07AM

    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?

    · Share on TwitterShare on Facebook
  • User_ZJC9T
    User_ZJC9T Member Posts: 4 Green Ribbon
    edited Feb 26, 2021 10:45PM

    @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.

    · Share on TwitterShare on Facebook
  • Noel McCusker
    Noel McCusker Member Posts: 18 Blue Ribbon

    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
    · Share on TwitterShare on Facebook
  • User_ZJC9T
    User_ZJC9T Member Posts: 4 Green Ribbon

    Thank You so much @Noel McCusker.

    · Share on TwitterShare on Facebook
  • User_ZJC9T
    User_ZJC9T Member Posts: 4 Green Ribbon

    @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?

    · Share on TwitterShare on Facebook
  • Noel McCusker
    Noel McCusker Member Posts: 18 Blue Ribbon

    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.

    · Share on TwitterShare on Facebook
  • User_F0TWL
    User_F0TWL Member Posts: 1 Green Ribbon
    edited Mar 25, 2021 2:31AM

    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)

    · Share on TwitterShare on Facebook
  • user11997318
    user11997318 Member Posts: 0 Green Ribbon

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

    Seems to work normally after that when using TADOQuery.

    · Share on TwitterShare on Facebook