This is an interesting question. So I thought to try it out instead of simply provide a guess.
Here is the solution:
Schema1 Leo
Table Name emp
Procedure Test
Schema2 Leo1
Table Name emp
Schema 1
SQL>
SQL> CREATE TABLE emp (
2 emp_id NUMBER(2)
3 emp_name VARCHAR2(25)
4 dep_id NUMBER(2)
5 emp_status CHAR(1)
6 );
Table created.
SQL> SQL> CREATE OR REPLACE PROCEDURE test AS
2 BEGIN
3 INSERT INTO emp VALUES (1 'LEO' 2 'Y');
4 COMMIT;
5 END;
6 /
Procedure created.
SQL> EXEC test
PL/SQL procedure successfully completed.
SQL> select * from emp;
EMP_ID EMP_NAME DEP_ID E
---------- ------------------------- ---------- -
1 LEO 2 Y
SQL> GRANT EXECUTE ON test TO leo1;
Grant succeeded.
SQL> GRANT SELECT ON emp TO leo1;
Grant succeeded.
@Schema Leo1
SQL> CREATE TABLE emp AS SELECT * FROM leo.emp WHERE ROWNUM 0;
Table created.
SQL> desc emp
Name Null? Type
----------------------------------------- -------- --------------------------
EMP_ID NUMBER(2)
EMP_NAME VARCHAR2(25)
DEP_ID NUMBER(2)
EMP_STATUS CHAR(1)
Now we created the table exactly as the same structure of emp table in schema leo. Now let us try to execute the procedure.
SQL> EXEC test
BEGIN test; END;
*
ERROR at line 1:
ORA-06550: line 1 column 7:
PLS-00201: identifier 'TEST' must be declared
ORA-06550: line 1 column 7:
PL/SQL: Statement ignored
Guess what if you think this should work (as I did) we are wroung. It took a while for me to figure this out. To execute the procedure from leo1 do as follows:
SQL> exec leo.test
PL/SQL procedure successfully completed.
Now let us check where the rows are being inserted.
@Schema leo1:
SQL> select * from emp;
no rows selected
@Schema leo:
SQL> select * from emp;
EMP_ID EMP_NAME DEP_ID E
---------- ------------------------- ---------- -
1 LEO 2 Y
1 LEO 2 Y
There you go. You added one more row now. So even though you execute the procedure from schema leo1 you inserted a row in leo.
So the ANSWER to the question is : Schema A.
Provide me your input in you have different opinion. All comments are welcome.
3 
0