GV

GeezerVison

The World Wide Web, restored to 800 pixels of glory.

Home|About|Gaming cabinet|Archives|How it works|Starter sites

On this site

  • Sign in
  • ActionsCI/CD and automation
  • CodespacesCloud development environments
  • All Features
  • Documentation
  • Training
  • Certifications
  • Build with Copilot
  • Customize Copilot
  • Integrations
  • Trending repos
  • GitHub Sponsors
  • Developer articles
  • Enterprises

This view

  • Original page
  • New address

GeezerVison is showing github.com — DDL Trigger not working · Issue #1088 · utPLSQL/utPLSQL

DDL Trigger not working #1088

New issue
Copy link
New issue
Copy link
Closed
#1093
Closed
DDL Trigger not working#1088
#1093
Copy link
Assignees
jgebal
Labels
bug

Description

@Sartograph
Sartograph
opened on Jul 4, 2020
Issue body actions

Describe the bug
utPLSQL was installed with DDL trigger. However, when creating new tests or updating tests, the trigger does not update the annotation cache. ut.run() does not update the cache either, because the DDL trigger is installed. We have to manually run ut_runner.rebuild_annotation_cache() to make any new tests available.

Provide version info
Information about utPLSQL and Database version,

11.2.0.4.0
11.2.0


PL/SQL procedure successfully completed.


UT_VERSION                                                  
------------------------------------------------------------
v3.1.10.3349


BANNER                                                                          
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE	11.2.0.4.0	Production  
TNS for Solaris: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production


PARAMETER                      VALUE                                   
------------------------------ ----------------------------------------
NLS_LANGUAGE                   ENGLISH                                 
NLS_TERRITORY                  GERMANY                                 
NLS_CURRENCY                   €                                       
NLS_ISO_CURRENCY               GERMANY                                 
NLS_NUMERIC_CHARACTERS         ,.                                      
NLS_CALENDAR                   GREGORIAN                               
NLS_DATE_FORMAT                DD.MM.YYYY HH24:MI:SS                   
NLS_DATE_LANGUAGE              ENGLISH                                 
NLS_SORT                       BINARY                                  
NLS_TIME_FORMAT                HH24:MI:SSXFF                           
NLS_TIMESTAMP_FORMAT           DD.MM.YYYY HH24:MI:SSXFF                
NLS_TIME_TZ_FORMAT             HH24:MI:SSXFF TZR                       
NLS_TIMESTAMP_TZ_FORMAT        DD.MM.YYYY HH24:MI:SSXFF TZR            
NLS_DUAL_CURRENCY              €                                       
NLS_COMP                       BINARY                                  
NLS_LENGTH_SEMANTICS           BYTE                                    
NLS_NCHAR_CONV_EXCP            FALSE                                   

17 rows selected. 


PORT_STRING                                                 
------------------------------------------------------------
SVR4-be-64bit-8.1.0

Information about client software
What client was used to run utPLSQL tests?
--> SQLDeveloper

To Reproduce
Steps to reproduce the behavior:

  1. install utPLSQL with DDL trigger
  2. create or modify a unit test
  3. run ut.run()
  4. notice that the new/changed tests are not present
  5. run ut_runner.rebuild_annotation_cache()
  6. run ut.run()
  7. notice the tests are correct now

Expected behavior
When the DDL trigger is installed and active, new test objects should automatically be recognized and part of the tests. No need to manully rebuild the cache.

Example code

select object_owner, max(parse_time)
from ut3.ut_annotation_cache_info
group by object_owner;

OBJECT_OWNER                   MAX(PARSE_TIME)              
------------------------------ -----------------------------
APPS                           03.07.2020 12:33:09,864321000


begin
if ut3.ut_trigger_check.is_alive() then
   dbms_output.put_line('Trigger alive');
else
   dbms_output.put_line('Trigger NOT alive');
end if;
end;

Trigger alive


PL/SQL procedure successfully completed.


select *
from ut3.ut_annotation_cache
where annotation_name = 'suite'
and annotation_text = 'xxpk_ut_test';

no rows selected


CREATE OR REPLACE PACKAGE apps.xxpk_ut_test
AUTHID DEFINER
AS
-- %suite(xxpk_ut_test)

   -- %test(just testing)
   PROCEDURE t_ut_test;

END xxpk_ut_test;

Package XXPK_UT_TEST compiled


CREATE OR REPLACE PACKAGE BODY apps.xxpk_ut_test
AS

   PROCEDURE t_ut_test
   IS
   BEGIN
      NULL;
   END;

END xxpk_ut_test;

Package Body XXPK_UT_TEST compiled


select *
from ut3.ut_annotation_cache
where annotation_name = 'suite'
and annotation_text = 'xxpk_ut_test';

no rows selected


select object_owner, max(parse_time)
from ut3.ut_annotation_cache_info
group by object_owner;

OBJECT_OWNER                   MAX(PARSE_TIME)              
------------------------------ -----------------------------
APPS                           03.07.2020 12:33:09,864321000

select *
from ut3.ut_annotation_cache_info
where object_owner = 'APPS'
and object_name = 'XXPK_UT_TEST';

no rows selected


exec ut_runner.rebuild_annotation_cache('APPS');

PL/SQL procedure successfully completed.


select object_owner, max(parse_time)
from ut3.ut_annotation_cache_info
group by object_owner;

OBJECT_OWNER                   MAX(PARSE_TIME)              
------------------------------ -----------------------------
APPS                           04.07.2020 11:16:48,524991000


