create table in another schema [message #667184] |
Wed, 13 December 2017 02:15 |
malu
Messages: 14 Registered: December 2017
|
Junior Member |
|
|
Hello ,
I have two schemas say schema1 and schema2
In schema1 i have a package pkg1 which creates some backups of tables dynamically.
I have a requirement like . i want to execute pkg1 from schema2
and the backup tables needs to be created in schema2 .
I tried with AUTHID concept . it didnt work.
Anyone pls help
Reagrds,
Malu
|
|
|
|
Re: create table in another schema [message #667187 is a reply to message #667185] |
Wed, 13 December 2017 03:00 |
malu
Messages: 14 Registered: December 2017
|
Junior Member |
|
|
Hello John,
There is no oracle error coming .
when i execute pkg1 from schema2 , it creates the backup tables in schema1 , not in schema2.
Actually i want the tables to create in schema2.
i changed the package to authid current_user , and checked . Still it created in schema1.
Thanks and regards,
Malu
|
|
|
Re: create table in another schema [message #667188 is a reply to message #667187] |
Wed, 13 December 2017 03:07 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
Please see the example below. If this is not sufficient, then post a copy and paste as I have done below of a complete run of your code, especially the package.
-- users and privileges:
SCOTT@orcl_12.1.0.2.0> create user schema1 identified by schema1 quota unlimited on users
2 /
User created.
SCOTT@orcl_12.1.0.2.0> grant create session, create procedure to schema1
2 /
Grant succeeded.
SCOTT@orcl_12.1.0.2.0> create user schema2 identified by schema2 quota unlimited on users
2 /
User created.
SCOTT@orcl_12.1.0.2.0> grant create session, create table to schema2
2 /
Grant succeeded.
-- package in schema1 with authid current_user and execute granted to schema2:
SCOTT@orcl_12.1.0.2.0> connect schema1/schema1
Connected.
SCHEMA1@orcl_12.1.0.2.0> create or replace package pkg1
2 authid current_user
3 as
4 procedure proc1;
5 end pkg1;
6 /
Package created.
SCHEMA1@orcl_12.1.0.2.0> show errors
No errors.
SCHEMA1@orcl_12.1.0.2.0> create or replace package body pkg1
2 as
3 procedure proc1
4 is
5 begin
6 execute immediate 'create table test_tab as select * from dual';
7 end proc1;
8 end pkg1;
9 /
Package body created.
SCHEMA1@orcl_12.1.0.2.0> show errors
No errors.
SCHEMA1@orcl_12.1.0.2.0> grant execute on pkg1 to schema2
2 /
Grant succeeded.
-- execution of packaged procedure in schema1 from schema2, resulting in table in schema2:
SCHEMA1@orcl_12.1.0.2.0> connect schema2/schema2
Connected.
SCHEMA2@orcl_12.1.0.2.0> execute schema1.pkg1.proc1
PL/SQL procedure successfully completed.
SCHEMA2@orcl_12.1.0.2.0> select * from test_tab;
D
-
X
1 row selected.
[Updated on: Wed, 13 December 2017 03:07] Report message to a moderator
|
|
|
|
|
|