j-sieben / pit Goto Github PK

PIT core check_prerequisites.sql script checks for UTL_TEXT existence and fails because core install.sql script does not call it's installation before (@&core_dir.utl_text/install.sql).

Hi Jürgen, I just dropped the schema and ran the install procedure from your latest Git commits. The installation ended with a success message but I found following issues:

• ')' is missing in PIT_APEX_PKG here:

pit.log(msg.WEBSOCKET_MESSAGE, msg_args(g_websocket_server, l_message);

• in PIT_PKG:
  • component 'NAME_TYPE' must be declared
  • identifier 'G_SCHEMA_NAME' must be declared

During installation I was asked about the values for true, false and the post- and prefixes. The text for the post fix says "Geben Sie einen Wert für false ein" so I guess it is a Copy&Paste issue.

Regards,
Stefan

Dear Jürgen,
I want to establish PIT and replace Logger in my company (leading German automotive supplier). Therefore I am preparing a demo environment to convince our architects :-)
I connected as SYS and got following issue. I would appreciate your help a lot!

I have made only small changes to the values in init.sql

-- ADJUST THIS SETTINGS IF YOU WANT ANOTHER TYPE 
--define FLAG_TYPE="char(1 byte)";
--define C_TRUE="'Y'";
--define C_FALSE="'N'";

define FLAG_TYPE="number(1, 0)";
define C_TRUE=1;
define C_FALSE=0;
...
define EXCEPTION_PREFIX=e
define EXCEPTION_POSTFIX=ERR

install script output:

********************************************************************************
*** PL/SQL INSTRUMENTATION TOOLKIT (PIT) Installation at user PIT_OWNER

********************************************************************************
*** Checking whether required users exist
**  Checking owner user PIT_OWNER
.    - User PIT_OWNER exists.
**  grant user rights
*   Grant SYSTEM privileges
**  Check whether PIT_OWNER has privilege create session
.    - System privilege create session is already granted.
**  Check whether PIT_OWNER has privilege create procedure
.    - System privilege create procedure is already granted.
**  Check whether PIT_OWNER has privilege create table
.    - System privilege create table is already granted.
**  Check whether PIT_OWNER has privilege create type
.    - System privilege create type is already granted.
*   Grant OBJECT privileges
**  Check whether PIT_OWNER has privilege select on dba_context
.    - Object privilege select on dba_context is already granted.
.    - SYS granted inherit privileges to PIT_OWNER
.    - Set Compiler-Flags

********************************************************************************
*** Installing parameter framework
**  Change current schema to PIT_OWNER
**  Clean up existing installations
.    - Package PARAM deleted.
.    - Package PARAM_ADMIN deleted.
.    - Table PARAMETER_GROUP deleted.
.    - Table PARAMETER_LOCAL deleted.
.    - Table PARAMETER_TAB deleted.
.    - Table PARAMETER_TYPE deleted.
.    - View PARAMETER_REALM_VW deleted.
.    - View PARAMETER_VW deleted.
**  Create parameter tables
.    - Create table PARAMETER_GROUP
.    - Table PARAMETER_REALM already exists
.    - Create table PARAMETER_TYPE
.    - Create table PARAMETER_TAB
.    - Create table PARAMETER_LOCAL
**  Create parameter view
.    - Create view parameter_core_vw
.    - Create view parameter_vw.owner
.    - Create view parameter_realm_vw
**  Create parameter packages
.    - Create package specification param
No errors.
.    - Create package specification param_admin
No errors.
.    - Create package body param
No errors.
.    - Create package body param_admin

Warning: Package Body created with compilation errors.

Errors for PACKAGE BODY PARAM_ADMIN:

LINE/COL ERROR
-------- -----------------------------------------------------------------
502/7    PL/SQL: SQL Statement ignored
504/38   PL/SQL: ORA-00932: inconsistent datatypes: expected NUMBER got
         CHAR