select *
from ut3.ut_annotation_cache_info
where object_owner = 'APPS'
and object_name = 'XXPK_UT_TEST';

  CACHE_ID OBJECT_OWNER                   OBJECT_NAME                    OBJECT_TYPE                    PARSE_TIME                   
---------- ------------------------------ ------------------------------ ------------------------------ -----------------------------
     41207 APPS                           XXPK_UT_TEST                   PACKAGE                        04.07.2020 11:16:48,524991000


select *
from ut3.ut_annotation_cache
where annotation_name = 'suite'
and annotation_text = 'xxpk_ut_test';

  CACHE_ID ANNOTATION_POSITION ANNOTATION_NAME                ANNOTATION_TEXT                SUBOBJECT_NAME                
---------- ------------------- ------------------------------ ------------------------------ ------------------------------
     41207                   4 suite                          xxpk_ut_test

Additional context
Installation:

# install utPL/SQL
if [ -f /mnt/dbpatches/utPLSQL/source/install_headless_with_trigger.sql ]; then
cd /mnt/dbpatches/utPLSQL/source
sqlplus "/ as sysdba" <<EOF > ~/delphix_log/install_utplsql.log
@install_headless_with_trigger.sql
EOF
fi

---------------------------------------------------------------------------

oraprod@dpx01db02$ cat install_utplsql.log 

SQL*Plus: Release 11.2.0.4.0 Production on Sun Jun 28 15:26:15 2020

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> 
no rows selected




