#1 utplsql with 2 schemas

[kslimani]

Hi,
I have a problem concerning the use of utplsql when i use 2 schemas.
In "Requirements for Executing Test Code" section it is noted : "...If, however, you install utPLSQL in a shared schema and then access it from other schemas, you may need to grant additional privileges to the utPLSQL schema...It therefore requires directly granted EXECUTE privileges on those code elements (both the code to be tested and the test packages) ..."

From my first schema :
(i) i have a schema which contains my package (package to test) noted P
(ii) i create in the same schema ut_P.pks and ut_P.pkb (with ut_setup and ut_teardown proc) + 1 proc to test my only function defined in P.
(iii) i execute grant execute on both P and ut_P from my user to the second schema (named utp).

From my second schema (utp) :
(iv) i create a user : utp + necessary grant (create table, create procedure, etc.).
(v) i install utplsql objects (tables, packages) in utp.

When i try, with utp :
sql>exec utplsql.test('P', recompile_in=>false,owner_in=>'my first schema');
i have failure : unable to run 'my first schema'.ut_p : identifier 'my first schema'.ut_p must be declared ...

when i try :
sql>exec utplsql.test('P', recompile_in=>false);
i have : proram named "ut_p" does not exist

FI. : with sqlplus when connected with utp i can call my function declared in P. I can call ut_setup and ut_teardown also ...

I suppose that there is an obvious element that I don't see or that I forget, but there I am blocked !
By advance, thank you for your assistance.

N.B. : please excuse my approximate English

kamel slimani

[kslimani]

see attached files

[Paul Walker]

Hi,

Please can you turn tracing on and do your test again?

To turn tracing on, run the procedure:

utplsql.trc

When you've finished, you can turn it off again with:

utplsql.notrc

Please post the full output.

Thanks,
Paul

[kslimani]

Hi,

thanks for your reply.

--
SQL> exec utplsql.trc

ProcÚdure PL/SQL terminÚe avec succÞs.

SQL> set serveroutput on
SQL> exec utplsql.test('p_ksltry', recompile_in=>false,owner_in=>'swioper');
Initialized utPLSQL session...
Setpkg to p_ksltry
Package and program = "swioper".ut_p_ksltry
Same package? N
Is package? Y
Prefix = ut_
Runprog of ut_SETUP
Package and program = "swioper".ut_p_ksltry.ut_SETUP
Same package? N
Is package? Y
Prefix = ut_
Compile Error "ORA-06550: Ligne 1, colonne 8 :
PLS-00201: l'identificateur
'swioper.UT_P_KSLTRY' doit Ûtre dÚclarÚ
ORA-06550: Ligne 1, colonne 8 :
PL/SQL:
Statement ignored" on:
BEGIN "swioper".ut_p_ksltry.ut_SETUP; END;
.

FFFFFFF AA III L U U RRRRR EEEEEEE
F A A I L U U R R E
F A A I L U U R R E
F A A I L U U R R E
FFFF A A I L U U RRRRRR EEEE
F AAAAAAAA I L U U R R E
F A A I L U U R R E
F A A I L U U R R E
F A A III LLLLLLL UUU R R EEEEEEE
.
FAILURE: "p_ksltry"
.
Individual Test Case Results:

FAILURE - .: Unable to run "swioper".ut_p_ksltry.ut_SETUP: ORA-06550: Ligne 1,
colonne 8 :
PLS-00201: l'identificateur 'swioper.UT_P_KSLTRY' doit Ûtre
dÚclarÚ
ORA-06550: Ligne 1, colonne 8 :
PL/SQL: Statement ignored

FAILURE - .: utPLSQL.test failure: User-Defined Exception

Errors recorded in utPLSQL Error Log:

