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  |
manubatham20
Messages: 307
Registered: September 2010
Location: Noida, India
|
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 #578286 is a reply to message #578285] |
Tue, 26 February 2013 10:12  |
cookiemonster
Messages: 9152
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 #578298 is a reply to message #578286] |
Tue, 26 February 2013 11:39 |
Solomon Yakobson
Messages: 1399
Registered: January 2010
|
Senior Member |
|
|
cookiemonster wrote on Tue, 26 February 2013 11:12So 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 |
manubatham20
Messages: 307
Registered: September 2010
Location: Noida, India
|
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 |
cookiemonster
Messages: 9152
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 #578366 is a reply to message #578364] |
Wed, 27 February 2013 05:02 |
manubatham20
Messages: 307
Registered: September 2010
Location: Noida, India
|
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
|
|
|
|
Goto Forum:
Current Time: Wed May 22 22:21:58 CDT 2013
Total time taken to generate the page: 0.21104 seconds
|
] 
Members
Search
Help
Register
Login
Home