Creating utPLSQL user UT3
--------------------------------------------------------------
Installing utPLSQL v3 framework into UT3 schema
--------------------------------------------------------------
Switching current schema to UT3
--------------------------------------------------------------
Installing component UT_DBMS_OUTPUT_CACHE
No errors.
--------------------------------------------------------------
Installing component UT_VARCHAR2_LIST
No errors.
--------------------------------------------------------------
Installing component UT_VARCHAR2_ROWS
No errors.
--------------------------------------------------------------
Installing component UT_INTEGER_LIST
No errors.
--------------------------------------------------------------
Installing component UT_OBJECT_NAME
No errors.
--------------------------------------------------------------
Installing component UT_OBJECT_NAME
No errors.
--------------------------------------------------------------
Installing component UT_OBJECT_NAMES
No errors.
--------------------------------------------------------------
Installing component UT_KEY_VALUE_PAIR
No errors.
--------------------------------------------------------------
Installing component UT_KEY_VALUE_PAIRS
No errors.
--------------------------------------------------------------
Installing component UT_REPORTER_INFO
No errors.
--------------------------------------------------------------
Installing component UT_REPORTERS_INFO
No errors.
--------------------------------------------------------------
Installing component UT_UTILS
No errors.
--------------------------------------------------------------
Installing component UT_METADATA
No errors.
--------------------------------------------------------------
Installing component UT_SAVEPOINT_SEQ
No errors.
--------------------------------------------------------------
Installing component UT_UTILS
No errors.
--------------------------------------------------------------
Installing component UT_METADATA
No errors.
--------------------------------------------------------------
Installing component UT_ANSICONSOLE_HELPER
No errors.
--------------------------------------------------------------
Installing component UT_ANSICONSOLE_HELPER
No errors.
--------------------------------------------------------------
Installing component UT_SUITE_ITEM_INFO
No errors.
--------------------------------------------------------------
Installing component UT_SUITE_ITEM_INFO
No errors.
--------------------------------------------------------------
Installing component UT_SUITE_ITEMS_INFO
No errors.
--------------------------------------------------------------
Installing component UT_EVENT_ITEM
No errors.
--------------------------------------------------------------
Installing component UT_EVENT_LISTENER
No errors.
--------------------------------------------------------------
Installing component UT_EVENT_MANAGER
No errors.
--------------------------------------------------------------
Installing component UT_EVENT_MANAGER
No errors.
--------------------------------------------------------------
Installing component UT_RUN_INFO
No errors.
--------------------------------------------------------------
Installing component UT_RUN_INFO
No errors.
--------------------------------------------------------------
Installing component UT_EXPECTATION_RESULT
No errors.
--------------------------------------------------------------
Installing component UT_EXPECTATION_RESULTS
No errors.
--------------------------------------------------------------
Installing component UT_RESULTS_COUNTER
No errors.
--------------------------------------------------------------
Installing component UT_SUITE_ITEM
No errors.
--------------------------------------------------------------
Installing component UT_SUITE_ITEMS
No errors.
--------------------------------------------------------------
Installing component UT_EXECUTABLE
No errors.
--------------------------------------------------------------
Installing component UT_EXECUTABLES
No errors.
--------------------------------------------------------------
Installing component UT_EXECUTABLE_TEST
No errors.
--------------------------------------------------------------
Installing component UT_TEST
No errors.
--------------------------------------------------------------
Installing component UT_LOGICAL_SUITE
No errors.
--------------------------------------------------------------
Installing component UT_SUITE
No errors.
--------------------------------------------------------------
Installing component UT_SUITE_CONTEXT
No errors.
--------------------------------------------------------------
Installing component UT_FILE_MAPPING
No errors.
--------------------------------------------------------------
Installing component UT_FILE_MAPPINGS
No errors.
--------------------------------------------------------------
Installing component UT_COVERAGE_OPTIONS
No errors.
--------------------------------------------------------------
Installing component UT_RUN
No errors.
--------------------------------------------------------------
Installing component UT_REPORTER_BASE
No errors.
--------------------------------------------------------------
Installing component UT_REPORTERS
No errors.
--------------------------------------------------------------
Installing component JSON_OBJECTS_SPECS
Installing json structures specs.
No errors.
--------------------------------------------------------------
Installing component UT_MATCHER_OPTIONS_ITEMS
No errors.
--------------------------------------------------------------
Installing component UT_MATCHER_OPTIONS
No errors.
--------------------------------------------------------------
Installing component UT_DATA_VALUE
No errors.
--------------------------------------------------------------
Installing component UT_KEY_ANYVAL_PAIR
No errors.
--------------------------------------------------------------
Installing component UT_KEY_ANYVAL_PAIRS
No errors.
--------------------------------------------------------------
Installing component UT_KEY_ANYVALUES
No errors.
--------------------------------------------------------------
Installing component UT_SESSION_CONTEXT
No errors.
--------------------------------------------------------------
Installing component UT_SESSION_CONTEXT
No errors.
--------------------------------------------------------------
Installing component UT_SESSION_INFO
No errors.
--------------------------------------------------------------
Installing component UT_SESSION_INFO
No errors.
--------------------------------------------------------------
Installing component UT_OUTPUT_BUFFER_INFO_TMP
No errors.
--------------------------------------------------------------
Installing component UT_OUTPUT_BUFFER_TMP
No errors.
--------------------------------------------------------------
Installing component UT_OUTPUT_CLOB_BUFFER_TMP
No errors.
--------------------------------------------------------------
Installing component UT_OUTPUT_DATA_ROW
No errors.
--------------------------------------------------------------
Installing component UT_OUTPUT_DATA_ROWS
No errors.
--------------------------------------------------------------
Installing component UT_OUTPUT_BUFFER_BASE
No errors.
--------------------------------------------------------------
Installing component UT_OUTPUT_BUFFER_BASE
No errors.
--------------------------------------------------------------
Installing component UT_OUTPUT_TABLE_BUFFER
No errors.
--------------------------------------------------------------
Installing component UT_OUTPUT_TABLE_BUFFER
No errors.
--------------------------------------------------------------
Installing component UT_OUTPUT_CLOB_TABLE_BUFFER
No errors.
--------------------------------------------------------------
Installing component UT_OUTPUT_CLOB_TABLE_BUFFER
No errors.
--------------------------------------------------------------
Installing component UT_OUTPUT_REPORTER_BASE
No errors.
--------------------------------------------------------------
Installing component UT_TRIGGER_CHECK
No errors.
--------------------------------------------------------------
Installing component UT_TRIGGER_CHECK
No errors.
--------------------------------------------------------------
Installing component UT_ANNOTATION
No errors.
--------------------------------------------------------------
Installing component UT_ANNOTATIONS
No errors.
--------------------------------------------------------------
Installing component UT_ANNOTATED_OBJECT
No errors.
--------------------------------------------------------------
Installing component UT_ANNOTATED_OBJECTS
No errors.
--------------------------------------------------------------
Installing component UT_ANNOTATION_OBJ_CACHE_INFO
No errors.
--------------------------------------------------------------
Installing component UT_ANNOTATION_OBJS_CACHE_INFO
No errors.
--------------------------------------------------------------
Installing component UT_ANNOTATION_CACHE_SEQ
No errors.
--------------------------------------------------------------
Installing component UT_ANNOTATION_CACHE_SCHEMA
No errors.
--------------------------------------------------------------
Installing component UT_ANNOTATION_CACHE_INFO
No errors.
--------------------------------------------------------------
Installing component UT_ANNOTATION_CACHE
No errors.
--------------------------------------------------------------
Installing component UT_ANNOTATION_CACHE_MANAGER
No errors.
--------------------------------------------------------------
Installing component UT_ANNOTATION_CACHE_MANAGER
No errors.
--------------------------------------------------------------
Installing component UT_ANNOTATION_PARSER
No errors.
--------------------------------------------------------------
Installing component UT_ANNOTATION_PARSER
No errors.
--------------------------------------------------------------
Installing component UT_ANNOTATION_MANAGER
No errors.
--------------------------------------------------------------
Installing component UT_ANNOTATION_MANAGER
No errors.
--------------------------------------------------------------
Installing component UT_SUITE_CACHE_ROW
No errors.
--------------------------------------------------------------
Installing component UT_SUITE_CACHE_ROWS
No errors.
--------------------------------------------------------------
Installing component UT_SUITE_CACHE_SCHEMA
No errors.
--------------------------------------------------------------
Installing component UT_SUITE_CACHE_PACKAGE
No errors.
--------------------------------------------------------------
Installing component UT_SUITE_CACHE_SEQ
No errors.
--------------------------------------------------------------
Installing component UT_SUITE_CACHE
No errors.
--------------------------------------------------------------
Installing component UT_SUITE_CACHE_MANAGER
No errors.
--------------------------------------------------------------
Installing component UT_SUITE_CACHE_MANAGER
No errors.
--------------------------------------------------------------
Installing component UT_SUITE_BUILDER
No errors.
--------------------------------------------------------------
Installing component UT_SUITE_BUILDER
No errors.
--------------------------------------------------------------
Installing component UT_SUITE_MANAGER
No errors.
--------------------------------------------------------------
Installing component UT_SUITE_MANAGER
No errors.
--------------------------------------------------------------
Installing component UT_EXPECTATION_PROCESSOR
No errors.
--------------------------------------------------------------
Installing component UT_EXPECTATION_PROCESSOR
No errors.
--------------------------------------------------------------
Installing PLSQL profiler objects into UT3 schema
PLSQL_PROFILER_RUNS table created
PLSQL_PROFILER_UNITS table created
PLSQL_PROFILER_DATA table created
Sequence PLSQL_PROFILER_RUNNUMBER created
Installing PLSQL profiler objects into UT3 schema
Installing DBMSPLSQL Tables objects into UT3 schema
Installing component UT_FILE_MAPPER
No errors.
--------------------------------------------------------------
Installing component UT_FILE_MAPPER
No errors.
--------------------------------------------------------------
Installing component UT_COVERAGE_SOURCES_TMP
No errors.
--------------------------------------------------------------
Installing component UT_COVERAGE_HELPER
No errors.
--------------------------------------------------------------
Installing component UT_COVERAGE_HELPER_BLOCK
No errors.
--------------------------------------------------------------
Installing component UT_COVERAGE_HELPER_PROFILER
No errors.
--------------------------------------------------------------
Installing component UT_COVERAGE
No errors.
--------------------------------------------------------------
Installing component UT_COVERAGE_BLOCK
No errors.
--------------------------------------------------------------
Installing component UT_COVERAGE_PROFILER
No errors.
--------------------------------------------------------------
Installing component UT_COVERAGE_REPORTER_BASE
No errors.
--------------------------------------------------------------
Installing component UT_COVERAGE_HELPER
No errors.
--------------------------------------------------------------
Installing component UT_COVERAGE_HELPER_BLOCK
No errors.
--------------------------------------------------------------
Installing component UT_COVERAGE_HELPER_PROFILER
No errors.
--------------------------------------------------------------
Installing component UT_COVERAGE
No errors.
--------------------------------------------------------------
Installing component UT_COVERAGE_BLOCK
No errors.
--------------------------------------------------------------
Installing component UT_COVERAGE_PROFILER
No errors.
--------------------------------------------------------------
Installing component UT_COVERAGE_REPORTER_BASE
No errors.
--------------------------------------------------------------
Installing component UT_RESULTS_COUNTER
No errors.
--------------------------------------------------------------
Installing component UT_SUITE_ITEM
No errors.
--------------------------------------------------------------
Installing component UT_TEST
No errors.
--------------------------------------------------------------
Installing component UT_LOGICAL_SUITE
No errors.
--------------------------------------------------------------
Installing component UT_SUITE
No errors.
--------------------------------------------------------------
Installing component UT_SUITE_CONTEXT
No errors.
--------------------------------------------------------------
Installing component UT_RUN
No errors.
--------------------------------------------------------------
Installing component UT_EXPECTATION_RESULT
No errors.
--------------------------------------------------------------
Installing component UT_REPORTER_BASE
No errors.
--------------------------------------------------------------
Installing component UT_OUTPUT_REPORTER_BASE
No errors.
--------------------------------------------------------------
Installing component UT_FILE_MAPPING
No errors.
--------------------------------------------------------------
Installing component UT_EXECUTABLE
No errors.
--------------------------------------------------------------
Installing component UT_EXECUTABLE_TEST
No errors.
--------------------------------------------------------------
Installing component UT_CONSOLE_REPORTER_BASE
No errors.
--------------------------------------------------------------
Installing component UT_CONSOLE_REPORTER_BASE
No errors.
--------------------------------------------------------------
Installing component UT_COMPOUND_DATA_TMP
No errors.
--------------------------------------------------------------
Installing component UT_COMPOUND_DATA_DIFF_TMP
No errors.
--------------------------------------------------------------
Installing component UT_JSON_DATA_DIFF_TMP
No errors.
--------------------------------------------------------------
Installing component UT_COMPOUND_DATA_VALUE
No errors.
--------------------------------------------------------------
Installing component UT_JSON_LEAF
No errors.
--------------------------------------------------------------
Installing component UT_JSON_LEAF_TAB
No errors.
--------------------------------------------------------------
Installing component UT_JSON_TREE_DETAILS
No errors.
--------------------------------------------------------------
Installing component UT_CURSOR_COLUMN
No errors.
--------------------------------------------------------------
Installing component UT_CURSOR_COLUMN_TAB
No errors.
--------------------------------------------------------------
Installing component UT_CURSOR_DETAILS
No errors.
--------------------------------------------------------------
Installing component UT_DATA_VALUE_BLOB
No errors.
--------------------------------------------------------------
Installing component UT_DATA_VALUE_BOOLEAN
No errors.
--------------------------------------------------------------
Installing component UT_DATA_VALUE_CLOB
No errors.
--------------------------------------------------------------
Installing component UT_DATA_VALUE_DATE
No errors.
--------------------------------------------------------------
Installing component UT_DATA_VALUE_DSINTERVAL
No errors.
--------------------------------------------------------------
Installing component UT_DATA_VALUE_NUMBER
No errors.
--------------------------------------------------------------
Installing component UT_DATA_VALUE_REFCURSOR
No errors.
--------------------------------------------------------------
Installing component UT_DATA_VALUE_ANYDATA
No errors.
--------------------------------------------------------------
Installing component UT_DATA_VALUE_TIMESTAMP
No errors.
--------------------------------------------------------------
Installing component UT_DATA_VALUE_TIMESTAMP_TZ
No errors.
--------------------------------------------------------------
Installing component UT_DATA_VALUE_TIMESTAMP_LTZ
No errors.
--------------------------------------------------------------
Installing component UT_DATA_VALUE_VARCHAR2
No errors.
--------------------------------------------------------------
Installing component UT_DATA_VALUE_YMINTERVAL
No errors.
--------------------------------------------------------------
Installing component UT_DATA_VALUE_XMLTYPE
No errors.
--------------------------------------------------------------
Installing component UT_COMPOUND_DATA_HELPER
No errors.
--------------------------------------------------------------
Installing component UT_DATA_VALUE_JSON
No errors.
--------------------------------------------------------------
Installing component UT_MATCHER
No errors.
--------------------------------------------------------------
Installing component UT_COMPARISON_MATCHER
No errors.
--------------------------------------------------------------
Installing component UT_BE_FALSE
No errors.
--------------------------------------------------------------
Installing component UT_BE_GREATER_OR_EQUAL
No errors.
--------------------------------------------------------------
Installing component UT_BE_GREATER_THAN
No errors.
--------------------------------------------------------------
Installing component UT_BE_LESS_OR_EQUAL
No errors.
--------------------------------------------------------------
Installing component UT_BE_LESS_THAN
No errors.
--------------------------------------------------------------
Installing component UT_BE_LIKE
No errors.
--------------------------------------------------------------
Installing component UT_BE_NOT_NULL
No errors.
--------------------------------------------------------------
Installing component UT_BE_NULL
No errors.
--------------------------------------------------------------
Installing component UT_BE_TRUE
No errors.
--------------------------------------------------------------
Installing component UT_EQUAL
No errors.
--------------------------------------------------------------
Installing component UT_CONTAIN
No errors.
--------------------------------------------------------------
Installing component UT_HAVE_COUNT
No errors.
--------------------------------------------------------------
Installing component UT_BE_BETWEEN
No errors.
--------------------------------------------------------------
Installing component UT_BE_EMPTY
No errors.
--------------------------------------------------------------
Installing component UT_MATCH
No errors.
--------------------------------------------------------------
Installing component UT_EXPECTATION
No errors.
--------------------------------------------------------------
Installing component UT_JSON_LEAF
No errors.
--------------------------------------------------------------
Installing component UT_JSON_TREE_DETAILS
No errors.
--------------------------------------------------------------
Installing component UT_CURSOR_COLUMN
No errors.
--------------------------------------------------------------
Installing component UT_CURSOR_DETAILS
No errors.
--------------------------------------------------------------
Installing component UT_EXPECTATION_COMPOUND
No errors.
--------------------------------------------------------------
Installing component UT_EXPECTATION_JSON
No errors.
--------------------------------------------------------------
Installing component UT_MATCHER_OPTIONS_ITEMS
No errors.
--------------------------------------------------------------
Installing component UT_MATCHER_OPTIONS
No errors.
--------------------------------------------------------------
Installing component UT_DATA_VALUE
No errors.
--------------------------------------------------------------
Installing component UT_COMPOUND_DATA_VALUE
No errors.
--------------------------------------------------------------
Installing component UT_COMPOUND_DATA_HELPER
No errors.
--------------------------------------------------------------
Installing component UT_DATA_VALUE_BLOB
No errors.
--------------------------------------------------------------
Installing component UT_DATA_VALUE_BOOLEAN
No errors.
--------------------------------------------------------------
Installing component UT_DATA_VALUE_CLOB
No errors.
--------------------------------------------------------------
Installing component UT_DATA_VALUE_DATE
No errors.
--------------------------------------------------------------
Installing component UT_DATA_VALUE_DSINTERVAL
No errors.
--------------------------------------------------------------
Installing component UT_DATA_VALUE_NUMBER
No errors.
--------------------------------------------------------------
Installing component UT_DATA_VALUE_REFCURSOR
No errors.
--------------------------------------------------------------
Installing component UT_DATA_VALUE_ANYDATA
No errors.
--------------------------------------------------------------
Installing component UT_DATA_VALUE_TIMESTAMP
No errors.
--------------------------------------------------------------
Installing component UT_DATA_VALUE_TIMESTAMP_TZ
No errors.
--------------------------------------------------------------
Installing component UT_DATA_VALUE_TIMESTAMP_LTZ
No errors.
--------------------------------------------------------------
Installing component UT_DATA_VALUE_VARCHAR2
No errors.
--------------------------------------------------------------
Installing component UT_DATA_VALUE_YMINTERVAL
No errors.
--------------------------------------------------------------
Installing component UT_DATA_VALUE_XMLTYPE
No errors.
--------------------------------------------------------------
Installing component UT_DATA_VALUE_JSON
No errors.
--------------------------------------------------------------
Installing component UT_MATCHER
No errors.
--------------------------------------------------------------
Installing component UT_COMPARISON_MATCHER
No errors.
--------------------------------------------------------------
Installing component UT_BE_FALSE
No errors.
--------------------------------------------------------------
Installing component UT_BE_GREATER_OR_EQUAL
No errors.
--------------------------------------------------------------
Installing component UT_BE_GREATER_THAN
No errors.
--------------------------------------------------------------
Installing component UT_BE_LESS_OR_EQUAL
No errors.
--------------------------------------------------------------
Installing component UT_BE_LESS_THAN
No errors.
--------------------------------------------------------------
Installing component UT_BE_LIKE
No errors.
--------------------------------------------------------------
Installing component UT_BE_NOT_NULL
No errors.
--------------------------------------------------------------
Installing component UT_BE_NULL
No errors.
--------------------------------------------------------------
Installing component UT_BE_TRUE
No errors.
--------------------------------------------------------------
Installing component UT_EQUAL
No errors.
--------------------------------------------------------------
Installing component UT_CONTAIN
No errors.
--------------------------------------------------------------
Installing component UT_HAVE_COUNT
No errors.
--------------------------------------------------------------
Installing component UT_BE_BETWEEN
No errors.
--------------------------------------------------------------
Installing component UT_BE_EMPTY
No errors.
--------------------------------------------------------------
Installing component UT_MATCH
No errors.
--------------------------------------------------------------
Installing component UT_EXPECTATION
No errors.
--------------------------------------------------------------
Installing component UT_EXPECTATION_COMPOUND
No errors.
--------------------------------------------------------------
Installing component UT_EXPECTATION_JSON
No errors.
--------------------------------------------------------------
Installing component UT_KEY_ANYVALUES
No errors.
--------------------------------------------------------------
Installing component UT_DOCUMENTATION_REPORTER
No errors.
--------------------------------------------------------------
Installing component UT_DOCUMENTATION_REPORTER
No errors.
--------------------------------------------------------------
Installing component UT_RUNNER
No errors.
--------------------------------------------------------------
Installing component UT_RUNNER
No errors.
--------------------------------------------------------------
Installing component UT
No errors.
--------------------------------------------------------------
Installing component UT
No errors.
--------------------------------------------------------------
Installing component UT_DEBUG_REPORTER
No errors.
--------------------------------------------------------------
Installing component UT_DEBUG_REPORTER
No errors.
--------------------------------------------------------------
Installing component UT_TEAMCITY_REPORTER
No errors.
--------------------------------------------------------------
Installing component UT_TEAMCITY_REPORTER_HELPER
No errors.
--------------------------------------------------------------
Installing component UT_TEAMCITY_REPORTER_HELPER
No errors.
--------------------------------------------------------------
Installing component UT_TEAMCITY_REPORTER
No errors.
--------------------------------------------------------------
Installing component UT_JUNIT_REPORTER
No errors.
--------------------------------------------------------------
Installing component UT_JUNIT_REPORTER
No errors.
--------------------------------------------------------------
Installing component UT_TFS_JUNIT_REPORTER
No errors.
--------------------------------------------------------------
Installing component UT_TFS_JUNIT_REPORTER
No errors.
--------------------------------------------------------------
Installing component UT_XUNIT_REPORTER
No errors.
--------------------------------------------------------------
Installing component UT_XUNIT_REPORTER
No errors.
--------------------------------------------------------------
Installing component UT_SONAR_TEST_REPORTER
No errors.
--------------------------------------------------------------
Installing component UT_SONAR_TEST_REPORTER
No errors.
--------------------------------------------------------------
Installing component UT_COVERAGE_HTML_REPORTER
No errors.
--------------------------------------------------------------
Installing component UT_COVERAGE_REPORT_HTML_HELPER
No errors.
--------------------------------------------------------------
Installing component UT_COVERAGE_REPORT_HTML_HELPER
No errors.
--------------------------------------------------------------
Installing component UT_COVERAGE_HTML_REPORTER
No errors.
--------------------------------------------------------------
Installing component UT_COVERAGE_SONAR_REPORTER
No errors.
--------------------------------------------------------------
Installing component UT_COVERAGE_SONAR_REPORTER
No errors.
--------------------------------------------------------------
Installing component UT_COVERALLS_REPORTER
No errors.
--------------------------------------------------------------
Installing component UT_COVERALLS_REPORTER
No errors.
--------------------------------------------------------------
Installing component UT_COVERAGE_COBERTURA_REPORTER
No errors.
--------------------------------------------------------------
Installing component UT_COVERAGE_COBERTURA_REPORTER
No errors.
--------------------------------------------------------------
Installing component UT_REALTIME_REPORTER
No errors.
--------------------------------------------------------------
Installing component UT_REALTIME_REPORTER
No errors.
--------------------------------------------------------------
Installing component BE_BETWEEN
No errors.
--------------------------------------------------------------
Installing component BE_EMPTY
No errors.
--------------------------------------------------------------
Installing component BE_FALSE
No errors.
--------------------------------------------------------------
Installing component BE_GREATER_OR_EQUAL
No errors.
--------------------------------------------------------------
Installing component BE_GREATER_THAN
No errors.
--------------------------------------------------------------
Installing component BE_LESS_OR_EQUAL
No errors.
--------------------------------------------------------------
Installing component BE_LESS_THAN
No errors.
--------------------------------------------------------------
Installing component BE_LIKE
No errors.
--------------------------------------------------------------
Installing component BE_NOT_NULL
No errors.
--------------------------------------------------------------
Installing component BE_NULL
No errors.
--------------------------------------------------------------
Installing component BE_TRUE
No errors.
--------------------------------------------------------------
Installing component EQUAL
No errors.
--------------------------------------------------------------
Installing component HAVE_COUNT
No errors.
--------------------------------------------------------------
Installing component MATCH
No errors.
--------------------------------------------------------------
Installing component CONTAIN
No errors.
--------------------------------------------------------------
Validating installation
--------------------------------------------------------------
Installation completed successfully
--------------------------------------------------------------
define ut3_user=PUBLIC
Granting privileges on UTPLSQL objects in UT3 schema to user PUBLIC

