This discussion is archived
2 Replies Latest reply: Apr 22, 2013 11:31 AM by 1003458 RSS

Problems after installing ODAC 11.2 Release 5 (11.2.0.3.20)

Roes Newbie
Currently Being Moderated
I have installed ODAC 11.2 Release 5 (11.2.0.3.20) with Oracle Developer Tools for Visual Studio.
I am getting pls-00114 identifier too long in VS2010 errors when configuring stored procedures in a dataset.
I made the procedure name shorter , now i am getting pls-00201 must be declared erros.

I used ODAC 11.2 Release 2 (11.2.0.1.2) with Oracle Developer Tools for Visual Studio previuosly without any problems.
  • 1. Re: Problems after installing ODAC 11.2 Release 5 (11.2.0.3.20)
    Christian.Shay - Oracle Pro
    Currently Being Moderated
    Roes wrote:
    I have installed ODAC 11.2 Release 5 (11.2.0.3.20) with Oracle Developer Tools for Visual Studio.
    I am getting pls-00114 identifier too long in VS2010 errors when configuring stored procedures in a dataset.
    I made the procedure name shorter , now i am getting pls-00201 must be declared erros.

    I used ODAC 11.2 Release 2 (11.2.0.1.2) with Oracle Developer Tools for Visual Studio previuosly without any problems.
    Can you please give me a step by step description of what I would need to do to reproduce this myself? I'll try it out.

    Thanks.
  • 2. Re: Problems after installing ODAC 11.2 Release 5 (11.2.0.3.20)
    1003458 Newbie
    Currently Being Moderated
    I got 11.2.0.3.60 Beta 2 release and it has the same issue as 11.2.0.3.0 from where this issue first started.

    To replicate the issue:-
    1. Create a simple function that returns a sys_refcursor inside an Oracle package. Test that the function runs without errors within sqlplus.

    FUNCTION GET_DUAL RETURN SYS_REFCURSOR IS
    rtn SYS_REFCURSOR;
    BEGIN
    OPEN rtn FOR SELECT * FROM dual;
    RETURN rtn;
    END GET_DUAL;

    2. In Visual Studio, add a new DataSet item.
    3. In Visual Studio, go to the Server Explorer window and connect with the same user as in sqlplus to eliminate any security concerns.
    4. In the DataSet design surface, add a new Table Adapter object. Click next to select "Use existing stored procedures". Select the (package name #0# function name) item from the dropdown and complete the wizard.

    Result:-
    If the total length of the string (package name + "#0#" + function name) gets bigger than 30, the error received is "too long" else "not exists".

    This worked with ODAC 11.2.0.2.1 just fine but none of the versions after that. SO Visual Studio has always worked.
    In 11.2.0.2.1, the TableAdapter Configuration Wizard shows the names using a dot: package[dot]function but with later ODAC versions the names are listed as package[#0#]function. The errors are because somewhere in the communication between Oracle and the wizard, the #0# in the names are no longer being correctly replaced with the dot. If the dot is not there, the function name now constitutes the package name + #0# and becomes longer than 30, hence the "too long" error. And if that name is still within 30, then there is no object that exists with the name (package name + "#0#" + function name) because the actual object name is (package name + [dot] + function name), hence the "not exists" error. This error is somewhere in the wizard logic from later versions of ODAC.
    As a normal behavior, just dragging the function name from the server explorer on to the DataSet design surface should create a DataTable for the above test function as it used to in ODAC 11.2.0.2.1.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points