Logo F2FInterview

Oracle Scenario Interview Questions

Q   |   QA
‹‹ previous12345

A full table scan could be quicker,since it will fetch multiple blocks in one fetch, compared to index->table, index->table for each row.
Measure both ways and decide 

Create package specifications before creatin package body. 

Acronym for simultaneous peripheral operations on-line, spooling refers to putting jobs in a buffer, a special area in memory or on a disk where a device can access them when it is ready. Spooling is useful because devices access data at different rates. The buffer provides a waiting station where data can rest while the slower device catches up. 

We can create a procedure to return REF cursor or VARRAY or PL/SQL Table type out parameters which can return more than one value. 

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. 

‹‹ previous12345

In order to link this F2FInterview's page as Reference on your website or Blog, click on below text area and pres (CTRL-C) to copy the code in clipboard or right click then copy the following lines after that paste into your website or Blog.

Get Reference Link To This Page: (copy below code by (CTRL-C) and paste into your website or Blog)
HTML Rendering of above code: