Toad World® Forums

schema.schema causing compile time error when code tester and application code is separate schemas


#1

application code - APPS schema

custom code - XXG schema

code tester - XXG_CODE_TESTER schema

I am testing a packaged procedure in APPS schema. One of the arguments is pi_claim_rec IN xxg_interface_stg%ROWTYPE

xxg_interface_stg table is created under the XXG schema and being referenced with a synonym in APPS schema where the actual packaged procedure being tested resides. I do NOT want to add xxg. prefix to the argument in procedure nor do i want to prefix it with APPS

XXG_CODE_TESTER has all the privileges on xxg_interface_stg table and execute priv on APPS. other tests are running fine.

When i create a test there is a compile time error

PLS-00201: identifier ‘XXG.APPS’ must be declared
PL/SQL: Item ignored
PLS-00313: ‘S_PI_CLAIM_REC$1’ not declared in this scope

If i copy the body of generated test code to TOAD, remove the duplicate schema.schema reference the code compiles fine but it doesn’t help

  1. With every new test case added the test code keeps getting overwritten with latest test code being generated

  2. When the test is run it says “No test results available at this level” under the results tab

  3. Under Properties tab test code compilation status keeps going invalid after the run even though i just compiled successfully after removing XXG.APPS.

Is this a known issue ? How can i get around this please ? I am using trial version code tester 3.2.0.19 part of Toad Oracle Expert 11.6 2017


#2

addtional info:

In Test Editor, under the set value tab i tried changing datatype used to declare variable from “APPS”.“XXG_INTERFACE_STG”%ROWTYPE tO “XXG”.“XXG_CLAIMS_INTERFACE_STG”%ROWTYPE

It saves but as soon as i run the test it goes back to “APPS”.“XXG_INTERFACE_STG”%ROWTYPE causing test code to be invalid.

created a synonym in xxg_code_tester for apps.xxg_interface_stg - did NOT work


#3

Hi Sarfraz,

Thank you for your question. There is an issue with a generation of assigment code for such definition of %ROWTYPE parameter. You can use customization to write your own code for the assignent function for such input parameter. Open the test editor, select the input parameter, right click and select ‘Edit Assigment function…’ from the menu. Then you can fix the multiple schemas in the definition and updated code will be used for the next test code generation.

Assignent_function.png
Regarding the change of datatype used for declaration - it could be done on the test element level. Then it will be populated to related inputs and/or outcomes definitions as well. But it’s not necessary in your case.

Regards,
Viťa


#4

HI Drabek,

Thanks a lot. This worked.

BR,

Sarfraz.


#5

Thanks a lot. It worked!


#6

we are using customized schema(CUBE) when ever we are running new test case it is adding

the schema name prefixed when we are running the code tester test case

we are using code tester 2.7.0

/* Capture start time. /
Q##start_time := DBMS_UTILITY.GET_TIME;
“CUBE”.“XXCFI_CB_UNIT_TEST_PKG”.“XXCFI_CB_UNIT_TEST_PRC”(
PIN_HTS_CODE => /
i_PIN_HTS_CODE /in#343688645#v(dyntc_index_in)
,POUT_ERROR_MESSAGE => P_POUT_ERROR_MESSAGE
);
/
START POST EXECUTION CONTENT */

.
the starting portion of schema name is causing the issue. “CUBE”.

“CUBE”.“XXCFI_CB_UNIT_TEST_PKG”.“XXCFI_CB_UNIT_TEST_PRC”(
PIN_HTS_CODE => /* i_PIN_HTS_CODE */in#343688645#v(dyntc_index_in)
,POUT_ERROR_MESSAGE => P_POUT_ERROR_MESSAGE
);

Any work around to resolve this issue will appreciate.


#7

Hi,

If you would like to do not include schema name because of it causing problem in your setup, you can try to check the ‘Do not prefix program name with owner of program’ option in the ‘Test Code Generation’ preferences (Database/Preferences menu) and regenerate your test code.

Optionally there is also an option to override execution section in the test case customization - there is a special section ‘Execute this code instead of generated execution code’ where you can write your customized execution.

Regards,
Vita


#8

We tried the option changing the preferences

Test Code Generation’ preferences (Database/Preferences menu)
but while running the code we are getting the below error

code tester packages added with CUBE prefix