Session altered.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.

define ut3_user=PUBLIC

Session altered.

Creating synonyms for UTPLSQL objects in UT3 schema to user PUBLIC

Synonym created.


Synonym created.


Synonym created.


Synonym created.


Synonym created.


Synonym created.


Synonym created.


Synonym created.


Synonym created.


Synonym created.


Synonym created.


Synonym created.


Synonym created.


Synonym created.


Synonym created.


Synonym created.


Synonym created.


Synonym created.


Synonym created.


Synonym created.


Synonym created.


Synonym created.


Synonym created.


Synonym created.


Synonym created.


Synonym created.


Synonym created.


Synonym created.


Synonym created.


Synonym created.


Synonym created.


Synonym created.


Synonym created.


Synonym created.


Synonym created.


Synonym created.


Synonym created.


Synonym created.


Synonym created.


Synonym created.


Synonym created.


Synonym created.


Synonym created.


Synonym created.


Synonym created.


Synonym created.


Synonym created.


Synonym created.


Synonym created.


Synonym created.

Installing component UT_TRIGGER_ANNOTATION_PARSING
No errors.
--------------------------------------------------------------
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
oraprod@dpx01db02$

Activity

  1. jgebal commented on Jul 6, 2020

    @jgebal
    jgebal
    on Jul 6, 2020
    Member
    More actions

    @Sartograph
    Thanks for reporting it.
    I've validated and confirm that it it seems to be a bug. For now, can you use utPLSQL without DDL trigger?

    Steps I used to reproduce:

    create user APPS IDENTIFIED BY APPS;

