Toad for Oracle Beta Program Community

Toad for Oracle - Beta Forum

Dennis Paulus

Beta Released (12.12.0.13)

Expand content
Beta Notes 12.12.0.13 Internal Bug Fixes

 

Beta Notes

12.12.0.13

Internal Bug Fixes

18 0 / Create an account to join the discussion.
kornelis.abe
Follow / 17 Aug 2017 at 2:22pm / Toad for Oracle Beta Program
Latest post by on 17 Aug 2017 at 3:06pm

Toad fails to execute query using SQL*Plus

Expand content
All, This relates to Toad for Oracle, Beta 12.12.0.12 When I have Toad execute a query using the "editor" --> "Execute query via SQL*Plus" that normally works flawlessly. Unfortunately...


All,

This relates to Toad for Oracle, Beta 12.12.0.12

When I have Toad execute a query using the "editor" --> "Execute query via SQL*Plus" that normally works flawlessly.

Unfortunately, for one specific schema it fails to do so; it does bring up a DOS-box for SQL*Plus, but then it fails:

So I tried to connect manually. Same result.
Realizing the password contains an @-sign, I then retried with the password enclosed in "double quotation marks".

That worked!

So, could it be Toad is supplying the password to SQL*Plus without enclosing quotation marks?

If so, could it be fixed, please?

Kind regards,
Abe Kornelis
==========

38 1 / Create an account to join the discussion.
kornelis.abe
Follow / 14 Aug 2017 at 8:59am / Toad for Oracle Beta Program
Latest post by on 17 Aug 2017 at 2:31pm

incorrect positioning after SQL error

Expand content
All, This relates to Toad for Oracle Beta 12.12.0.11 - but has been around for a while. I have an extremely large and complex SQL statement - please see attachment below. When I change something and...

All,

This relates to Toad for Oracle Beta 12.12.0.11 - but has been around for a while.

I have an extremely large and complex SQL statement - please see attachment below.
When I change something and inadvertently introduce an error, Oracle will throw an error at me - which is fine.

Toad then repositions the editor so that the cursor is placed on the error line.
Unfortunately, with large/complex queries Toad selects the wrong line.

In the attached query, the error is somewhere in lines 2053-2086 - that's the section I changed...

When I hit F9, I get error:
ORA-00904: "RTN"."ROUTINE_TYPE": invalid identifier

and the editor repositions to line 819.
Assuming Oracle reports the last occurrence it would be off by 1260 lines.

I'm not sure it's a Toad error - it might just as well be an Oracle error (this is running on Oracle 9...)

If you'd like additional info, please do let me know.

Kind regards,
Abe Kornelis
==========

  Please mind this .png file is actually a .zip file holding the query and the table definitions. You'll miss the data, but that should make no difference...

71 3 / Create an account to join the discussion.
kornelis.abe
Follow / 16 Aug 2017 at 8:32am / Toad for Oracle Beta Program
Latest post by on 17 Aug 2017 at 8:28am

connect from project panel fails to supply password

Expand content
All, this relates to Toad for Oracle Beta 12.12.0.12 When I right-click a schema in the project browser and request to bring up the editor, Toad will normally connect using the password it has saved...

All,

this relates to Toad for Oracle Beta 12.12.0.12

When I right-click a schema in the project browser and request to bring up the editor, Toad will normally connect using the password it has saved on a prior occasion.

For a few schemas, this does not work anymore. When I ask for the editor, it will first prompt me for a password.
Toad does not save the password. After disconnecting, the process repeats unchanged.

Funny thing is, if I go to <Session> --> <New Connection> then the connection is established using the saved password.
From that time forward the initial symptom disappears. That is: after connecting to the schema from the menu bar just once,
requesting a connection from the project pane seems to be re-enabled to use the password for that particular schema.

If this is expected behavior, that's ok.
If it requires fixing, I have 5 schemas left that we could use for testing.

Kind regards,
Abe Kornelis
==========

35 3 / Create an account to join the discussion.
Rich J.
Follow / 15 Aug 2017 at 5:45pm / Toad for Oracle Beta Program
Latest post by on 15 Aug 2017 at 5:59pm

Is the color of the data grid's lines changeable?

Expand content
Howdy all, Subject just about says it all. I can change the width of the grid lines, but can I change the color? I had embiggened my grid font while working with another person and when I went to debigulate...

Howdy all,

Subject just about says it all.  I can change the width of the grid lines, but can I change the color?  I had embiggened my grid font while working with another person and when I went to debigulate the font, the grid lines started screaming at my OCD.  Otherwise, my grid is perfectly cromulent.

Thanks!
Rich

Disclaimer: And if I see a movie where computers threaten our personal liberties, I will know you have stolen my idea.

Answered 28 1 / Create an account to join the discussion.
droeschley_109
Follow / 7 Aug 2017 at 10:43pm / Toad for Oracle Beta Program
Latest post by on 14 Aug 2017 at 7:01pm

Report Manager doesn't run

Expand content
Click on Report Manager and nothing happens. Using .8 beta

Click on Report Manager  and nothing happens.  

