Question: Output multiple REFCURSOR variables

Discussion of open issues, suggestions and bugs regarding database management and development tools for Oracle
Post Reply
wi937c-amr
Posts: 16
Joined: Fri 29 Jun 2012 19:49

Question: Output multiple REFCURSOR variables

Post by wi937c-amr » Wed 19 Dec 2012 17:36

Is there a way to output multiple REFCURSOR variables to the data window in separate tabs?

Current Behavior (Single Variable): Executing the following statement will output the cursor to the data window.

Code: Select all

VARIABLE 
  RC REFCURSOR;
DECLARE
  MY_NUMBER NUMBER := 100;
BEGIN
  OPEN :RC FOR
  SELECT MY_NUMBER DIGIT FROM DUAL D;
END;
Current Behavior (Multiple Variables) : Executing the following statement will only output the first cursor variable to the data window.

Code: Select all

VARIABLE 
  RC REFCURSOR;
VARIABLE 
  RC2 REFCURSOR;
DECLARE
  MY_NUMBER NUMBER := 100;
  MY_OTHER_NUMBER NUMBER := 200;
BEGIN
  OPEN :RC FOR
  SELECT MY_NUMBER DIGIT FROM DUAL D;

  OPEN :RC2 FOR
  SELECT MY_OTHER_NUMBER OTHER_DIGIT FROM DUAL D;
END;

Expected/Desired Result:

Both REFCURSOR variables output to the data windows in separate tabs similar to executing the following sample statements in a single query window. Each query is displayed in the data window under a separate tab Query1 and Query2 respectively.

Code: Select all


SELECT 100 DIGIT FROM DUAL D;
SELECT 200 OTHER_DIGIT FROM DUAL D;


alexa
Devart Team
Posts: 2430
Joined: Fri 24 Jun 2011 14:17

Re: Question: Output multiple REFCURSOR variables

Post by alexa » Fri 21 Dec 2012 17:27

It should be opened in two separate tabs. Please make sure you are using the latest version of the product v3.1.243: http://www.devart.com/dbforge/oracle/st ... nload.html

Otherwise, could you please send us a screenshot displaying the result and complete SQL script to our support system at supportATdevartDOTcom

wi937c-amr
Posts: 16
Joined: Fri 29 Jun 2012 19:49

Re: Question: Output multiple REFCURSOR variables

Post by wi937c-amr » Wed 02 Jan 2013 15:26

The screen captures have been sent.

alexa
Devart Team
Posts: 2430
Joined: Fri 24 Jun 2011 14:17

Re: Question: Output multiple REFCURSOR variables

Post by alexa » Thu 03 Jan 2013 09:13

Could you please resend the screen captures as we haven't received them?

alexa
Devart Team
Posts: 2430
Joined: Fri 24 Jun 2011 14:17

Re: Question: Output multiple REFCURSOR variables

Post by alexa » Thu 03 Jan 2013 12:55

Thank you for the reply.

This issue will be fixed in one of the next builds of dbForge Studio for Oracle. We will notify you once this product build is available for downloading.

In the meanwhile, we suggest you to use the script below instead of the one you have sent us:

DECLARE
my_number NUMBER := 100;
my_other_number NUMBER := 200;
BEGIN
OPEN :rc FOR
SELECT my_number digit FROM dual d;

OPEN :rc2 FOR
SELECT my_other_number other_digit FROM dual d;
END;

In case the 'OCI invalid handle' message pops up, please click 'OK' and execute the query once again.

wi937c-amr
Posts: 16
Joined: Fri 29 Jun 2012 19:49

Re: Question: Output multiple REFCURSOR variables

Post by wi937c-amr » Thu 03 Jan 2013 15:34

Support,

Thank you for your suggestion.

Code: Select all

VARIABLE x REFCURSOR;
Removing the variable definition and instead defining the variables/parameters thru the edit parameters dialog works as expected.

The only thing of note is the fact that when the script executes, the query result tabs do not have any label text to identify them; as you look into this issue for next release, I would suggest that you use the name of the variable/parameter as the label text for the query result tab, or at least maintain the current behavior when executing multiple simple statements "Query1, Query2, etc". Of course if you feeling generous being able to apply custom labels to the query result tabs would be great too.

alexa
Devart Team
Posts: 2430
Joined: Fri 24 Jun 2011 14:17

Re: Question: Output multiple REFCURSOR variables

Post by alexa » Thu 03 Jan 2013 16:14

Thank you for bringing this to our attentions.

The corresponding labels will be also added in the next product build.

alexa
Devart Team
Posts: 2430
Joined: Fri 24 Jun 2011 14:17

Re: Question: Output multiple REFCURSOR variables

Post by alexa » Fri 11 Jan 2013 17:20

We would like to let you know that the new build 3.1.251 of dbForge Studio for Oracle where the issue you reported is fixed, has been released and is available for downloading: http://www.devart.com/dbforge/oracle/st ... nload.html

Thank you for your help in improving dbForge Studio for Oracle.

wi937c-amr
Posts: 16
Joined: Fri 29 Jun 2012 19:49

Re: Question: Output multiple REFCURSOR variables

Post by wi937c-amr » Tue 15 Jan 2013 16:35

Thank you the fix works as expected.

Post Reply