GRANT CONNECT, RESOURCE, select any table, execute any procedure, SELECT ANY DICTIONARY TO APPS;

conn apps/apps@127.0.0.1:1521/xe

CREATE OR REPLACE PACKAGE apps.xxpk_ut_test
AUTHID DEFINER
AS
-- %suite(xxpk_ut_test)

   -- %test(just testing)
   PROCEDURE t_ut_test;

END xxpk_ut_test;
/

CREATE OR REPLACE PACKAGE BODY apps.xxpk_ut_test
AS

   PROCEDURE t_ut_test
   IS
   BEGIN
      NULL;
   END;

END xxpk_ut_test;
/

select object_owner, max(parse_time)
from ut3.ut_annotation_cache_info
group by object_owner;

select *
from ut3.ut_annotation_cache_info
where object_owner = 'APPS';

set serverout on
call ut.run('xxpk_ut_test');
  2. jgebal
    self-assigned this
    on Jul 6, 2020
  3. jgebal
    added
    bug
    on Jul 6, 2020

  4. Sartograph commented on Jul 6, 2020

    @Sartograph
    Sartograph
    on Jul 6, 2020
    Author
    More actions

    Do we just need to drop/disable the DDL trigger?
    For now we can work around that, yes. Especially after figuring out where the problem is. And good to know that you can reproduce it, I was worried that it might be related to our environment or some permission stuff regarding dictionaries.
    Also I can confirm that the trigger worked before 1.10, but don't know which one we had installed before (1.8?)...

  5. jgebal commented on Jul 6, 2020

    @jgebal
    jgebal
    on Jul 6, 2020
    Member
    More actions

    Dropping or disabling trigger will do the trick.
    utPLSQL will figure that trigger is not alive and will work just fine.

  6. jgebal commented on Aug 3, 2020

    @jgebal
    jgebal
    on Aug 3, 2020
    Member
    More actions

    I've investigated the issue and found the root cause.

    It seems that DBMS_PREPROCESSOR doesn't accept source code with schema name in it.
    When passing source-code from trigger, we didn't strip the APPS. from the CREATE OR REPLACE PACKAGE apps.xxpk_ut_test and so the preprocessor was failing.

    Thanks for reporting this issue. I'm working on a fix now.

  7. jgebal commented on Aug 3, 2020

    @jgebal
    jgebal
    on Aug 3, 2020
    Member
    More actions

    After further investigation. It seems that it only occurs when AUTHID is also used.

    So it's a bit of an edge case, where DBMS_PREPROCESSOR:

    Works fine if source line is like:

    • PACKAGE APPS.SOME_PACKAGE IS...
    • PACKAGE SOME_PACKAGE IS...
    • PACKAGE SOME_PACKAGE AUTHID DEFINER IS...
      But it fails to do it's job when source is like:
    • PACKAGE APPS.SOME_PACKAGE AUTHID DEFINER IS...
  8. jgebal
    added a commit that references this issue on Aug 3, 2020

    Fixes to annotation parsing.

    This issue will close once commit 81d4194 is merged into the 'develop' branch.
    81d4194

  9. jgebal commented on Aug 3, 2020

    @jgebal
    jgebal
    on Aug 3, 2020
    Member
    More actions

    Even more, it seems that this issue only appears on Oracle 11g.
    See here: https://travis-ci.org/github/utPLSQL/utPLSQL/builds/714545564
    Only 11g build failed.
    So I assume this issue with DBMS_PREPROCESSOR is already fixed and there could be a patch available for 11g.
    @Sartograph - you might need to research oracle support for a fix.

    In the mean time, I've added a workaround/fix for the problem in our code.

  10. jgebal
    closed this as completedin #1093on Aug 9, 2020

  11. VladDr85 commented on Sep 3, 2021

    @VladDr85
    VladDr85
    on Sep 3, 2021
    More actions

    For information.
    Reproduced the problem on Oracle 19

    v3.1.10.3349
    Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
    Version 19.12.0.0.0

  12. pesse commented on Sep 3, 2021

    @pesse
    pesse
    on Sep 3, 2021
    Member
    More actions

    Hey @VladDr85 , can you offer detailed instructions how you reproduce the problem you're experiencing on 19c?
    The original issue has already been resolved with a workaround, so this might be a new case/issue.

  13. VladDr85 commented on Sep 3, 2021

    @VladDr85
    VladDr85
    on Sep 3, 2021
    More actions

    @pesse
    utPLSQL v3.1.10.3349
    Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
    Version 19.12.0.0.0

    utPLSQL is installed without a trigger, after it is installed "install_headless_with_trigger.sql"

    create or replace function uni_priceon.betwnstr(a_string    varchar2
                                               ,a_start_pos integer
                                               ,a_end_pos   integer) return varchar2 is
