Toad World® Forums

Problem testing Ref_cursors.

Hi,

I am using QCTO version 1.6.1.212.

I am having trouble chceking the count of the number of records returned by a ref cursor. Following are the issues.

  1. In the Datachanged section as per the documentation it says i have to mention the structure of the ref cursor being queried.

eg. select ‘A’ name, ‘A’ address from dual

This dosent work.
However if i use the original table name it works.

  eg.  select name,address from employee
  1. But unfortunately the solution doesnot hold for queries which has nested select statements / join statements

    eg. select name, address,
    (select salary from salary for emp_id =10) as salary,
    from employee.

Can someone please help me solving this.

Thanks
Deepak.

Hi Deepak,

“It doesn’t work” isn’t a whole lot of information… Can you provide some more details… the code you’re trying to test, the table, the content of this table (or at least a sample of it), the steps you took…
Remember that we don’t have access to your database, your code or anything… so provide this information, with CREATE and INSERT statements that we can run.

Thanks for the reply… sorry about the inadequate information.

here u go.

Table - Employee with two columns
Name - varchar2
Address- varchar2

Procedure which is being tested
PROCEDURE load_Employee(cur_emp OUT cur_prr_lc_app) AS

BEGIN

OPEN cur_emp FOR
SELECT name,
address
FROM employee;

EXCEPTION
WHEN no_data_found THEN
raise_application_error(-20001, ‘NO DATA FOUND -’ ||SQLCODE||’:’||SQLERRM);
WHEN others THEN
raise_application_error(-20001, ‘ERRORS OCCURED LOADING Employee -’||SQLCODE||’:’||SQLERRM);

END load_Employee;

In the Datachanged section as per the documentation it says i have to mention the structure of the ref cursor being queried.
eg. select ‘A’ name, ‘A’ address from dual
This throws the following oracle error when i run the test “ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind”

However if i use the original table name it works.
eg. select name,address from employee

But the problem is when i test the following query for the same table.
Procedure which is being tested
PROCEDURE load_Employee(cur_emp OUT cur_prr_lc_app) AS

BEGIN

OPEN cur_emp FOR
SELECT name,
address,
(select salary from salary where emp_id = 10) as salary FROM employee;

EXCEPTION
WHEN no_data_found THEN
raise_application_error(-20001, ‘NO DATA FOUND -’ ||SQLCODE||’:’||SQLERRM);
WHEN others THEN
raise_application_error(-20001, ‘ERRORS OCCURED LOADING Employee -’||SQLCODE||’:’||SQLERRM);

END load_Employee;

I cant follow the same method which i followed previously where i mentioned the table name itself coz the "salary"column doesnot belong to that table and it throws a compilation error.

Whats the workaround. Hope i am clear this time.

Thanks
Deepak.

Thanks, that is much better information and I can reproduce the problem. The good news is that we have already fixed it for 1.8, which is undergoing final testing now and will be generally available by the end of February. You can download the beta by clicking on the “Quest Code Tester Beta” link to the left.

The problem stems from a declaration of local variables for your columns that mimics TOO CLOSELY what you provide in the query (CHAR(1)) to be specific.

I will look into a workaround for 1.6 now.

Regards,
SF

And here is the workaround:

Change your query to something like this:

SELECT RPAD (‘a’, 32767) NAME, RPAD (‘a’, 32767) address
FROM DUAL

and then Code Tester will declare variables that will not result in a -6502 error on querying.

Please confirm whether or not this works for you.

Thanks, SF

Hi SF,

Thanks for the fix it works for me.

Will be waiting for the new release.

Thanks
Deepak.

Hi Steve,

I’ve encountered the same issue but I am using version 2.6.1. The work around suggested in this post worked for me as well but I thought this issue is resolved in version1.8. I’m assuming same goes with higher version?

Thanks,

Sherly