529/7    PL/SQL: Statement ignored
530/32   PLS-00364: loop index variable 'PRE' use is invalid
**  Load data
.    - Script merge_parameter_realm
begin
*
ERROR at line 1:
ORA-04063: package body "PIT_OWNER.PARAM_ADMIN" has errors
ORA-06508: PL/SQL: could not find program unit being called: "PIT_OWNER.PARAM_ADMIN"
ORA-06512: at line 3

Learned about PIT yesterday and I'm really impressed. Love what you've build here!
I thought I already have high standards with ORA Logger but you put it to the next level :D

But after reading through the doc I have some questions and thoughts. (Important: I haven't installed or used PIT yet!)

• As far as I understood you have to define the severity for a message. But I only see log procedures which override this and dont have variable level?! E.g.: debug | Captures debug information, level debug.
  So the severity of the msg will be ignored? Do I miss something?
• How to handle the messages with multiple development environments. E.g. keep all the pit_admin.merge_messages in a script which can be version controlled and therefore merged and called on every instance?!
• Some procedure names are in my opinion not self explanatory, eg.: "stop" (handles an error and calls leave and re-raises the error). This info needs to be in the name in my opinion, eg. "leave_and_raise"
• Option to log msg_params without calling "enter" or "leave" which would switch the context.
• Is there a way to raise exception with raise_application_error and the correct message? Sth like pit.raise(msg.CHILD_RECORD_FOUND_ERR)? Or should this be done with pit.sql_exception?

Maybe some bad questions but as I said I just had a first look :)

Hey Juergen,

this tool looks awesome, however I won't be able to use it as part of a client project because I will never get DBA privileges at any point for the client's production database.
It seems to me that the only reason to use dba privileges for installation is convenience, is that correct?
Can you give a hint what would be needed to create an alternative installation approach that allows to install PIT into an existing user without any privileges?
I'd be willing to contribute for implementation.

Sam

Hi Jürgen,
I am preparing my demo to some important guys in order to convince them to integrate PIT into our new Exadata machine. I found the following obsolete syn. in my application schema.

• pit_log (replaced by PIT_TABLE_LOG?)
• PIT_CALL_STACK (to PIT_TABLE_CALL_STACK?)
• PIT_TABLE_CALL_PARAMS (to PIT_TABLE_PARAMS?)
  I hope I have not overlooked anything.
  Regards, Stefan

Hallo Jürgen,

bei der Installation von PIT (neueste Version) wird folgender Fehler ausgegeben:

** Create tables
. - Create table PIT_MESSAGE_LANGUAGE
. - Create table PIT_MESSAGE_GROUP
. - Create table PIT_TRANSLATABLE_ITEM
. - Create table PIT_MESSAGE_SEVERITY
pse_pti_pmg_id varchar2(50 char) default on null 'PIT',
*
ERROR at line 5:
ORA-00936: missing expression

Kannst Du mir an dieser Stelle weiterhelfen ?

Besten Dank und viele Grüße
Thomas

Fehler anfang:
. - Create package Body PIT_ADMIN

Warnung: Package Body wurde mit Kompilierungsfehlern erstellt.

Fehler bei PACKAGE BODY PIT_ADMIN:

LINE/COL ERROR

988/7 PL/SQL: Statement ignored
988/25 PLS-00201: ID 'MSG.SQL_ERROR' muss deklariert werden
. - Create default parameters
. - Create translatable items
begin
*
FEHLER in Zeile 1:
ORA-04063: package body "PIT_OWNER.PIT_ADMIN" enthält Fehler
ORA-06508: PL/SQL: aufgerufene Programmeinheit : "PIT_OWNER.PIT_ADMIN" konnte nicht gefunden werden
ORA-06512: in Zeile 3

Grüße
Jürgen (aus Berlin ;-) )

Good afternoon,

Trying to install the PIT and accompanying Apex application on autonomous cloud.

The install script for PIT mentions:
-- PIT_MAIL may be installed after installing UTL_TEXT
--@modules/pit_mail/install.sql
But when I install PIT first and UTL_TEXT next and then PIT with uncommented @modules/pit_mail/install.sql, I lose the UTL_TEXT messages and I end up with an invalid UTL_TEXT. I'm a bit in a chicken-egg sitiuation...