begin
  return substr( a_string, a_start_pos, a_end_pos - greatest( a_start_pos, 1 ) + 1 );
end;
/

create or replace package ut_am.bout_block_calc_pkg as

  --%suite
  --%suitepath(calc_ma)
  
  --%test(Returns substring from start position to end position)
  procedure basic_usage;
  
  --%test(Returns substring when start position is zero)
  procedure zero_start_position;
  
end;
/

create or replace package body ut_am.bout_block_calc_pkg as

  procedure basic_usage is
  begin
    ut.expect(uni_priceon.betwnstr('1234567', 2, 5)).to_equal('2345');
  end;

  procedure zero_start_position is
  begin
    ut.expect(uni_priceon.betwnstr('1234567', 0, 5)).to_equal('12345');
  end;
 
end;

    Run

    -- Выполнить тесты из определенного пакета
begin
  ut.run('ut_am.bout_block_calc_pkg');
end;
select *
  from table(ut.run('ut_am.bout_block_calc_pkg'));

    trigger

    create or replace trigger ut_trigger_annotation_parsing
  after create or alter or drop
on database
begin
  if (ora_dict_obj_owner = UPPER('UT3')
    and ora_dict_obj_name = 'UT3_TRIGGER_ALIVE'
    and ora_dict_obj_type = 'SYNONYM')
  then
    execute immediate 'begin ut_trigger_check.is_alive(); end;';
  elsif ora_dict_obj_type in ('PACKAGE','PROCEDURE','FUNCTION','TYPE')
    and not (ora_dict_obj_type = 'TYPE' and ora_dict_obj_name like 'SYS\_PLSQL\_%' escape '\')
  then
      execute immediate 'begin ut_annotation_manager.trigger_obj_annotation_rebuild; end;';
  end if;
exception
  when others then null;
end;

    I'm changing the test, but I won't perform it yet
    begin ut3.ut_runner.rebuild_annotation_cache(a_object_owner => 'UT_AM'); end;
    nothing changes

    Вот как-то так

  14. VladDr85 commented on Sep 3, 2021

    @VladDr85
    VladDr85
    on Sep 3, 2021
    More actions

    For information: ut3.ut_annotation_manager

      procedure trigger_obj_annotation_rebuild is
    l_sql_text         ora_name_list_t;
    l_parts            binary_integer;
    l_restricted_users ora_name_list_t;

    function get_source_from_sql_text(a_object_name varchar2, a_sql_text ora_name_list_t, a_parts binary_integer) return sys_refcursor is
      l_sql_clob    clob;
      l_sql_lines   ut_varchar2_rows := ut_varchar2_rows();
      l_result      sys_refcursor;
    begin
      if a_parts > 0 then
        for i in 1..a_parts loop
          ut_utils.append_to_clob(l_sql_clob, a_sql_text(i));
        end loop;
        l_sql_clob := ut_utils.replace_multiline_comments(l_sql_clob);
        -- replace comment lines that contain "-- create or replace"
        l_sql_clob := regexp_replace(l_sql_clob, '^.*[-]{2,}\s*create(\s+or\s+replace).*$', modifier => 'mi');
        -- remove the "create [or replace] [[non]editionable] " so that we have only "type|package" for parsing
        -- needed for dbms_preprocessor
        l_sql_clob := regexp_replace(l_sql_clob, '^(.*?\s*create(\s+or\s+replace)?(\s+(editionable|noneditionable))?\s+?)((package|type).*)', '\5', 1, 1, 'ni');
        l_sql_lines := ut_utils.convert_collection( ut_utils.clob_to_table(l_sql_clob) );
      end if;
      open l_result for
        select a_object_name as name, column_value||chr(10) as text from table(l_sql_lines);
      return l_result;
    end;

  15. jgebal commented on Nov 16, 2021

    @jgebal
    jgebal
    on Nov 16, 2021
    Member
    More actions

    Hi @VladDr85
    Can you try installing the develop version and see if you still encounter the issue?
    I'm preparing release 3.1.11 now and would like to be sure that the issue is resolved.
    I'll give it a try myself but seems like you were able to reproduce on 3.1.10, not sure if develop version (with fixes) will solve that particular case.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

  • @jgebal
    jgebal

Labels

bug

Type

No type

Projects

No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions
    You are visitor 01402503 — GeezerVison does not store the pages you look up.
    Best viewed with Netscape Navigator 4.7 or Internet Explorer 6 at 800×600. No cookies required.