I have a PL/SQL script processing information between Oracle database and MySQL database. My script ran perfectly with DG4ODBC 126.96.36.199. Then we upgraded Oracle to 10.2.0 and DG4ODBC 188.8.131.52. Now if I run my script from Solaris command line like ./myscript.shl, I get the following errors:
PL/SQL: ORA-00904: "cmswhit_moodle1"."mdl_grade_grades"."finalgrade": invalid identifier
PL/SQL: ORA-00904: "cmswhit_moodle1"."mdl_question_states"."attempt": invalid identifier
The strange thing is if I run the same query by cut and paste into sqlplus from command line, the query works perfectly without any problems.
What is the cause of this problem?
Any help would be greatly appreciated.
if possible could you please enable gateway tracing level DEBUG, then execute your code again in SQL*Plus and upload it to a public share?
Please take care what you're going to upload as everybody will be able to check out the public share. If the trace contains company confidential data please log a service request at Oracle support instead.
Looking at the trace it shows only the id column for the table mdl_question_states and in the trace mdl_grade_grades is not covered at all.
Could you please create 2 gateway trace files once again running the procedure and the other one just running the select statement in SQL*Plus? To get 2 trace files please make sure to exit from SQL*Plus once you executed the select statement and make sure to run the procedure code in its own SQL*Plus session.
Then please upload the file to a share like dropbox and provide me the link - it makes it easier to read instead of checking out the file in a browser.
The problem was solved after I updated MySQL ODBC to a new version from 5.1.8 to 5.1.13.
Summary of the problem and its solution:
The Problem: It appears that dg4odbc 184.108.40.206 requires a newer version of MyODBC. Previously I used MyODBC 5.1.8 which ran into problems with dg4odbc 220.127.116.11.
The Solution: After I updated MyODBC to 5.1.13, my PL/SQL scripts all work.
I need to point out that with MyODBC 5.1.8, I can run queries and updates from SQL*Plus console, but now PL/SQL scripts.
I'll close this thread.
Once again, thank you and happy holidays.