Using .8 beta

78 4 / Create an account to join the discussion.
Dennis Paulus

Beta Released (12.12.0.12)

Expand content
Beta Notes 12.12.0.12 Internal Bug Fixes

 

Beta Notes

12.12.0.12

Internal Bug Fixes

32 0 / Create an account to join the discussion.
kornelis.abe
Follow / 4 Aug 2017 at 1:43pm / Toad for Oracle Beta Program
Latest post by on 14 Aug 2017 at 6:37am

Formatting create materialized view statement

Expand content
All, I'm on Beta 12.12.0.7. When I format a create statement for a materizalized view it is formatted quite different than the corresponding create view statement. Here's an example (complete...

All,

I'm on Beta 12.12.0.7.

When I format a create statement for a materizalized view it is formatted quite different than the corresponding create view statement.
Here's an example (complete source code at end):

Looks ok to me, no complaints thus far.

However, when I make this a materialized view, I get quite a different result:

Most annoyingly, the initial name list on lines 2-5 are slammed into the left-hand border
without any indentation.

Is this fixable, please?

Kind regards,
Abe Kornelis
==========

4201.wcsprd_catentpart.sql

94 8 / Create an account to join the discussion.
Rich J.
Follow / 10 Aug 2017 at 9:35pm / Toad for Oracle Beta Program

ANY wildcard spec of MODEL clause flagged as syntax error

Expand content
Howdy, In beta 12.12.0.8 and 12.12.0.11, I'm running this SQL: WARNING! SQL WILL TRIGGER THE LICENSED DIAGNOSTICS PACK USAGE IF RUN! (Remove "_REMOVETHISTORUN" from the SQL to run successfully...

Howdy,

In beta 12.12.0.8 and 12.12.0.11, I'm running this SQL:

WARNING!  SQL WILL TRIGGER THE LICENSED DIAGNOSTICS PACK USAGE IF RUN! (Remove "_REMOVETHISTORUN" from the SQL to run successfully.  Thanks, Oracle Corp.)

SELECT  object_name,
        snap_time,
        space_allocated_total,
        projected_allocation
FROM    dba_hist_seg_stat_REMOVETHISTORUN s,
        dba_hist_seg_stat_obj o,
        dba_hist_snapshot sn
WHERE   o.tablespace_name = :ts_name AND
        s.obj# = o.obj#
    AND s.dataobj# = o.dataobj#
    AND s.dbid = o.dbid
    AND sn.snap_id = s.snap_id
    AND sn.dbid = s.dbid
    AND sn.instance_number = s.instance_number
    AND s.space_allocated_delta != 0
    AND o.object_name = :table_name
    AND TO_CHAR(sn.begin_interval_time,'YYYYMMDD') > :yyyymmdd
MODEL
    DIMENSION BY
    (
        o.object_name,
        TO_CHAR(sn.begin_interval_time,'MM/DD/YYYY HH24:MI:SS') "SNAP_TIME"
    )
    MEASURES
    (
        s.space_allocated_total,
        CAST(NULL AS NUMBER) "PROJECTED_ALLOCATION",
        TO_NUMBER(TO_CHAR(sn.begin_interval_time,'J')) + (TO_NUMBER(TO_CHAR(sn.begin_interval_time,'SSSSS'))/(24*60*60)) "SNAP_JULIAN"
    )
    RULES UPSERT
    (
        projected_allocation[:table_name, snap_time] =
            TRUNC
            (
                (REGR_SLOPE(space_allocated_total, snap_julian)[CV(), ANY] * snap_julian[CV(), CV()])
                + REGR_INTERCEPT(space_allocated_total, snap_julian)[CV(), ANY]
            )
    )
ORDER BY 1,2;

The ANY wildcard specification gets flagged as being invalid, but it isn't.  The documentation on it ain't great, but I did manage to find it at https://docs.oracle.com/database/121/DWHSG/sqlmodel.htm#GUID-CC847DA6-8838-412B-B984-AF75EA0FFAC8  And as I was using this SQL in Oracle 10.1, it's been around awhile....

Thanks!
Rich

Disclaimer: I wrote the SQL 10 years ago, so don't expect me to be able to explain it.

35 0 / Create an account to join the discussion.
Dennis Paulus

Beta Released (12.12.0.11)

Expand content
Beta Notes 12.12.0.11 Internal Bug Fixes

 

Beta Notes

12.12.0.11

Internal Bug Fixes

29 0 / Create an account to join the discussion.
Peter Lang
Follow / 9 Aug 2017 at 10:55am / Toad for Oracle Beta Program
Latest post by on 9 Aug 2017 at 2:34pm

Wrong column suggestions with un-aliased sub-query

Expand content
When selecting from a sub-query without alias, the suggestions are wrong. I would say that the correct suggestions would be the columns without any prefix ( COL1,COL2 ), same with the suggested COUNT...

When selecting from a sub-query without alias, the suggestions are wrong.

I would say that the correct suggestions would be the columns without any prefix (COL1,COL2), same with the suggested COUNT methods.

BTW: This feature is awesome. Even with complex queries, the suggestions are usually correct.

SELECT |
  FROM (
    SELECT 'x' AS col1, 'x' AS col2 FROM dual
  );

Unanswered 50 1 / Create an account to join the discussion.
kornelis.abe
Follow / 8 Aug 2017 at 7:32am / Toad for Oracle Beta Program
Latest post by on 9 Aug 2017 at 6:44am

Script runner aborts after compile error

Expand content
All, This relates to Toad for Oracle Beta 12.12.0.8. I'm running a script (using F5) that defines a table or a materialized view of the table, depending on the system. After defining the object...

All,

This relates to Toad for Oracle Beta 12.12.0.8.

I'm running a script (using F5) that defines a table or a materialized view of the table, depending on the system.

After defining the object, it invokes three scripts to define triggers,
and then it invokes a script to set up the initial content (if it is a table, rather than an MV).
Like this:
-- Triggers first: insert statement in _data.sql depends on insert trigger!
@..\Triggers\database_tbl_rbi.trg
@..\Triggers\database_tbl_rbu.trg
@..\Triggers\database_tbl_rbd.trg
@..\Setup_data\database_tbl_data.sql

Unfortunately, the third trigger does not compile.
But that should not cause the script from terminating;
and certainly not without reporting why it is quitting.

Here's the final piece of the script output window:
Trigger created.
 PL/SQL procedure successfully completed.
Trigger created.
 PL/SQL procedure successfully completed.
Warning: compiled but with compilation errors

I'm not worried about the error in the trigger.
What bothers me is that the script execution is terminated
without any message.

So I spooled SQL to see if that yields any clue.
Please see below for content.

I have lots of nested scripts.
It's really an issue if I cannot depend on the scripts
being run completely :-(

If you need any additional info, please let me know!

Abe Kornelis.
==========

Spooled output:


----------------------------------
-- Session: UTL_KRG@WMT920
-- Timestamp: 08:20:46.620
BEGIN SYS.DBMS_OUTPUT.ENABLE(buffer_size => NULL); END;

----------------------------------
-- Session: UTL_KRG@WMT920
-- Timestamp: 08:20:46.672
begin dbms_output.enable(0); end;

----------------------------------
-- Session: UTL_KRG@WMT920
-- Timestamp: 08:20:46.685
select PARAMETER,VALUE from nls_session_parameters where PARAMETER in('NLS_NUMERIC_CHARACTERS','NLS_DATE_FORMAT','NLS_CURRENCY');

----------------------------------
-- Session: UTL_KRG@WMT920
-- Timestamp: 08:20:46.696
select to_char(9,'9C') from dual;

----------------------------------
-- Session: UTL_KRG@WMT920
-- Timestamp: 08:20:46.711
begin
  utl_krg.install.drop_table(p_schema               => 'UTL_KRG'
                           , p_table                => 'database_tbl'
                           , p_save_data            => 'N'
                           , p_drop_ref_constraints => 'Y'
                            );
exception
  when others
  then
    utl_krg.msg_log.standard_exception_handler(p_schema           => 'utl_krg'
                                             , p_routine          => 'database_tbl'
                                             , p_msg_code         => sqlcode
                                             , p_local_msg_prefix => 'UTL'
                                             , p_message          => sqlerrm
                                              );
end;

----------------------------------
-- Session: UTL_KRG@WMT920
-- Timestamp: 08:20:46.857
begin dbms_output.get_line(:ln,:st); end;
:ln(LONG,OUT)=<NULL>
:st(INTEGER,OUT)=<NULL>

----------------------------------
-- Session: UTL_KRG@WMT920
-- Timestamp: 08:20:46.882
create table utl_krg.database_tbl(database_name       varchar2(20 char) not null /* Name of database as we use it */
                                , seq                 numeric(3, 0) not null /* Meaningless sequence number */
                                , internal_name       varchar2(50 char) not null /* Name of database as defined to itself */
                                , database_descr      varchar2(80 char) not null /* Description */
                                , otap_levels         varchar2(8 char) not null /* O=dev, T=tst, A=Accept, P=Prod, S=Support, E=Engineering */
                                , utl_krg_start       date /* date UTL_KRG schema was installed. Null when UTL_KRG not installed */
                                , utl_krg_end         date /* date UTL_KRG schema was uninstalled. Null when UTL_KRG not uninstalled */
                                , msg_repository      varchar2(8 char) /* DB_name of central database when utl_krg_installed=Y; null otherwise */
                                , standard_repository varchar2(1 char) not null /* Y/N repository must match the TO_REPPRD target DB */
                                , start_date          date not null /* First day compno is valid */
                                , end_date            date /* Last day this compno is valid */
                                , change_date         date not null
                                , change_user         varchar2(22 char) not null
                                , comments            varchar2(1024 char)
                                 );

----------------------------------
-- Session: UTL_KRG@WMT920
-- Timestamp: 08:20:46.925
comment on table utl_krg.database_tbl is '  /******************************************************************************
   NAME:      database_tbl
   REF:       Quality Improvement Program

   PURPOSE:   We want a single location to store database-related data

   REVISIONS:
   Ver  Date       Author       Description
   ---  ---------  -----------  ------------------------------------
   1.0  16-Mar-15  Abe Kornelis Created this definition
   1.1  07-Sep-15  Abe Kornelis ORADEV-176 Create common parameter table

   NOTES:

   ******************************************************************************/';

----------------------------------
-- Session: UTL_KRG@WMT920
-- Timestamp: 08:20:46.968
declare
  l_row_count pls_integer;
begin
  if upper(substr(ora_database_name, 1, 3)) <> 'REP'
  then
    utl_krg.install.drop_table(p_schema               => 'UTL_KRG'
                             , p_table                => 'database_TBL'
                             , p_save_data            => 'N'
                             , p_drop_ref_constraints => 'N'
                              );

    execute immediate 'select count(*)
                       from   all_tab_cols@rep_utl_krg
                       where  owner = ''UTL_KRG''
                          and table_name = ''DATABASE_TBL''
                          and column_name = ''STANDARD_REPOSITORY''' into l_row_count;

    if l_row_count = 1
    then -- column standard_repository is defined on target system
      execute immediate 'create materialized view utl_krg.database_tbl
                         build immediate
                         refresh complete on demand
                         as
                           select *
                           from   utl_krg.database_tbl@rep_utl_krg';
    else
      execute immediate 'create materialized view utl_krg.database_tbl
                         build immediate
                         refresh complete on demand
                         as
                           select db.*
                                , ''N'' as standard_repository
                           from   utl_krg.database_tbl@rep_utl_krg db';
    end if;
  end if;
exception
  when others
  then
    utl_krg.msg_log.standard_exception_handler(p_schema           => 'utl_krg'
                                             , p_routine          => 'database_tbl'
                                             , p_msg_code         => sqlcode
                                             , p_local_msg_prefix => 'UTL'
                                             , p_message          => sqlerrm
                                              );
end;

----------------------------------
-- Session: UTL_KRG@WMT920
-- Timestamp: 08:20:47.399
begin dbms_output.get_line(:ln,:st); end;
:ln(LONG,OUT)=<NULL>
:st(INTEGER,OUT)=<NULL>

----------------------------------
-- Session: UTL_KRG@WMT920
-- Timestamp: 08:20:47.422
comment on column utl_krg.database_tbl.database_name is 'Name of database as we know it';

----------------------------------
-- Session: UTL_KRG@WMT920
-- Timestamp: 08:20:47.451
comment on column utl_krg.database_tbl.seq is 'Meaningless sequence number to create unique keys';

----------------------------------
-- Session: UTL_KRG@WMT920
-- Timestamp: 08:20:47.480
comment on column utl_krg.database_tbl.internal_name is 'Internal name the database has defined for itself';

----------------------------------
-- Session: UTL_KRG@WMT920
-- Timestamp: 08:20:47.518
comment on column utl_krg.database_tbl.database_descr is 'Full description';

----------------------------------
-- Session: UTL_KRG@WMT920
-- Timestamp: 08:20:47.547
comment on column utl_krg.database_tbl.otap_levels is
  'Intended usage: O=development, T=test, A=Acceptance, P=Production, S=Support, E=Engineering';

----------------------------------
-- Session: UTL_KRG@WMT920
-- Timestamp: 08:20:47.576
comment on column utl_krg.database_tbl.utl_krg_start is 'Date utl_krg schema was installed; null when utl_krg not (yet) installed';

----------------------------------
-- Session: UTL_KRG@WMT920
-- Timestamp: 08:20:47.615
comment on column utl_krg.database_tbl.utl_krg_end is 'Date utl_krg schema was uninstalled';

----------------------------------
-- Session: UTL_KRG@WMT920
-- Timestamp: 08:20:47.648
comment on column utl_krg.database_tbl.msg_repository is 'Database where propagated messages are stored';

----------------------------------
-- Session: UTL_KRG@WMT920
-- Timestamp: 08:20:47.682
comment on column utl_krg.database_tbl.standard_repository is 'Y/N repository must match the TO_REPPRD target DB';

----------------------------------
-- Session: UTL_KRG@WMT920
-- Timestamp: 08:20:47.714
comment on column utl_krg.database_tbl.start_date is 'First day this database is valid';

----------------------------------
-- Session: UTL_KRG@WMT920
-- Timestamp: 08:20:47.748
comment on column utl_krg.database_tbl.end_date is 'Last day this database is valid';

----------------------------------
-- Session: UTL_KRG@WMT920
-- Timestamp: 08:20:47.782
comment on column utl_krg.database_tbl.change_date is 'Timestamp of last update';

----------------------------------
-- Session: UTL_KRG@WMT920
-- Timestamp: 08:20:47.816
comment on column utl_krg.database_tbl.change_user is 'Initiator of last update';

----------------------------------
-- Session: UTL_KRG@WMT920
-- Timestamp: 08:20:47.845
comment on column utl_krg.database_tbl.comments is 'Comment field';

----------------------------------
-- Session: UTL_KRG@WMT920
-- Timestamp: 08:20:47.891
begin
  if upper(substr(ora_database_name, 1, 3)) = 'REP'
  then
    execute immediate 'create unique index utl_krg.database_tbl_pk
                         on utl_krg.database_tbl(database_name, seq)';

    execute immediate 'alter table utl_krg.database_tbl add constraint database_tbl_pk
                         primary key(database_name, seq)';

    utl_krg.install.declare_table(p_schema => 'utl_krg', p_table => 'database_tbl');
  else
    utl_krg.install.declare_materialized_view(p_schema => 'utl_krg', p_materialized_view => 'database_tbl');
  end if;
exception
  when others
  then
    utl_krg.msg_log.standard_exception_handler(p_schema           => 'utl_krg'
                                             , p_routine          => 'database_tbl'
                                             , p_msg_code         => sqlcode
                                             , p_local_msg_prefix => 'UTL'
                                             , p_message          => sqlerrm
                                              );
end;

----------------------------------
-- Session: UTL_KRG@WMT920
-- Timestamp: 08:20:47.910
begin dbms_output.get_line(:ln,:st); end;
:ln(LONG,OUT)=<NULL>
:st(INTEGER,OUT)=<NULL>

----------------------------------
-- Session: UTL_KRG@WMT920
-- Timestamp: 08:20:47.933
alter table database_tbl
  add constraint database_tbl_fk_otap_levels foreign key(otap_levels) references utl_krg.otap_levels(otap_level) enable validate;

----------------------------------
-- Session: UTL_KRG@WMT920
-- Timestamp: 08:20:47.968
alter table utl_krg.database_tbl
  add constraint database_tbl_dates check
        (   (    start_date is null
             and end_date is null)
         or (    start_date is not null
             and (   end_date is null
                  or end_date > start_date)));

----------------------------------
-- Session: UTL_KRG@WMT920
-- Timestamp: 08:20:48.135
alter table utl_krg.database_tbl
  add constraint database_tbl_utl_dates check
        (   (    utl_krg_start is null
             and utl_krg_end is null)
         or (    utl_krg_start is not null
             and (   utl_krg_end is null
                  or utl_krg_end > utl_krg_start)));

----------------------------------
-- Session: UTL_KRG@WMT920
-- Timestamp: 08:20:48.168
alter table utl_krg.database_tbl
  add constraint database_tbl_repository check
        (   (    utl_krg_start is null
             and msg_repository is null)
         or (    utl_krg_start is not null
             and msg_repository is not null)
         or (    utl_krg_start is not null
             and utl_krg_end is not null
             and msg_repository is not null));

----------------------------------
-- Session: UTL_KRG@WMT920
-- Timestamp: 08:20:48.206
alter table utl_krg.database_tbl
  add constraint database_tbl_std_repository check(standard_repository in ('Y', 'N'));

----------------------------------
-- Session: UTL_KRG@WMT920
-- Timestamp: 08:20:48.238
grant select on utl_krg.database_tbl to public;

----------------------------------
-- Session: UTL_KRG@WMT920
-- Timestamp: 08:20:48.294
create or replace trigger utl_krg.database_tbl_rbi
  /******************************************************************************
     NAME:      database_tbl_rbi
     REF:       NEPS

     PURPOSE:   Validate data before inserting a row into the database_tbl table

     INPUT:
     OUTPUT:

     REVISIONS:
     Ver  Date       Author       Description
     ---  ---------  -----------  ------------------------------------
     1.0  16-Mrt-15  Abe Kornelis Created this definition

     NOTES:
     ******************************************************************************/
  before insert
  on utl_krg.database_tbl
  for each row
declare
  l_my_schema  constant varchar2(40) := 'utl_krg';
  l_my_routine constant varchar2(40) := 'database_tbl_rbi';

  l_message             varchar2(4000);
  l_aantal              pls_integer;
begin
  :new.change_date := current_date;

  select env.osuser
  into   :new.change_user
  from   utl_krg.current_env_vw env;
end database_tbl_rbi;

----------------------------------
-- Session: UTL_KRG@WMT920
-- Timestamp: 08:20:48.322
begin
  if upper(substr(ora_database_name, 1, 3)) <> 'REP'
  then
    utl_krg.install.drop_trigger(p_schema => 'UTL_KRG', p_trigger => 'database_TBL_RBI');
    utl_krg.msg_log.log_info_msg(p_caller  => 'utl_krg.database_tbl_rbi'
                               , p_message => 'Trigger UTL_KRG.database_tbl_rbi is not needed on ' || ora_database_name
                                );
  else
    utl_krg.install.declare_trigger(p_schema => 'UTL_KRG', p_trigger => 'database_tbl_rbi');
    utl_krg.msg_log.log_info_msg(p_caller  => 'utl_krg.database_tbl_rbi'
                               , p_message => 'Trigger UTL_KRG.database_tbl_rbi has been (re-)created'
                                );
  end if;
exception
  when others
  then
    utl_krg.msg_log.standard_exception_handler(p_schema           => 'utl_krg'
                                             , p_routine          => 'database_tbl_rbi'
                                             , p_msg_code         => sqlcode
                                             , p_local_msg_prefix => 'UTL'
                                             , p_message          => sqlerrm
                                              );
end;

----------------------------------
-- Session: UTL_KRG@WMT920
-- Timestamp: 08:20:48.353
begin dbms_output.get_line(:ln,:st); end;
:ln(LONG,OUT)=<NULL>
:st(INTEGER,OUT)=<NULL>

----------------------------------
-- Session: UTL_KRG@WMT920
-- Timestamp: 08:20:48.396
create or replace trigger utl_krg.database_tbl_rbu
  /******************************************************************************
     NAME:      database_tbl_rbu
     REF:       NEPS

     PURPOSE:   Validate data before updating a row in the database_tbl table

     INPUT:
     OUTPUT:

     REVISIONS:
     Ver  Date       Author       Description
     ---  ---------  -----------  ------------------------------------
     1.0  16-Mrt-15  Abe Kornelis Created this definition

     NOTES:
     ******************************************************************************/
  before update
  on utl_krg.database_tbl
  for each row
declare
  l_my_schema  constant varchar2(40) := 'utl_krg';
  l_my_routine constant varchar2(40) := 'database_tbl_rbu';

  l_message             varchar2(4000);
  l_aantal              pls_integer;
begin
  /* Database_name must remain unchanged */
  if :new.database_name <> :old.database_name
  then
    raise_application_error(-20001, 'changing database name is not allowed');
  end if;

  /* Sequence number must remain unchanged */
  if :new.seq <> :old.seq
  then
    raise_application_error(-20002, 'changing sequence number is not allowed');
  end if;

  /* Internal database name must remain unchanged */
  if     :new.internal_name <> :old.internal_name
     and current_date > trunc(:old.start_date)
  then
    raise_application_error(-20003, 'changing internal name is not allowed for an active database');
  end if;

  /* OTAP-levels must remain unchanged */
  if     :new.otap_levels <> :old.otap_levels
     and current_date > trunc(:old.start_date)
  then
    raise_application_error(-20004, 'changing otap_levels is not allowed for an active database');
  end if;

  /* Utl_krg can be installed only once */
  if    (    :new.utl_krg_start is null
         and :old.utl_krg_start is not null
         and current_date > trunc(:old.start_date))
     or (    :new.utl_krg_start <> :old.utl_krg_start
         and current_date > trunc(:old.start_date))
  then
    raise_application_error(-20005, 'changing utl_krg start_date is not allowed for an active database');
  end if;

  /* Utl_krg can be un-installed only once */
  if    (    :new.utl_krg_end is null
         and :old.utl_krg_end is not null
         and current_date > trunc(:old.start_date))
     or (    :new.utl_krg_end <> :old.utl_krg_end
         and current_date > trunc(:old.start_date))
  then
    raise_application_error(-20006, 'changing utl_krg end_date is not allowed for an active database');
  end if;

  /* msg_repository must remain unchanged */
  if    (    :new.msg_repository is null
         and :old.msg_repository is not null
         and current_date > trunc(:old.start_date))
     or (    :new.msg_repository <> :old.msg_repository
         and current_date > trunc(:old.start_date))
  then
    raise_application_error(-20007, 'changing msg_repository is not allowed for an active database');
  end if;

  /* start_date should remain unchanged */
  if (       :new.start_date is null
         and :old.start_date is not null
         and current_date > trunc(:old.start_date)
      or (    :new.start_date <> :old.start_date
          and current_date > trunc(:old.start_date)))
  then
    raise_application_error(-20008, 'changing start_date is not allowed for an active database');
  end if;

  /* end_date should remain unchanged */
  if (       :new.end_date is null
         and :old.end_date is not null
         and current_date > trunc(:old.start_date)
      or (    :new.end_date <> :old.end_date
          and current_date > trunc(:old.start_date)))
  then
    raise_application_error(-20009, 'changing end_date is not allowed for an active database');
  end if;

  /* Set date and user applying this change */
  :new.change_date := current_date;

  select env.osuser
  into   :new.change_user
  from   utl_krg.current_env_vw env;
end database_tbl_rbu;

----------------------------------
-- Session: UTL_KRG@WMT920
-- Timestamp: 08:20:48.435
begin
  if upper(substr(ora_database_name, 1, 3)) <> 'REP'
  then
    utl_krg.install.drop_trigger(p_schema => 'UTL_KRG', p_trigger => 'database_TBL_RBU');
    utl_krg.msg_log.log_info_msg(p_caller  => 'utl_krg.database_tbl_rbu'
                               , p_message => 'Trigger UTL_KRG.database_tbl_rbu is not needed on ' || ora_database_name
                                );
  else
    utl_krg.install.declare_trigger(p_schema => 'UTL_KRG', p_trigger => 'database_tbl_rbu');
    utl_krg.msg_log.log_info_msg(p_caller  => 'utl_krg.database_tbl_rbu'
                               , p_message => 'Trigger UTL_KRG.database_tbl_rbu has been (re-)created'
                                );
  end if;
exception
  when others
  then
    utl_krg.msg_log.standard_exception_handler(p_schema           => 'utl_krg'
                                             , p_routine          => 'database_tbl_rbu'
                                             , p_msg_code         => sqlcode
                                             , p_local_msg_prefix => 'UTL'
                                             , p_message          => sqlerrm
                                              );
end;

----------------------------------
-- Session: UTL_KRG@WMT920
-- Timestamp: 08:20:48.472
begin dbms_output.get_line(:ln,:st); end;
:ln(LONG,OUT)=<NULL>
:st(INTEGER,OUT)=<NULL>

----------------------------------
-- Session: UTL_KRG@WMT920
-- Timestamp: 08:20:48.510
create or replace trigger utl_krg.database_tbl_rbd
  /******************************************************************************
     NAME:      database_tbl_rbd
     REF:       NEPS

     PURPOSE:   Prevent data from being deleted from database_tbl

     INPUT:
     OUTPUT:

     REVISIONS:
     Ver  Date       Author       Description
     ---  ---------  -----------  ------------------------------------
     1.0  16-Mrt-15  Abe Kornelis Created this definition

     NOTES:
     ******************************************************************************/

  before delete
  on utl_krg.database_tbl
  for each row
declare
  l_my_schema  constant varchar2(40) := 'utl_krg';
  l_my_routine constant varchar2(40) := 'database_tbl_rbd';

  l_aantal              pls_integer;
begin
  if :old.start_date <= current_date
  then
    raise_application_error(-20001, 'deleting an active database is not allowed. Set end_date instead.');
  end if;

  select count(*)
  into   l_aantal
  from   utl_krg.database_group_rel dgr
  where  dgr.database_name = :old.database_name;

  if l_aantal > 0
  then
    raise_application_error(-20002, 'A database related to a group cannot be deleted.');
  end if;
end database_tbl_rbd;
Error: ORA-24344: success with compilation error


----------------------------------
-- Session: UTL_KRG@WMT920
-- Timestamp: 08:20:48.604
declare
CURSOR Cur IS
SELECT Line,Position,Text
FROM sys.All_Errors
WHERE Owner = :Owner
AND Name = :Name and Type = :Type
ORDER BY Sequence;
Rec User_Errors%ROWTYPE;
StartPos INTEGER;
EndPos INTEGER;
begin
:Res := '';
for Rec in Cur loop
if Rec.Line <> 0 or Rec.Position <> 0 then
:Res := :Res || RPad(Rec.Line || '/' || Rec.Position, 8);
end if;
StartPos := 1;
EndPos := 1;
while EndPos > 0 and StartPos <= Length(Rec.Text) loop
EndPos := InStr(Rec.Text, Chr(10), StartPos);
if EndPos > 0 then
if EndPos <> StartPos then
if StartPos > 1 then
:Res := :Res || RPad(' ', 10);
end if;
:Res := :Res || LTrim(SubStr(Rec.Text, StartPos, EndPos - StartPos)) || Chr(13);
end if;
else
if StartPos > 1 then
:Res := :Res || RPad(' ', 10);
end if;
:Res := :Res || LTrim(SubStr(Rec.Text, StartPos)) || Chr(13);
end if;
StartPos := EndPos + 1;
end loop;
end loop;
end;
:Owner(VARCHAR[7])='UTL_KRG'
:Name(VARCHAR[16])='DATABASE_TBL_RBD'
:TYPE(VARCHAR[7])='TRIGGER'
:Res(VARCHAR[0],OUT)=<NULL>

----------------------------------
-- Session: UTL_KRG@WMT920
-- Timestamp: 08:20:48.754
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
         DBMS_LOB.writeappend (p_clob, LENGTH (p_str), p_str);
      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;
:inOutCnt(INTEGER,IN/OUT)=500
:trim_line(INTEGER,IN)=1
:outLines(CLOB,OUT)=<NULL>

70 5 / Create an account to join the discussion.
Peter Lang
Follow / 15 May 2017 at 9:20am / Toad for Oracle Beta Program
Latest post by on 9 Aug 2017 at 6:23am

Schema browser: Scrolling should not change table selection

Expand content
This has bothered me for a long time, so I'd like to hear if there is a special reason for this implementation. When scrolling (mouse-wheel or scrollbars) vertically, the selected row changes. I...

This has bothered me for a long time, so I'd like to hear if there is a special reason for this implementation.

When scrolling (mouse-wheel or scrollbars) vertically, the selected row changes. I don't like this behavior, especially since this is barely visible (blue marker is not moved).
The behavior is also inconsistent, scrolling horizontally does not affect the selection.

Is there a way to disable this?

I scrolled down in the following example, so when I start typing now, row 4 instead of row 1 is edited.

Unanswered 174 7 / Create an account to join the discussion.
Peter Lang
Follow / 7 Aug 2017 at 2:50pm / Toad for Oracle Beta Program
Latest post by on 8 Aug 2017 at 6:32pm

Posting a string with 1000 characters into the editor

Expand content
When I paste a string with 1000 characters in one line into an editor, TOAD crashes. Can you reproduce this? Thanks, Peter

When I paste a string with 1000 characters in one line into an editor, TOAD crashes.

Can you reproduce this?

Thanks, Peter

94 7 / Create an account to join the discussion.
Dennis Paulus

Beta Released (12.12.0.8)

Expand content
Beta Notes 12.12.0.8 Internal Bug Fixes

 

Beta Notes

12.12.0.8

Internal Bug Fixes

23 0 / Create an account to join the discussion.
Peter Lang
Follow / 1 Mar 2017 at 3:28pm / Toad for Oracle Beta Program
Latest post by on 7 Aug 2017 at 2:52pm

Cannot jump to declaration of object-type with Ctrl+Click

Expand content
I cannot Ctrl+Click on in_some_object, because both the object and the member are highlighted. CREATE PROCEDURE test_me( in_some_object IN some_object ) AS BEGIN dbms_output.put_line( in_some_object...

I cannot Ctrl+Click on in_some_object, because both the object and the member are highlighted.

CREATE PROCEDURE test_me(
  in_some_object IN some_object
)
AS
BEGIN
  dbms_output.put_line( in_some_object.some_member );
END;

Unanswered 158 2 / Create an account to join the discussion.
kornelis.abe
Follow / 7 Aug 2017 at 11:18am / Toad for Oracle Beta Program
Latest post by on 7 Aug 2017 at 1:48pm

Formatter indents comment blocks too much

Expand content
All, This relates to Toad for Oracle Beta12.12.0.7. I have a large query with some comment blocks. The new formatter insists on pushing the comments to the far right: I don't care much about...

All,

This relates to Toad for Oracle Beta12.12.0.7.

I have a large query with some comment blocks. The new formatter insists on pushing the comments to the far right:

I don't care much about small indents for comment blocks, as long as they align proper.
But in this case the comments are indented to the far right, exceeding the intended right margin by many positions.
As a result, I have to scroll to the right to be able to read my comments.

As you may note, the second block of comments starts with a proper block comment /**/ but that does not seem to solve the problem.

Is there a way around this?

Thanks in advance,
Abe Kornelis
==========

46 4 / Create an account to join the discussion.
kornelis.abe
Follow / 7 Aug 2017 at 8:11am / Toad for Oracle Beta Program
Latest post by on 7 Aug 2017 at 9:06am

Formatter indents column alias too far

Expand content
All, I'm on Toad Beta 12.12.0.7. When formatting a select statement with a long literal all aliases are indented to the far right: This makes it difficult to visually associate each column with...

All,

I'm on Toad Beta 12.12.0.7.

When formatting a select statement with a long literal all aliases are indented to the far right:

This makes it difficult to visually associate each column with the correct alias.

When calculating the lineup column, please exclude long literals.

Discussion point: what should be the distinction between a 'long' literal and an ordinary one?
My answer: if it is more than 10 positions beyond the longest 'ordinary' column definition.

I understand this is a potential can of worms...

Thanks in advance for any associated improvement suggestions.

Abe Kornelis
==========

31 1 / Create an account to join the discussion.
kornelis.abe
Follow / 7 Aug 2017 at 8:02am / Toad for Oracle Beta Program
Latest post by on 7 Aug 2017 at 9:01am

Formatting of column aliases insufficient indentation

Expand content
All, I'm on Toad Beta 12.12.0.7. On a select statement where some columns have an alias and some do not, I get the following result: I think the source code would appear cleaner, better readable...

All,

I'm on Toad Beta 12.12.0.7.

On a select statement where some columns have an alias and some do not, I get the following result:

I think the source code would appear cleaner, better readable and better maintainable
if the aliases lined up where they would have been if all columns had had an alias.

Now, since some of the un-aliased columns have longer definitions,
the aliases and column definitions are intermingled in the same vertical 'column'.

Would this be a fixable issue?

Kind regards,
Abe Kornelis
|=========

48 1 / Create an account to join the discussion.
Peter Lang
Follow / 7 Aug 2017 at 7:17am / Toad for Oracle Beta Program
Latest post by on 7 Aug 2017 at 8:34am

Unused variable not detected (related to MEMBER OF)

Expand content
CREATE OR REPLACE PACKAGE BODY test_package AS PROCEDURE x1 AS TYPE tab IS TABLE OF NUMBER; BEGIN IF( 1 MEMBER OF NEW tab() ) THEN NULL; END IF; END x1; PROCEDURE x2 AS v_unused BOOLEAN; -- not detected...

CREATE OR REPLACE PACKAGE BODY test_package AS
  PROCEDURE x1 AS
    TYPE tab IS TABLE OF NUMBER;
  BEGIN
    IF( 1 MEMBER OF NEW tab() ) THEN
      NULL;
    END IF;
  END x1;

  PROCEDURE x2 AS
    v_unused BOOLEAN;   -- not detected
  BEGIN
    NULL;
  END x2;
END test_package;
/

Unanswered 50 1 / Create an account to join the discussion.