Toad World® Forums

Displaying Ref cursor output in Result/Grid Pane


#1

Hi, when i try to open a cursor as a output in the result pane i get an exception like that “Execution failed: Refer to the output window” and i can’t find the reason, it may stem from number of coloumns in the cursor result? Can you help me?
I attach the view, thanks…

Message was edited by: yago

exception.jpeg


#2

Hi,
could you attach your package? Then i could say you more…


#3

Hi Yago,

Welcome to the community.
As Piter stated the package will be handy so that we will get an idea what you are doing in that.
Just for convenience I have attached some screen shots for viewing the ref cursor results Please follow the order from comm1 to comm5.

regards
sekhar
attachments.zip (302 KB)


#4

PROCEDURE get_cust_info(cust_no in number,cust_info out rc)

is
param SPK_CUST_TYPES.customer_type;
l_data customer_type_table := customer_type_table();
Begin

SPK_REUSE.GET_CUSTOMER_INFO(cust_no,param);

l_data.extend;

l_data(1) :=
customer_type(
param.name,
param.surname);

open cust_info for
select *
from TABLE ( cast ( l_data as customer_type_table) );
end;

–type rc is ref cursor


PACKAGE SPK_CUST_TYPES
IS
TYPE customer_type IS RECORD (
name varchar2(50),
surname varchar2(50));
END;


–type customer_type_table as table of customer_type


TYPE customer_type as object (
name varchar2(50),
surname varchar2(50));


SPK_REUSE is
PROCEDURE GET_CUSTOMER_INFO(cust_no in number,cust_info out SPK_CUST_TYPES.customer_type) IS

cursor cur_cust is select

                 name,surname from customer_table where customer_no=cust_no;

begin

for row_cust in cur_cust loop
cust_info.name := row_cust.name;
cust_info.surname := row_cust.surname;
end;
END GET_CUSTOMER_INFO;


As i told you friends before , when i run the get_cust_info(cust_no in number,cust_info out rc) i can get the customers name and surname, there is no problem, but when i try to get other customer information (such as their addresses) by adding new columns into the customer_type objects this time i get previous exception (Execution failed: Refer to the output window), i checked all variables that i added their types are correct, customer data is correct, but it gets exception, the reason may be related with the number of columns that i try to get? are there any limitations?


#5

o.k. I found the reason, it results from the type conflict between two variables: birth_date date at customer_type record in SPK_CUST_TYPES , and birth_date varchar2(10) at customer_type object. When i set them to date i can see the result, problem does not related with the numbers of columns…

i really appriate you becuse you tried to answer as sson as possible, at start i didn’t believe that you would give me answer in a short time!

thanks everybody…


#6

Hi Yago,

Thanks for the update. Glad to hear that you have manage to solve the issue. Thanks for sending in the script, we could not manage to get back to you earlier b4 you found the solution yourself .

Hope that we could hear more from you in our Community. It would be nice to hear your feedback in our next release Beta build. There will be a new Code editor in which the work flow in the editor, the debugger…are to be changed, ex. the execution wizard/console will be replaced. We are currently working closely with a number of our users like Charlie, Piter…to complete the work required. We are also looking forward to your feedback if you could assist us. That will ensure that we deliver release to meet your and our SQL Nav users’ expectation and requirements.

Thanks and regards,
Bruce