Stop auto expand in schema browser for Package

Toad for Oracle & DevOps Toolkit
1

Hi,

I'm using the latest Toad for Oracle 15.0.97.1178, and I have 2 questions

  1. Schema browser - How do I stop Toad to auto expand on a package in Schema browser (Treeview)? We have packages that has tons of variables, and I don't need to see them because I'd like to see the functions/procedures.

  2. Editor - once a package spec or body is compiled, Toad froze (hourglass) for a few seconds, up to 10, before it came back. Any idea??

Thanks.

2

  1. When is it auto expanding? When you first open it? When you click on something? The Schema Browser Navigator shows the same thing that the Editor navigator does. If you go into editor, right-click on navigator, then choose "navigator options", and uncheck the stuff you don't want to see. You can hide the variables by unchecking "Show declarations".

  2. I'm not seeing a freeze. Does it happen consistently for every package? You can see if Toad is executing some slow running sql afterwards with Spool SQL. Main Menu -> Database -> Spool SQL -> Spool to Screen.

3

Hi John,

  1. I've hidden the variables that I don't need to see. It works. Thanks.
  2. I turn on the Spool to Screen, and after pasting a new package, it shows

-- Session: XXX@DEV
-- Timestamp: 13:45:14.399
Select TEXT
from SYS.USER_SOURCE
where NAME=:name
and TYPE=:type
order by LINE;
:name(VARCHAR[8],IN)='MY_PKG'
:TYPE(VARCHAR[7],IN)='PACKAGE'

After compiling the package, it shows

-- Session: XXX@DEV
-- Timestamp: 13:51:29.013
SELECT t.*
FROM TABLE (
ut_runner.get_suites_info (a_owner => :owner, a_package_name => :name))
t
ORDER BY CASE
WHEN t.item_type = 'UT_LOGICAL_SUITE' THEN 1
WHEN t.item_type = 'UT_SUITE' THEN 2
WHEN t.item_type = 'UT_SUITE_CONTEXT' THEN 3
ELSE 4
END;
:owner(VARCHAR[8],IN)='XXX'
:name(VARCHAR[0],IN)=

-- Session: XXX@DEV
-- Timestamp: 13:51:45.221
DECLARE
LogData clob;
BEGIN
dbms_lob.CreateTemporary(lob_loc=>LogData, cache=>true, dur=>dbms_lob.call);
dbms_lob.freetemporary(LogData);
END;

-- Session: XXX@DEV
-- Timestamp: 13:51:45.232
DECLARE
v_lines SYS.DBMS_OUTPUT.chararr;
v_line VARCHAR2 (32767);
v_clob CLOB;
v_cnt NUMBER;
v_output VARCHAR2 (32767);
PROCEDURE write_to_clob (p_clob IN OUT CLOB, p_str IN OUT VARCHAR2)
AS
BEGIN
BEGIN
IF p_str IS NOT NULL THEN
DBMS_LOB.writeappend (p_clob, LENGTH (p_str), p_str);
END IF;
EXCEPTION
WHEN VALUE_ERROR
THEN
NULL;
END;
p_str := NULL;
END;
BEGIN
v_cnt := :inOutCnt;
SYS.DBMS_OUTPUT.get_lines (v_lines, v_cnt);
DBMS_LOB.createtemporary (v_clob, FALSE, DBMS_LOB.session);
DBMS_LOB.open (v_clob, DBMS_LOB.lob_readwrite);
v_output := NULL;
FOR line IN 1 .. v_lines.COUNT
LOOP
IF :trim_line = 1 THEN
v_line := TRIM (v_lines (line));
ELSE
v_line := v_lines (line);
END IF;
IF v_line IS NOT NULL
THEN
v_line := REPLACE (v_line, CHR (0));
IF LENGTHB (v_output) + LENGTHB (v_line) > 32767
THEN
write_to_clob (v_clob, v_output);
END IF;
v_output := v_output || v_line;
IF line < v_lines.COUNT
THEN
IF LENGTHB (v_output) + 2 > 32767
THEN
write_to_clob (v_clob, v_output);
END IF;
v_output := v_output || CHR (13) || CHR (10);
END IF;
END IF;
END LOOP;
write_to_clob (v_clob, v_output);
if v_cnt > 0 then
:outLines := v_clob;
end if;
:inOutCnt := v_cnt;
DBMS_LOB.freetemporary (v_clob);
END;
:trim_line(INTEGER,IN)=1
:inOutCnt(INTEGER,IN/OUT)=500
:outLines(CLOB,OUT)=

Is this normal??

4

It looks like the unit test query took about 15 seconds. Try running that on its own in the editor to make sure it really takes that long. If it does, you might want to take it up on a utplsql forum. Maybe there is something you can do to speed that up....trim some data, add an index, etc.

The stuff with Lobs afterwards is getting dbms_output. That usually runs pretty fast.

5

Is there a way to disable or uninstall utplsql that listed in the Unit Testing menu?? I don't remember I installed it or Toad installed it silently?? Do we need it??

6

Toad does not install it silently. You do not need it, but maybe another person in your organization installed it into the database. It is not a part of Toad, but Toad uses it if it is present.

There is documentation (including how to uninstall) here.

I think UT3 is the default schema that it is installed into.

7

I see. You're right, we're currently sharing database with other teams that happens to own the database. I saw the UT3 schema too.

Thanks again for you help John.

1 Like
8

There are couple of things you can do that may ease this delay.

  1. Close Unit Test Manager panel if it is open (see Unit Testing menu)

  2. Compile using F9 - In my testing it seems that you may be compiling using F5 (Execute as Script)

The best long term solution for you would be to have the others using the shared database configure the synonyms for utPLSQL such that they are not public. They can then make synonyms/grant access as needed only to those schemas that are using it.

9

Hi Michael,

No, I don't have the Unit Testing panel open. I tried both to compile my package using either F9 or F5, but both triggers a wait state after the compilation. I prefer F5 since it displays the output of the compilation or error if there is any. I normally use F5 or this icon image to compile.

For utPLSQL, I have to live with it because it's not our database but owns by other teams. We are limited only to our project.

I tried it though, the utPLSQL is 3.1.1 which is incompatible with my Toad version 15.0.

Thanks.