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: AdministratorSchema1 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
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
Read Online Oracle Scenarios Job Interview Questions And Answers
Top Oracle Scenarios Questions
☺ | Explain about the oracle disaster recovery scenarios? |
☺ | Explain What is Testing Scenario? What is scenario based testing? can u explain with an example? |
☺ | Explain What is bulk SQL? |
☺ | Tell us In which situation whether peak time or off peak time we will execute the ANALYZE TABLE command and Why? |
☺ | Explain What are the differences between database designing and database modeling? |
Top DB Oracle Categories
☺ | Oracle PL-SQL Interview Questions. |
☺ | Oracle DBA Interview Questions. |
☺ | Oracle D2K Interview Questions. |
☺ | OCI Interview Questions. |
☺ | Oracle RMAN Interview Questions. |