Unanticipated error: ORA-06550: line , column :
ORA-06512: at line 1
ORA-06512: at “CUBE.QU_RUNTIME”, line 2087
ORA-06512: at “CUBE.QU_RUNTIME”, line 2110
ORA-06512: at “CUBE.QU_RUNTIME”, line 2158
ORA-06512: at “CUBE.QU_RUNTIME”, line 2331
ORA-06512: at “CUBE.QU_TEST”, line 1686
ORA-06550: line 1, column 63:
PLS-00225: subprogram or cursor ‘CUBE’ reference is out of scope
ORA-06550: line 1, column 8:
PL/SQL: Statement ignored
ORA-06550: line 1, column 106:
PLS-00225: subprogram or cursor ‘CUBE’ reference is out of scope
ORA-06550: line 1, column 72:
PL/SQL: Statement ignored

PL/SQL Error Backtrace: ORA-06512: at line 1
ORA-06512: at “CUBE.QU_RUNTIME”, line 2087
ORA-06512: at “CUBE.QU_RUNTIME”, line 2110
ORA-06512: at “CUBE.QU_RUNTIME”, line 2158
ORA-06512: at “CUBE.QU_RUNTIME”, line 2331
ORA-06512: at “CUBE.QU_TEST”, line 1686
ORA-06512: at “CUBE.QU_TEST”, line 1736
ORA-06512: at “CUBE.QU_TEST”, line 1948


#9

This means that there are some other dependencies on the CUBE schema, could be datatype declaration or something else depend on the complexity of the test definition. Please try to investigate the generated test code.


#10

we tried customization of the code but the same error is poping up .

at runtime it is fetching the CUBE schema name to code tester packages

as CUBE.QU_RUNTIME


#11

Could you please summarize what is the user executing the test, where is the code Tester repository installed, where is the tested procedure, where you would like to have test code generated? Thanks. Vita


#12

Thanks for the patience,

we are having customized schema name is CUBE

We installed code tester version 3.3.0.24 version in CUBE schema

we created simple procedure to test the no of records in the package that is also we created in the same
schema CUBE – name --XXCFI_CB_UNIT_TEST_PKG

when we verified the generated test code it is compiling properly .

when we are running one QU_TEST test case we are getting this error saying .

Unanticipated error: ORA-06550: line , column :
ORA-06512: at line 1
ORA-06512: at “CUBE.QU_RUNTIME”, line 2087
ORA-06512: at “CUBE.QU_RUNTIME”, line 2110
ORA-06512: at “CUBE.QU_RUNTIME”, line 2158
ORA-06512: at “CUBE.QU_RUNTIME”, line 2331
ORA-06512: at “CUBE.QU_TEST”, line 1686
ORA-06550: line 1, column 63:


#13

Just a question that could help to investigate - is there any object/program named ‘CUBE’ or such synonym in your ‘CUBE’ database schema?


#14

we verified all the objects and synonyms there is no object or reference like CUBE .


#15

Thanks for verification. The reason why I asked was that this is the common reason of raising Oracle ‘PLS-00225: subprogram or cursor ‘CUBE’ reference is out of scope’.
Is it possible to send me the export of the test definition directly?


#16

Q##CUBE.XXCFI_CB_UNIT_TEST_PKG.txt (41.7 KB)

Please find the attachment in txt file aftre exporting to excel we have added .txt extension to upload here.


#17

Hi,

thanks for input. I checked to run your test definitions with the same result. I am affraid that CUBE is one of the Oracle SQL keywords and this is the reason. Oracle says that usage of SQL keyword as a name of object may lead to unpredictable results and this is such case. If you change the schema name then there is no problem with execution.

Vita


#18

Thanks for patience in replying .

we are also agree about the CUBE is a key word . but we cannot change the schema name entire application is depending .

our question is why it is calling CUBE.qu_run_time packages.?

For your info when I am taking the debug code from code tester

after right clicking on test case we have option “Copy Debug Block to Clipboard”

This code when I am running from oracle/toad it is working fine and results are shown.

am attaching the debug code for your reference.

debug.txt (6.1 KB)


#19

qu_runtime package is executed by the backend when unanticipated error occur during the test code execution to process the Oracle error, save message to qu_log table and so on.
There is also an option to install Code Tester repository to some other schema and grant execution of the tested code to such schema.


#20

we have only one schema is CUBE we are testing the application in the CUBE schema only.

In debugging block there is no SCHEMA.Package is called .

why it is happening in the TEST run ?

Any other alternatives will appreciate.