I have to disable this set plsql_code_type - is it really neccesary?
-- insufficient privileges for this one on autonomous cloud
-- alter session set plsql_code_type='NATIVE';

Also using PIT_OWNER schema name as password is not allowed, I have to include a separate parameter with password.

There is no tablespace user on cloud, the default permanent tablespace is data. Maybe fetching the default permanent tablespace and using that, is more robust?

Shouldn't that be &C_TRUE. and &C_FALSE. ?
Now the install asks for the values to use and crashes on the second FALSE replacement value...

Dear Jürgen
thanks for reworking the installation grants for PIT, this will help to introduce it a lot in my company.
Unfortunately while installing it always fails due to invalid PIT_MODULE spec.
I also tried to manually compile all type specs but this also failed.
Anyway, happy easter, see you at APEX Connect!
Stefan

. - Create type specificationPIT_MODULE_LIST
** Create ADMIN package declarations
. - Create package specification pit_util

Fehler fⁿr TYPE PIT_OWNER.PIT_MODULE:

LINE/COL ERROR

0/0 PL/SQL: Compilation unit analysis terminated
1/11 PLS-00304: Body von 'PIT_MODULE' nicht ohne die Spezifikation kompilierbar
`

Hallo Jürgen,

bei meinen Tests ist es vorgekommen (jedes Mal), dass Objekte nicht Valid waren. In Deinem Projekt UTL_TEXT verwendest Du in Deinem InstallScript am Ende folgendes Statement, um ein Recompile durchzuführen, vielleicht ist es sinnvoll das oder etwas Ähnliches auch hier eingesetzt werden.

prompt &h1.Recompiling invalid objects
declare
l_invalid_objects binary_integer;
begin
dbms_utility.compile_schema(
schema => user,
compile_all => false);

select count(*)
into l_invalid_objects
from user_objects
where status = 'INVALID';

dbms_output.put_line(l_invalid_objects || ' invalid objects found');
end;
/

mfg.
Ralf

Hi Jürgen,

ich versuche gerade PIT in APEX22 zum laufen zu bekommen. Ich habe soweit alle Funktionen für Websheets mit Dummy Werten aufgefüllt, da ich diese eh nicht verwenden werde. Damit lässt sich das Package zumindest wieder kompilieren. Und die Installation läuft durch.

Ich habe nun nur das Problem das PIT_UI Package Fehler hat, da die message Konstanten für msg.UTL_INVALID_REQUEST, msg.UTL_PARAMETER_REQUIRED und msg.UTL_INVALID_REQUEST_ERR fehlen. Ich finde diese aber in keinem Installationsskript.
Kann ich die Parameter einfach selbst im Package anlegen und mir einen ErrorCode dazu ausdenken?

Grüße
Moritz

Dear Jürgen,
there is a similar issue when granting privs to a client.

alter session set plsql_implicit_conversion_bool = true
Error at line 9
ORA-02248: invalid option for ALTER SESSION

Connected PIT_OWNER

*** Grant access to PIT to client DEV

** Grant access to PARAM installation

** Granting access to parameter package to DEV

• Grant access rights to DEV
  Script stopped on line 8 of ...git\pit\PIT\install_scripts../parameters/grant_client.sql.

Hallo Jürgen,

bei einer Neuinstallation ist mir folgendes aufgefallen:
Voraussetzungen:

• separates tablespace (create tablespace TS_SU_ALL Datafile ....)
• neuer Benutzer (create user SUTLS identified by "pswd" default tablespace TS_SU_ALL ...)
• Berechtigung (grant connect to SULTS)

Ein Aufruf des Installationsskriptes mittels @pit_install SUTLS GERMAN führt erst mal zu einem Fehler in "chek_users_exist.sql". Dort kann der Identifizierer "l_user_has_quota" nicht aufgelöst werden. Nach dem Ersetzen von "l_user_has_quota" durch "l_user_has_tablespace" war das Problem behoben.
Allerdings scheiterte ein weiterer Versuch jetzt daran, dass die Tabelle "parameter_group" im Skript "parameter_group.tbl" wegen nicht ausreichender Berechtigung nicht angelegt werden konnte (ORA-01950: keine Berechtigungen für Tablespace 'TS_SU_ALL'). Der Grund dafür besteht darin, dass im Skript "chek_users_exist.sql" quotas für das tablespace "users" erzeugt werden, nicht aber für das default tablespace des Benutzers.

Mein Vorgehen für eine gelungene Installation besteht also jetzt darin, dass ich a) in "chek_users_exist.sql" den Bezeichner "l_user_has_quota" durch "l_user_has_tablespace" ersetze und b) beim Anlegen des Benutzers die quotas für das default tablespace vorher festlege.

Vermutlich ist mein Vorgehen bei der Installation nicht der allgemein beschrittene Pfad, aber vielleicht lässt sich auch hierfür noch eine kleine Verbesserung etablieren.

Viele Grüße und besten Dank für die hervorragenden Werkzeuge,
Frank

Hi!
I entered a non-existing language at this point
"Enter default language (Oracle language name) for messages:"

The installation nevertheless continued and but later on gave me some error like "ungueltiger Benutzer" or something similar.
It would be great, if the installer aborts earlier and with a more logical error.

The error was due the init/init.sql script which failed to initialize the INSTALL_USER variable using the following statement due the fact that my chosen language was non existent:
select user sys_user,
upper('&1.') install_user,
null remote_user,
value default_language
from V$NLS_VALID_VALUES
where parameter = 'LANGUAGE'
and value = upper('&2.');

I know this is just convenient feature, so no worries.

Best regards,

Nils Stritzel

Folgende Fehlermeldung bekomme ich bei der Installation von PIT:
Fehler f³r PACKAGE BODY UTILS.PIT_ADMIN:

LINE/COL ERROR

790/16 PL/SQL: Item ignored
790/16 PLS-00201: ID 'WWV_FLOW_GLOBAL.VC_ARR2' muss deklariert werden
799/7 PL/SQL: Statement ignored
799/7 PLS-00320: Die Typ-Deklaration dieses Ausdruckes ist unvollstõndig oder fehlerhaft
800/7 PL/SQL: Statement ignored
800/31 PLS-00320: Die Typ-Deklaration dieses Ausdruckes ist unvollstõndig oder fehlerhaft
801/7 PL/SQL: Statement ignored
801/16 PLS-00320: Die Typ-Deklaration dieses Ausdruckes ist unvollstõndig oder fehlerhaft
. - Create default parameters

greets
tanto

Hi Jürgen,
sorry, it's me again with a hopefully small issue ;-) After the sucessful installation I wanted to grant access to my test user to start building a demo.
A hint: During installation I set the boolean parameter as follows:
define FLAG_TYPE="number(1, 0)"; define C_TRUE=1; define C_FALSE=0;

Thanks a lot for any help!

output of pit_install_client.sql

`INSTALL_USER REMOTE_USER