NONE FOUND
Runprog of ut_TEARDOWN
Package and program = "swioper".ut_p_ksltry.ut_TEARDOWN
Same package? N
Is package? Y
Prefix = ut_
Compile Error "ORA-06550: Ligne 1, colonne 8 :
PLS-00201: l'identificateur 'swioper.UT_P_KSLTRY' doit Ûtre dÚclarÚ
ORA-06550: Ligne 1, colonne 8 :
PL/SQL: Statement ignored
ORA-06510: PL/SQL : exception dÚfinie par l'utilisateur non traitÚe
ORA-06512: Ó "UTP.UTASSERT2", ligne 149
ORA-06512: Ó "UTP.UTASSERT", ligne 49
ORA-06512: Ó "UTP.UTPLSQL", ligne
426
ORA-06550: Ligne 1, colonne 8 :
PLS-00201: l'identificateur
'swioper.UT_P_KSLTRY' doit Ûtre dÚclarÚ
ORA-06550: Ligne 1, colonne 8 :
PL/SQL:
Statement ignored" on:
BEGIN "swioper".ut_p_ksltry.ut_TEARDOWN; END;
BEGIN utplsql.test('p_ksltry', recompile_in=>false,owner_in=>'swioper'); END;

*
ERREUR Ó la ligne 1 :
ORA-06510: PL/SQL : exception dÚfinie par l'utilisateur non traitÚe
ORA-06512: Ó "UTP.UTASSERT2", ligne 149
ORA-06512: Ó "UTP.UTASSERT", ligne 49
ORA-06512: Ó "UTP.UTPLSQL", ligne 426
ORA-06550: Ligne 1, colonne 8 :
PLS-00201: l'identificateur 'swioper.UT_P_KSLTRY' doit Ûtre dÚclarÚ
ORA-06550: Ligne 1, colonne 8 :
PL/SQL: Statement ignored
ORA-06512: Ó "UTP.UTPLSQL", ligne 929
ORA-06512: Ó "UTP.UTPLSQL", ligne 1079
ORA-06510: PL/SQL : exception dÚfinie par l'utilisateur non traitÚe
ORA-06512: Ó "UTP.UTASSERT2", ligne 149
ORA-06512: Ó "UTP.UTASSERT", ligne 49
ORA-06512: Ó "UTP.UTPLSQL", ligne 426
ORA-06550: Ligne 1, colonne 8 :
PLS-00201: l'identificateur 'swioper.UT_P_KSLTRY' doit Ûtre dÚclarÚ
ORA-06550: Ligne 1, colonne 8 :
PL/SQL: Statement ignored
ORA-06512: Ó ligne 1

SQL>

F.I. :

SQL> select user from dual;

USER

UTP

--calling function (in my first schema from utp schema) to be tested
SQL> select swioper.p_ksltry.f1 from dual;

    F1

---

     1

--calling procs of package ut_myPackageToBeTested
SQL> exec swioper.ut_p_ksltry.ut_setup;
ut_setup...

ProcÚdure PL/SQL terminÚe avec succÞs.

SQL> exec swioper.ut_p_ksltry.ut_teardown;
ut_teardown...

/ here is the code of my ut_teardown
procedure ut_teardown is
begin
dbms_output.put_line('ut_teardown...');
null;
end ut_teardown;
/

ProcÚdure PL/SQL terminÚe avec succÞs.

SQL> exec swioper.ut_p_ksltry.ut_f1;

ProcÚdure PL/SQL terminÚe avec succÞs.

SQL>

[kslimani]

--Additional info :

--with utp schema :

SQL> BEGIN "swioper".ut_p_ksltry.ut_SETUP; END;
2 /
BEGIN "swioper".ut_p_ksltry.ut_SETUP; END;
*
ERREUR Ó la ligne 1 :
ORA-06550: Ligne 1, colonne 8 :
PLS-00201: l'identificateur 'swioper.UT_P_KSLTRY' doit Ûtre dÚclarÚ
ORA-06550: Ligne 1, colonne 8 :
PL/SQL: Statement ignored

--whereas :

SQL> BEGIN swioper.ut_p_ksltry.ut_SETUP; END;
2 /
ut_setup...

ProcÚdure PL/SQL terminÚe avec succÞs.

SQL>

[kslimani]

I progress concerning my problem : I do not have my precedent errors. I created synonyms for the 2 packages + corresponding grants and there i do not have any more the error. On the other hand, I have an odd warning: “Warning… No tests were identified for execution!”.
I continue to investigate. I keep you informed.

Thank you

kamel

[Paul Walker]

Glad you're making progress :)

Please can you post the output from the following when logged on as each user (so from "utp" and "swioper"):

SELECT owner, object_name, procedure_name
FROM   all_procedures
WHERE  object_name = 'UT_P_KSLTRY';

Are you also able to attach the package spec and body for UT_P_KSLTRY?

