Home » SQL & PL/SQL » SQL & PL/SQL » create table in another schema
create table in another schema [message #667184] Wed, 13 December 2017 02:15 Go to next message
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 #667185 is a reply to message #667184] Wed, 13 December 2017 02:40 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

"it didnt work" is not an Oracle error message. You need to show the code that you have written and what happens when you run it. Use SQL*Plus, and copy/paste it all here. Please be sure to enclose it within [code] tags.
Re: create table in another schema [message #667187 is a reply to message #667185] Wed, 13 December 2017 03:00 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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

Re: create table in another schema [message #667189 is a reply to message #667185] Wed, 13 December 2017 03:10 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I do find it hard to debug code that I have not seen.
Re: create table in another schema [message #667191 is a reply to message #667189] Thu, 14 December 2017 00:26 Go to previous messageGo to next message
malu
Messages: 14
Registered: December 2017
Junior Member
Hello Barbara Boehmer,
Thanks a lot Smile
It worked .
Regards,
Malu
Re: create table in another schema [message #667312 is a reply to message #667191] Wed, 20 December 2017 07:56 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
malu wrote on Wed, 13 December 2017 22:26
Hello Barbara Boehmer,
Thanks a lot Smile
It worked .
Regards,
Malu

Procedures are designed to run code multiple times.
What is result when you run procedure 2 or more times?
Previous Topic: Help regarding regexp_substr
Next Topic: How to execute a result of another table.
Goto Forum:
  


Current Time: Thu Mar 28 08:34:09 CDT 2024