⟩ Schema A has some objects and created one procedure and granted to Schema B. Schema B has the same objects like schema A. Schema B executed the procedure like inserting some records. In this case where the data will be stored whether in Schema A or Schema B?
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.