[kslimani]

After having created the synonyms for the 2 packages (p_ksltry and ut_pksltry), and by using (with utp user) "SQL> exec utplsql.test('p_ksltry', recompile_in=>true);" command to test, now I have Failure with warning : "Warning...no tests were identified for execution!"

As if it didn't see my procedure UT_F1 (to test a simple function f1 which makes select a 1 into a variable and return this variable)…

I give up! In any event, I do not need to make unit tests (or others), I do not make a bug:) !!!

Anyway, thank you for your assistance.

OWNER, OBJECT_NAME, PROCEDURE_NAME
SWIOPER, UT_P_KSLTRY, UT_TEARDOWN
SWIOPER, UT_P_KSLTRY, UT_SETUP
SWIOPER, UT_P_KSLTRY, UT_F1
SWIOPER, UT_P_KSLTRY, NULL

-- ut_p_ksltry.pks
CREATE OR REPLACE package SWIOPER.UT_P_KSLTRY authid current_user
as
procedure UT_SETUP;
procedure UT_TEARDOWN;
procedure UT_F1;
end UT_P_KSLTRY;

-- ut_p_ksltry.pkb

CREATE OR REPLACE package body SWIOPER.UT_P_KSLTRY
as
procedure UT_SETUP is
begin
dbms_output.put_line('ut_setup.....!');
null;
/
exception
when others
then
null; -- ignore if any errors.
/
end UT_SETUP;

procedure UT_TEARDOWN is
begin
dbms_output.put_line('ut_teardown.....!');
null;
end UT_TEARDOWN;

procedure UT_F1 is
begin
--dbms_output.put_line('ut_f1 begin...');

 utp.utassert.eq (
     'test 1',
     P_KSLTRY.F1,
     1
 );
 --dbms_output.put_line('ut_f1 end...');

end UT_F1;
end UT_P_KSLTRY;
/

[Paul Walker]

I've been doing some testing, trying to replicate what you're seeing. I'm not certain my setup is exactly the same as yours, but I think I managed to get the outcome you're wanting. These are the steps I took:

1) Log on as SYS and create the users:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

--User: utp CREATE USER utp IDENTIFIED BY utp DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP"; GRANT UNLIMITED TABLESPACE TO utp; GRANT CONNECT TO utp; GRANT CREATE PROCEDURE TO utp; GRANT CREATE PUBLIC SYNONYM TO utp; GRANT CREATE SEQUENCE TO utp; GRANT CREATE TABLE TO utp; GRANT CREATE VIEW TO utp; GRANT DROP PUBLIC SYNONYM TO utp; --User: swioper CREATE USER swioper IDENTIFIED BY swioper DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP"; GRANT UNLIMITED TABLESPACE TO swioper; GRANT CONNECT TO swioper; GRANT CREATE PROCEDURE TO swioper;

2) Log on as UTP and install utPLSQL:

1	@ut_i_do install

3) Log on as SWIOPER and create the main package and the test package (attached as pkg.sql and ut_pkg.sql)

1 2	@pkg @ut_pkg

4) While still logged on as SWIOPER, execute the test:

1 2	set serveroutput on exec utplsql.test('p_ksltry', recompile_in=>false);

After doing this, I got the following output:

ut_setup.....!
.
>    SSSS   U     U   CCC     CCC   EEEEEEE   SSSS     SSSS
>   S    S  U     U  C   C   C   C  E        S    S   S    S
>  S        U     U C     C C     C E       S        S
>   S       U     U C       C       E        S        S
>    SSSS   U     U C       C       EEEE      SSSS     SSSS
>        S  U     U C       C       E             S        S
>         S U     U C     C C     C E              S        S
>   S    S   U   U   C   C   C   C  E        S    S   S    S
>    SSSS     UUU     CCC     CCC   EEEEEEE   SSSS     SSSS
.
SUCCESS: "p_ksltry"
.
> Individual Test Case Results:
>
SUCCESS - p_ksltry.UT_F1: EQ "test 1" Expected "1" and got "1"
>
>
> Errors recorded in utPLSQL Error Log:
>
> NONE FOUND
ut_teardown.....!

PL/SQL procedure successfully completed.

Depending on how similar or different our setups are, you might need to tweak what I did, but hopefully it should give you a starting point.

