New Feature Request

[Roeland]

Hi,

When selecting some code in a package or method, it's possible to right click on it an choose "Test". This is already very nice also because you can't do it when it doesn't make sense.

BUT, I want more!

1) When searching for variables, could you use 1 bind-variable for each unique occurrence? The current behavior is just making a bind-variable for each variable, regardless of the variables are the same. Could you also enhance the naming of the bind-variables?

2) Could you also lookup "constants" and replace them with their value? We have a lot of constants created in packages, but replacing them in a SQL window is tedious.

This 2 enhancements would make debugging code a lot easier.

Thanks,

Roeland

 

[Claus Pedersen]

Hi Roland,

1) what do you mean by "Could you also enhance the naming of the bind-variables"? When a test program unit is created, the naming of the bind variables correspond to the naming of the method parameters? This must be OK?

2) I totally agree on this! For some obscure reason, Oracle is not able to make use of package constants in selects, views etc., but only functions. For this reason we have made a lot of functions with the sole purpose of returning the value of a package variable. Tedious!
But when testing for instance a select statement originating from a cursor, we have often used many package constants. These constants will then have to be replaced by the constant literal values or their corresponding function. It would be so nice if PLD could assist in this process, for instance by looking up and replacing the package constant with the corresponding literal value.
A way to implement this in the GUI, could be to mark a select, right-click and select the new menu option "Replace constants with literal values". Furthermore, as many package constants are not prefixed by package name, when used inside the same package, it would be nice to have the option somehow to set a package context like in SQL servers USES clause, so all occurrences of a constant name is prefixed with a package name when searching for the corresponding value.

Also, it would be nice if the test window could automatically fill in values for parameters with default values in the calling method.

For instance, when a method is defined as

PROCEDURE tester (myParameter1 NUMBER DEFAULT 1,
                        myParameter2 VARCHAR DEFAULT 'X') IS
<...>

The test parameters :myParameter1 should default be filled in with 1 in the parameter list at the bottom of the screen, and :myParameter2 should default to X.
Also, it would be nice, if a comment is made in the generated test script for each parameter that has a default value, so this parameter could be commented out and thus always obtain it's default value, like this:

BEGIN
  tester (
          myParameter1 => :myParameter1, -- Parameter has default value 1 (NUMBER)
          myParameter2 => :myParameter2  -- parameter has default value 'X' (VARCHAR2)
          );
END;

or similar.

Does this make sense?

 

[Roeland]

Hi Claus,

1) what do you mean by "Could you also enhance the naming of the bind-variables"?

If you read my original question again:

When selecting some code in a package or method, it's possible to right click on it an choose "Test".

Just try it. It's one of those hidden features of PL/SQL Developer, that once you know them, you wonder why you didn't used them before.

But if you try this, you'll notice that there are some strange issues.
(see my first post)

Roeland

 

[Claus Pedersen]

Hi Roeland,

finally, the penny has dropped for me

I believe, you are referring to an example like the following:

SELECT *
FROM   emp
WHERE  emp.job = myPackage.myConstant

Mark it, right-click, and choose test, a test window with the following code is generated:

SELECT *
FROM   emp
WHERE  emp.job = :Var1

I agree that the naming :Var1 is not at all optimal.

 

[Roeland]

Hi Claus,

It's even worse.

Inside a package or method:

procedure View_Emp (p_Cursor out sys_refcursor,
                    p_Id in Emp.id%type,
                    p_Name in Emp.Name%type) is
begin
  open p_Cursor for
--Start mark here =>
    SELECT *
    FROM   emp
    WHERE  emp.job = myPackage.myConstant
      and  emp.id = p_Id
      and  emp.id = p_Id
      and  emp.name = p_Name;  -- <= End mark here
end ;

Mark the SELECT Block, right-click, and choose test, a test window with the following code is generated:

SELECT *
    FROM   emp
    WHERE  emp.job = myPackage.myConstant
      and  emp.id = :Var1
      and  emp.id = :Var2
      and  emp.name = :Var3;  -- <= End mark here

As you can see, p_Id is changed into :Var1 AND :Var2, while I just want _Id and _Name.
Also, the constant is still there.

This is what I want:

SELECT *
    FROM   emp
    WHERE  emp.job = 5 -- myPackage.myConstant
      and  emp.id = :p_Id
      and  emp.id = :p_Id
      and  emp.name = :p_Name;  -- <= End mark here

Thanks,

Roeland

PS: Thank you for the example. It made it obvious for me again that some sample code can do wonders...

 

[Bernhard S]

I'd like to easily add window list items to the project via right click there. It could also be usefull to have another right click menu item to add all items in your window list to your current project this way.
I'd also appreciate it a lot if there was an easy way to right away see, which items in your window list belong to the project and which ones don't. Maybe format the text of items not belonging to the project in cursive?

 

[Claus Pedersen]

@Berhard S:
Uhh, I think this comment belongs to another post?

 

[Bernhard S]

@Claus:
I just thought this thread is as the topic says about "New Feature Request" and wanted to add my request here.

 

[Babo]

not exactly related to the thread, but from the "improvement" perspective i would love to have "find" dialog re-worked.

well, and maybe constraints tab in "tables" too, right Marco ?