Oracle Scenarios Interview Question:

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?

Submitted by: Administrator

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.
Submitted by: Administrator