Thanks for your patience, please let me know how you get on.

[kslimani]

Hi,
thank you for your answer.
I already tried to call utplsql.test when logged as swioper…
However, there is a problem (2 problems) with executing utplsql.test while logged as swioper :

---

SQL> exec utp.utplsql.test('p_ksltry', recompile_in=>false);
BEGIN utp.utplsql.test('p_ksltry', recompile_in=>false); END;

*
ERREUR Ó la ligne 1 :
ORA-02291: violation de contrainte d'intÚgritÚ (UTP.UTR_ERROR_OUTCOME_FK) - clÚ
parent introuvable
ORA-06512: Ó "UTP.UTRERROR", ligne 149
ORA-06512: Ó "UTP.UTRERROR", ligne 324
ORA-06512: Ó "UTP.UTROUTCOME", ligne 146
ORA-01400: impossible d'insÚrer NULL dans ("UTP"."UTR_OUTCOME"."RUN_ID")
ORA-06512: Ó "UTP.UTRESULT2", ligne 72
ORA-06512: Ó "UTP.UTASSERT2", ligne 137
ORA-06512: Ó "UTP.UTASSERT", ligne 49
ORA-06512: Ó "UTP.UTPLSQL", ligne 1074
ORA-02289: la sÚquence n'existe pas
ORA-06512: Ó ligne 1

---

For the first problem, I disabled the FK constraint but the second problem remains.
With utp, i grant execute on utplsql and utassert to swioper.
I have the feeling which it is necessary to grant more utp-privileges (all?) to swioper…
Hummm ...

[Paul Walker]

Just out of interest, if you put both packages ("p_ksltry" and "ut_p_ksltry") into the "UTP" schema, then run the test as "UTP", what happens?

[kslimani]

Hi,

I will do it. Nevertheless, this causes a potential problem from my point of view : “architecture” problem (structure of an application).
Indeed, put the package p_ksltry (which contains “applicative” code) in a purely “technical” schema intended only to unit tests is not very "clean" approache (in term of application design and delivery process). At least, i will duplicate
my applicative code towards a tests schema with the risks on the security level that could generate. The other option would be to consider "UTP" user like an applicative user (“functional” user or schema) and that can also generate risks in term of security…
I don't know what think you and which is your opinion about this ... ?

But in a purely technical interest, i do what you propose and i keep you informed.

Thank you

[kslimani]

Hi,

please excuse me for this delay to answer.
When using UTP (the package to be tested and the corresponding ut package ), that works !

[kslimani]

Thank you for time that you passed to answer me.
Anyway, with my case, I couldn't use the framework utPLSQL!
To summarize, so that works, it is necessary either to duplicate application code in the schema UTP, or to extract all the objects from the UTP schema and to put them (to copy) in an application schema (solution even worse…).

In fact, ideally, it would be fun if i can debug the call of the proc "test" of the package "utplsql".
I will make it when i will have a little free time and I would keep you informed.

Thanks

[Svyatoslav Chatchenko]

Hi. Please try to do this:

grant execute on swioper.UT_P_KSLTRY to utp
grant execute on swioper.P_KSLTRY to utp

and run test from UTP schema

BEGIN 
  utplsql.trc;
  utplsql.test( package_in => 'p_ksltry', recompile_in => false, owner_in => 'swioper'); 
END;

Please attach log.

To summarize, so that works, it is necessary either to duplicate application code in the schema UTP, or to extract all the objects from the UTP schema and to put them (to copy) in an application schema (solution even worse…).

well, thats not true. Your architecture can be like this:
- UTP schema contains only utPLSQL framework
- SWIOPER schema containts application code (code that need to be tested eg. p_ksltry) and test packages (eg. ut_p_ksltry)
That should work! I have the same architecture in my project.

Also you should be abble to run test directly from you SWIOPER schema. Event without additional grants, that I mentioned before.

Please try execute from SWIOPER schema this:

BEGIN 
   utplsql.trc; 
   utplsql.test('p_ksltry', recompile_in=>false, owner_in => 'swioper'); 
END;

Please attach log.

[Paul Walker]

I will carry out further testing on different combinations of schemas/users. It might be that some combinations do not work at the moment or some may just need documenting better

[Paul Walker]

Closing this ticket. It's something we should look at as part of v3