PIT_OWNER POS8FE

ORA_NAME_TYPE ORA_MAX_LENGTH

varchar2(128 byte) ##########

*** Installing PIT client POS8FE

*** Checking whether required users exist
** Checking owner user PIT_OWNER
. - User PIT_OWNER exists.
** Check whether PIT_OWNER has privilege create session
. - System privilege create session is already granted.

** Grant access to PARAM installation

** Granting access to parameter package to POS8FE

• Clean up schema POS8FE
  . - revoking client rights
• Grant access rights to POS8FE
  . - Granting execute on param to POS8FE
  . - Create synonym param for PIT_OWNER.param at POS8FE
  . - Granting execute on param_admin to POS8FE
  . - Create synonym param_admin for PIT_OWNER.param_admin at POS8FE
  . - Granting select, references on parameter_group to POS8FE
  . - Create synonym parameter_group for PIT_OWNER.parameter_group at POS8FE
  . - Granting select, references on parameter_realm to POS8FE
  . - Create synonym parameter_realm for PIT_OWNER.parameter_realm at POS8FE
  . - Granting select, references on parameter_tab to POS8FE
  . - Create synonym parameter_tab for PIT_OWNER.parameter_tab at POS8FE
  . - Granting select, references on parameter_type to POS8FE
  . - Create synonym parameter_type for PIT_OWNER.parameter_type at POS8FE
  . - Granting select on parameter_core_vw to POS8FE
  . - Create synonym parameter_core_vw for PIT_OWNER.parameter_core_vw at POS8FE
  . - Granting select on parameter_realm_vw to POS8FE
  . - Create synonym parameter_realm_vw for PIT_OWNER.parameter_realm_vw at POS8FE
