Toad World® Forums

Cursor Rowtype as Argument in Procedure throws "Unable to resolve type"


#1

Hello Folks,

i’m using Quest Code Tester a couple of Days now. First of all, great tool! Easy to handle and write tests for PL/SQL.
Second, please don’t jugde me for my bad english writing. I know, that i will make a lot of spelling mistaks. See them es a kind of gift and maybee funny moments while reading :wink:

Important things first: We use

  • Oracle 11g
  • TOAD 12.5.1.1
  • Code Tester 2.6.2.974

Lets start with my Problem. I’m facing a major problem and i don’t know, what wrong with it. I hope you can help me to get it working.

Due to some “Best Practice” rules in the company, I have two packages. One for the logic to implement, second for common Constant definition. To discripe my Problem a more easy way, her a simple Example of both:

FIRST ONE (LOGIC):
CREATE OR REPLACE PACKAGE x_x_x_x AS

PROCEDURE xTest(value1 IN SIMPLE_INTEGER, value2 IN SIMPLE_INTEGER,rowTyp IN OUT NOCOPY x_x_x_x_DEF.Y_Y_Y%ROWTYPE);

END x_x_x_x;
/

CREATE OR REPLACE PACKAGE BODY x_x_x_x AS

PROCEDURE xTest(value1 IN SIMPLE_INTEGER, value2 IN SIMPLE_INTEGER, rowTyp IN OUT NOCOPY x_x_x_x_DEF.Y_Y_Y%ROWTYPE) IS

BEGIN

NULL; – NOTHING TO DO

END;

END x_x_x_x;
/

SECOND ONE (CONSTANT):
CREATE OR REPLACE PACKAGE x_x_x_x_DEF AS

CURSOR y_y_y(value1 SIMPLE_INTEGER, value2 SIMPLE_INTEGER) IS(SELECT ‘That dont Work’ from dual);

END x_x_x_x_DEF;
/

As you can see, there is no big magic in it. One Procedure, that takes some Input (including the Problem, an Rowtype of a cursor) and de second only has the Cursor def. in it.
When i try to write a test in Code Tester, (Create Test Definition -> "x_x_x_x " -> New Test Case) I get the following error:

Internal error.
Unable to resolve type “X_X_X_X_DEF”.“Y_Y_Y”%ROWTYPE. Please make sure that this type exists in your data base or is “visible” to your connected schema.
Exception Class: ETypeResolveError

I even tried to include the cursor-def in the Package with the logic, but that fails the same way.
Then i look via google and found this one: https://support.quest.com/code-tester-for-oracle/kb/96434
We tried the Grands but that doesn’t work either… (Maybe because this fix is for release 2.2.2?)

What am I missing? Do I have to define a kind of Record for this Purpose? The real SQL-Select has more than 20 fields (with alias)…

Furthermore, I didn’t find anything to the following question:

  • Is there a Lenght restriction for package name, cursor name? (The orignial Packagename has a length of 26, cursor 16)
  • What about the underscores? Like in the example, the names have them in the same way…

I hope, you could get a glue what i’m facing and can give me an advice / solution. :slight_smile:

Greeting
Marc


#2

Hm, no one any guess?

As work around, i use a Recordtype now.

Maybee it isn’t possible to get the colum type out of an Select-Statment.

This is sure a difficult one, but I hope this could be possible in one of the future releases :slight_smile: