Home » SQL & PL/SQL » SQL & PL/SQL » Calling other procedures from autonomous procedure (Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production)
Calling other procedures from autonomous procedure [message #578282] Tue, 26 February 2013 09:15 Go to next message
manubatham20
Messages: 464
Registered: September 2010
Location: Champaign, IL
Senior Member

Hi,

It's somewhat related to my previous post
DBMS_SQL Usage

But here I tried to capture whole scenario.
Scenario is like below:

create user a identified by a;
grant connect,resource to a;

create user b identified by b;
grant connect,resource to b;

create user c identified by c;
grant connect,resource to c;

connect a/a

create or replace procedure a_abc as
begin
execute immediate 'alter session set current_schema = C';
dbms_output.put_line('In A_ABC Schema '||sys_context( 'userenv', 'current_schema' ));
end;
/

create or replace procedure a_mno as
PRAGMA AUTONOMOUS_TRANSACTION;
begin
a_abc;
dbms_output.put_line('In A_MNO Schema '||sys_context( 'userenv', 'current_schema' ));
end;
/


create or replace procedure a_xyz as
cnt number;
L_CUR              integer := DBMS_SQL.OPEN_CURSOR;
L_RET              number;

begin
a_mno;

    DBMS_SQL.PARSE(L_CUR, 'select count(*) from test', DBMS_SQL.native);
    L_RET := DBMS_SQL.execute(L_CUR);
    DBMS_SQL.CLOSE_CURSOR(L_CUR);

dbms_output.put_line('In A_XYZ Schema'||sys_context( 'userenv', 'current_schema' ));
end;
/

grant execute on A_ABC to b
/
grant execute on A_MNO to b
/
grant execute on A_XYZ to b
/

connect c/c

create table test (a number)
/

insert into test values (1)
/

grant select on test to b
/

connect b/b

set serveroutput on
exec a.a_xyz;


And I am getting error:

In A_ABC Schema A
In A_MNO Schema A
BEGIN a.a_xyz; END;

*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SQL", line 1199
ORA-06512: at "A.A_XYZ", line 9
ORA-06512: at line 1


Not getting why the schema is not changing... and how to resolve the error.

Regards,
Manu

[Updated on: Tue, 26 February 2013 09:49]

Report message to a moderator

Re: Calling other procedures from autonomous procedure [message #578285 is a reply to message #578282] Tue, 26 February 2013 10:03 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
It's not the schema change that's the problem, it's the fact that you haven't granted select on test to b.
However the fact that sys_context( 'userenv', 'current_schema' )) shows the original schema even after it's been changed seems suspect.
Re: Calling other procedures from autonomous procedure [message #578286 is a reply to message #578285] Tue, 26 February 2013 10:12 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
Actually it's not just the missing priv, though obviously that's required. With it added we get:
SQL> select sys_context( 'userenv', 'current_schema' ) from dual;

SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
--------------------------------------------------------------------------------
B

SQL> set serveroutput on
SQL> exec b_xyz;
In B_ABC Schema B
In B_MNO Schema B
BEGIN b_xyz; END;

*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SYS_SQL", line 906
ORA-06512: at "SYS.DBMS_SQL", line 39
ORA-06512: at "B.B_XYZ", line 9
ORA-06512: at line 1


SQL> select sys_context( 'userenv', 'current_schema' ) from dual;

SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
--------------------------------------------------------------------------------
C

SQL> exec b_xyz;
BEGIN b_xyz; END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'B_XYZ' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


SQL> exec b.b_xyz;
In B_ABC Schema B
In B_MNO Schema B
BEGIN b.b_xyz; END;

*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SYS_SQL", line 906
ORA-06512: at "SYS.DBMS_SQL", line 39
ORA-06512: at "B.B_XYZ", line 9
ORA-06512: at line 1



So current_schema has changed, but not within the procedure
Re: Calling other procedures from autonomous procedure [message #578289 is a reply to message #578286] Tue, 26 February 2013 10:32 Go to previous messageGo to next message
manubatham20
Messages: 464
Registered: September 2010
Location: Champaign, IL
Senior Member

So how to change it?
Re: Calling other procedures from autonomous procedure [message #578298 is a reply to message #578286] Tue, 26 February 2013 11:39 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2033
Registered: January 2010
Senior Member
cookiemonster wrote on Tue, 26 February 2013 11:12
So current_schema has changed, but not within the procedure


First of all, OP's whole design is flawed. Secondly, you need to understand differences between definer right and invoker rights:

SQL> connect scott@orcl/tiger
Connected.
SQL> show user
USER is "SCOTT"
SQL> select  sys_context('userenv','current_schema')
  2    from  dual
  3  /

SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
--------------------------------------------------------------------------------
SCOTT

SQL> desc u1_tbl
ERROR:
ORA-04043: object u1_tbl does not exist


SQL> desc u1.u1_tbl
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 N                                                  NUMBER

SQL> select  object_name,
  2          object_type
  3    from  dba_objects
  4    where owner = 'U1'
  5  /

OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
-------------------
U1_TBL
TABLE


SQL> create or replace
  2    procedure p1
  3      is
  4          v_cnt number;
  5      begin
  6          dbms_output.put_line('BEFORE ALTER SESSION: ' || sys_context('userenv','current_schema'));
  7          execute immediate 'alter session set current_schema = u1';
  8          dbms_output.put_line('AFTER ALTER SESSION: ' || sys_context('userenv','current_schema'));
  9          execute immediate 'select count(*) from u1_tbl' into v_cnt;
 10          dbms_output.put_line(v_cnt);
 11  end;
 12  /

Procedure created.

SQL> set serveroutput on
SQL> exec p1
BEFORE ALTER SESSION: SCOTT
AFTER ALTER SESSION: SCOTT
BEGIN p1; END;

*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SCOTT.P1", line 8
ORA-06512: at line 1


SQL> select  sys_context('userenv','current_schema')
  2    from  dual
  3  /

SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
--------------------------------------------------------------------------------
U1

SQL> connect scott@orcl/tiger
Connected.
SQL> show user
USER is "SCOTT"
SQL> select  sys_context('userenv','current_schema')
  2    from  dual
  3  /

SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
--------------------------------------------------------------------------------
SCOTT

SQL> desc u1_tbl
ERROR:
ORA-04043: object u1_tbl does not exist


SQL> desc u1.u1_tbl
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 N                                                  NUMBER

SQL> select  object_name,
  2          object_type
  3    from  dba_objects
  4    where owner = 'U1'
  5  /

OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
-------------------
U1_TBL
TABLE


SQL> create or replace
  2    procedure p1
  3      authid current_user
  4      is
  5          v_cnt number;
  6      begin
  7          dbms_output.put_line('BEFORE ALTER SESSION: ' || sys_context('userenv','current_schema'));
  8          execute immediate 'alter session set current_schema = u1';
  9          dbms_output.put_line('AFTER ALTER SESSION: ' || sys_context('userenv','current_schema'));
 10          execute immediate 'select count(*) from u1_tbl' into v_cnt;
 11          dbms_output.put_line(v_cnt);
 12  end;
 13  /

Procedure created.

SQL> set serveroutput on
SQL> exec p1
BEFORE ALTER SESSION: SCOTT
AFTER ALTER SESSION: U1
0

PL/SQL procedure successfully completed.

SQL> select  sys_context('userenv','current_schema')
  2    from  dual
  3  /

SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
--------------------------------------------------------------------------------
U1

SQL> 


But using invoker rights will not do OP any good. Docs clearly state: "This setting changes the current schema, but it does not change the session user or the current user, nor does it give the session user any additional system or object privileges for the session". The only reason procedure was able to select from table u1_tbl is session user SCOTT has select privilege on it but it forced me to use dynamic SQL, which is obviously bad design.

SY.
Re: Calling other procedures from autonomous procedure [message #578344 is a reply to message #578298] Wed, 27 February 2013 03:00 Go to previous messageGo to next message
manubatham20
Messages: 464
Registered: September 2010
Location: Champaign, IL
Senior Member

Sir,

I think I am aware about the Definer and Invoker rights, and I tried that before as well.

Still I don't know why it's not working for me. Can you please suggest changes in my code.

create user a identified by a;
grant connect,resource to a;

create user b identified by b;
grant connect,resource to b;

create user c identified by c;
grant connect,resource to c;

connect a/a

create table a_schema_table (a number)
/

insert into a_schema_table values (1)
/

create or replace procedure a_abc 
authid current_user
as
begin

execute immediate 'alter session set current_schema = C';
dbms_output.put_line('In A_ABC, Schema = '||sys_context( 'userenv', 'current_schema' ));

end;
/

create or replace procedure a_mno
authid current_user
as
PRAGMA AUTONOMOUS_TRANSACTION;

cnt 	number;
L_CUR   integer := DBMS_SQL.OPEN_CURSOR;
L_RET   number;

begin

 select count(*) into cnt from a_schema_table;
 dbms_output.put_line ('In A_MNO, First select succeeded');
 
 a_abc;
 dbms_output.put_line('In A_MNO, Schema = '||sys_context( 'userenv', 'current_schema' ));

 select count(*) into cnt from a_schema_table;
 dbms_output.put_line ('In A_MNO, Second select succeeded');

 execute immediate 'alter session set current_schema = C';
 dbms_output.put_line('In A_MNO, Schema = '||sys_context( 'userenv', 'current_schema' ));

 select count(*) into cnt from a_schema_table;
 dbms_output.put_line ('In A_MNO, Third select succeeded');

 DBMS_SQL.PARSE(L_CUR, 'select count(*) from test', DBMS_SQL.native);
 L_RET := DBMS_SQL.execute(L_CUR);
 DBMS_SQL.CLOSE_CURSOR(L_CUR);

end;
/


create or replace procedure a_xyz as
result_count number;

begin

 select count(*) into result_count from  a_schema_table;
 if result_count > 0 then
  a_mno;
 end if;
 dbms_output.put_line('In A_XYZ, Schema = '||sys_context( 'userenv', 'current_schema' ));

end;
/

grant execute on A_ABC to b
/
grant execute on A_MNO to b
/
grant execute on A_XYZ to b
/

connect c/c

create table test (a number)
/

insert into test values (1)
/

grant select on test to b
/

connect b/b

set serveroutput on
exec a.a_xyz;



I am building a pluging for existing system, so I have very less control over the things which are already there, you can say my design as bad desing. Any alternative is always welcome.

Regards,
Manu
Re: Calling other procedures from autonomous procedure [message #578354 is a reply to message #578344] Wed, 27 February 2013 04:07 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
Changes to the current schema are only picked up by procedures that are invoker rights.
Your first example works if you make a_xyz invoker rights.

But really the correct way to do this is either create a synonym or hard code the schema name in the code. I don't see why the fact that your doing a plugin for an existing system prevents this.
Re: Calling other procedures from autonomous procedure [message #578364 is a reply to message #578354] Wed, 27 February 2013 04:55 Go to previous messageGo to next message
manubatham20
Messages: 464
Registered: September 2010
Location: Champaign, IL
Senior Member

Ummm, If I will hardcode the schema name with query, it will be for that particular schema. While I want that user can reuse its query with multiple schemas having same information...
Re: Calling other procedures from autonomous procedure [message #578366 is a reply to message #578364] Wed, 27 February 2013 05:02 Go to previous message
manubatham20
Messages: 464
Registered: September 2010
Location: Champaign, IL
Senior Member

Also in my second scenario your suggestion is not working (I got it why... Well, I have to admit my mistake, I was aware about definer and invoker rights, but was doing mistake in using them).

Second scenario was very near to my acutal scenario.

Waiting few more hours for any magical comment, otherwise have to put schema name in query.

Regards,
Manu
Previous Topic: Handle exception Invalid identifier
Next Topic: Avoid divisions by zero
Goto Forum:
  


Current Time: Tue Sep 23 16:02:01 CDT 2014

Total time taken to generate the page: 0.12761 seconds