• Change current schema to POS8FE
• Create PIT user tables and views
  . - Create table PARAMETER_LOCAL
  . - Create view PARAMETER_VW
  coalesce(pal_boolean_value, par_boolean_value) par_boolean_value,
  *
  FEHLER in Zeile 10:
  ORA-00932: Inkonsistente Datentypen: CHAR erwartet, NUMBER erhalten`

Hi Jürgen,
I proudly created my first test message
BEGIN pit.info( msg.my_first_info_message ); END;
but the execution ended up in
[Error] Execution (222: 1): ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "PIT_OWNER.PIT_UTIL", line 393
ORA-06512: at "PIT_OWNER.PIT_PKG", line 234
ORA-06512: at "PIT_OWNER.PIT_PKG", line 262
ORA-06512: at "PIT_OWNER.PIT_PKG", line 284
ORA-06512: at "PIT_OWNER.PIT_PKG", line 815
ORA-06512: at "PIT_OWNER.PIT_PKG", line 896
ORA-06512: at "PIT_OWNER.PIT", line 238
ORA-06512: at line 2

The message is inserted correctly in MSG table (wanted to add a screenshot but this does not work at the moment).
(PSE_ID: 50, PML_NAME: AMERICAN, PMS_ID = PMS_NAME = MY_FIRST_INFO_MESSAGE)

Do you have a clue what's the reason here?

Hello Jürgen,
I think I found one last place where Y/N is not replaced by the values provided in settings:
C_SETTING_REGEX constant varchar2(200) := '^(((10|20|30|40|50|60|70)\|(10|20|30|40|50)\|(Y|N)\|[A-Z_]+(\:[A-Z_]+)*)|(10\|10\|N\|))$';

I came across this when I wanted to create my first named context.

this works fine:
BEGIN
pit.set_context( p_log_level => pit.level_all
, p_trace_level => pit.trace_all
, p_trace_timing => TRUE
, p_log_modules => 'PIT_CONSOLE:PIT_TABLE'
);
END;
this fails:
BEGIN
pit_admin.create_named_context(
p_context_name => 'CONTEXT_12'
, p_log_level => 70
, p_trace_level => 40
, p_trace_timing => TRUE
, p_module_list => 'PIT_CONSOLE:PIT_TABLE'
);
END;

Have a good one,
Stefan

Hallo Jürgen, erstmal schöne Osterfeiertage.
Wollte heute am Karfreitag PIT installieren, was aber immer an derselben Stelle mit einem Fehler abbricht:
. - Create default parameters
. - Script ParameterGroup_PIT
Geben Sie einen Wert für true ein: 'y'
Geben Sie einen Wert für false ein: 'n'
Geben Sie einen Wert für false ein: 'n'
,p_par_description => 'Named context, switches logging on [LOG_LEVEL|TRACE_LEVEL|TRACE_TIMING_FLAG ('Y','N')|MODULE_L
IST]'
*
FEHLER in Zeile 37:
ORA-06550: Zeile 37, Spalte 105:
PLS-00103: Fand das Symbol "Y" als eines der folgenden erwartet wurde:
) , * & = - + < / > at in is mod remainder not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like like2
like4 likec between || multiset member submultiset

Habe es auch mit den Symbolen versucht, die mir als 'erwartet' angezeigt wurden.
Viele Liebe Grüße
Jürgen (Tanto)

Hi Jürgen,
sorry, it' me again with an issue:-)
In utl_text.clob_to_blob the name of the assertion exception does not get adapted to what was setup during the PIT installation (I chose E as prefix).

when msg.ASSERT_IS_NOT_NULL_ERR then...

Have a nice day,
Stefan

Hi Jürgen, this time I did not find a new issue;-) I just found out that package PIT_APEX_PKG is invalid due to my DB version 12.1.02 and the JSON_OBJECT_T which was introduced in 12.2 :-(
Unfortunately we will not upgrade in the next months so I would need to apply a workaround.
But if I get it right it's relatively easy to achieve the same with manual string concatenation. The format expected is
[ {"key1":"value1"},...{"key_n":"value_n"} ], then everything should work be fine and I simply pass it over instead of l_message.stringify here:

pit.log(msg.WEBSOCKET_MESSAGE, msg_args(g_websocket_server, l_message.stringify)); l_response := apex_web_service.make_rest_request( p_url => g_websocket_server, p_http_method => 'GET', p_body => l_message.stringify());

If you see any further issues I haven't considered I would appreciate a short note.
Have a nice weekend!
Stefan

Hello, we were trying to install PIT on the Oracle-Always-Free-Cloud-ATP database instance, but it seems that the admin user is not allowed to log in as sysdba. We see the same problems with Logger for example. Maybe you have already tried this yourself?

Hey Jürgen,
while proceeding with my demo (to convince our architect and chief developer) I found two small issues in this subchapter

1. the MERGE_MESSAGE procedure has new parameter names
2. the TRANSLATE_MESSAGE does not exist any more. I assume that I can use the merge_message and just provide another language to translate (at least this worked for me, I hope this is the intended way)
3. a typo in parameter name:
   pit.enter('my_func', p_ oparams => msg_params(

Enjoy the rest of your day,
Stefan

Hallo Jürgen, diesmal auf Deutsch, ist einfacher ;-)
Ich bin fast am Ziel, für mein Client Schema "DEV" ist das Skript "grant_client_access" bereits erfolgreich durchgelaufen.
Weil ich mit einer Sandbox-DB arbeite, die nicht in der zentral-verteilten TNS names ist, habe ich in der install_client.bat einfach den connect Befehl direkt eingebaut, daran sollte es aber nicht liegen:

echo @install_scripts/grant_client_access %InstallUser% %RemoteUser% | sql PIT_OWNER/******@//*****:1522/CIAPEX19C.WORLD 
echo @install_scripts/create_client_synonyms %InstallUser% %RemoteUser% | sql DEV/******@//*****:1522/CIAPEX19C.WORLD

Das Skript create_client_synonyms bricht vorzeitig ab, beim Anlegen den ersten Synonyms.

DEFAULT_TABLESPACE
_____________________
USERS
.    - Checking whether PIT exists at user PIT_OWNER

ERR_PRE    ERR_POST
__________ ___________
E_

Boolean type: number(1, 0), 1|0

********************************************************************************
*** Registering PIT, found at PIT_OWNER at client DEV

**  Registering parameter package.
*   Clean up schema DEV

**  Remove registration of parameter package.
*   Drop synonyms
*   Drop local objects
.    - Drop table PARAMETER_LOCAL
.    - Drop view PARAMETER_VW
*   Create synonyms
.    - Create synonym param
Abgemeldet von Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.7.0.0.0

es startet damit

prompt &h3.Create synonyms
@&tools.create_synonym.sql param

Abbruch müsste dann hier erfolgen, evtl. weil PIT_USER nicht übergeben wurde und der Vergleich dann schief geht.

prompt &s1.Create synonym &1.
begin
  if '&PIT_USER.' != user then
    execute immediate 'create or replace synonym &1. for &PIT_USER..&1.';
  end if;
end;
/

Gruß
Stefan