mg

• Mar 10th, 2005

 

Schema B

naren

• Aug 30th, 2005

 

Though the procedure is executed from schema A, the data will be stored in schema B coz' the object name is same.

ajayesh

• Sep 29th, 2005

 

The records will be inserted into Schema A since the procedure will be created with the default AUTHID as DEFINER hence even though B executes it, it will execute as though A is executing and inserts into Schema A table.

If the procedure is created with the AUTHID as CURRENT_USER then the records get inserted into B tables

leojames22

 Profile Answers by leojames22 

• May 31st, 2006

 

This is an interesting question. So I thought to try it out instead of simply provide a guess.

Here is the solution:

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.

Provide me your input in you have different opinion. All comments are welcome.

Shobhit

• Mar 5th, 2007

 

In this scenario Schema A will be holding new records because Schema A created those objects and Schema A just allowed Schema B to use those objects in this I wish to add one more thing Schema B wont be able to drop those objects because ownership of those objects is with Schema A.

simakas

 Profile Answers by simakas 

• Oct 9th, 2007

 

The answer is Schema B. Read the question once again. It's not said, that how Schema B is executing procedure. "Schema B has the same objects" - that involves a procedure too. In an example above you execute "SQL> exec leo.test" from leo1 (thats Schema B). So, following the example, if leo1 has the same procedure (object) "test", if you connect to leo1 and execute "SQL> exec test", your data will be inserted in leo1 (thats Schema B).

prk_cbz

 Profile Answers by prk_cbz Questions by prk_cbz

• Jan 23rd, 2008

 

Its Schema B.

Regards,
Srinivas

rajesh_bu2001

 Profile Answers by rajesh_bu2001 

• Aug 24th, 2009

 

@leojames22

Well explained jones!!

I have checked it, even if a public synonym is there for the procedure -
leo@mydb> create public synonym test for test;

synonym created.

now even if we try to use this procedure test from the uesr leo1 -

leo1@mydb> exec test;

PL/SQL procedure successfully completed.

the procedure populates the table emp in the schema1 only.

Keep Smilin...
Rajesh

harshljica

 Profile Answers by harshljica 

• Aug 25th, 2009

 

By default in Schema A the table will be updated

eshwar prasad

 Profile Answers by eshwar prasad 

• Sep 2nd, 2010

 

Schema B