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
Answer: This is an interesting question. So I thought to try it out instead of simply provide a guess.
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.
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 Answer:
This is an interesting question. So I thought to try it out instead of simply provide a guess.
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. Source: CoolInterview.com
By default subprogram executes under the security domain of owner(LEO schema).But if we write AUTHID CURRENT_USER after parameter's list then it will execute under executor's security not owner's security. If we write authid current_user in TEST then rows will be inserted into LEO1 schema after caling TEST rocedure of LEO schema. Source: CoolInterview.com
In exception handling we have some NOT_FOUND and OTHERS. In inner layer we have some NOT_FOUND and OTHERS. While executing which one whether outer layer or inner layer will check first
Dual table explain. Is any data internally storing in dual table. Lot of users are accessing select sysdate from dual and they getting some millisecond differences. If we execute SELECT SYSDATE FROM EMP; what error will we get. Why
If the large table contains thousands of records and the application is accessing 35% of the table which method to use: index searching or full table scan