Home » SQL & PL/SQL » SQL & PL/SQL » changing schema using PLSQL
changing schema using PLSQL [message #191090] Mon, 04 September 2006 08:55 Go to next message
rampratap
Messages: 50
Registered: November 2004
Location: New Delhi
Member

Hi,

I want to change schema by running a PLSQL code:
I am able to do it but my approch is not corrct can any one
correct it ?.

I have two schema ( 'SINGH' and 'SCOTT') EMP table is in Scott only;

what I tried

Step 1.

begin
execute immediate 'alter session set current_schema= scott';
declare
cursor c1 is select * from emp;
begin
for r1 in c1 loop
dbms_output.put_line(r1.ename);
end loop;
end;
end;

This does not work at all .


Step 2.
begin
execute immediate 'alter session set current_schema = scott';
end;
/

then next block

declare
cursor c1 is select ename from emp;
begin
for r1 in c1 loop
dbms_output.put_line(r1.ename);
end loop;
end;
/

can any body help me combine these two block in one ?.
I tried even by creating two procedure and calling them in one block but no user.

Thanks

Re: changing schema using PLSQL [message #191092 is a reply to message #191090] Mon, 04 September 2006 09:17 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Maybe a daft question, but why not simply:

  declare
    cursor c1 is select * from scott.emp;
  begin
    for r1 in c1 loop
      dbms_output.put_line(r1.ename);
    end loop;
  end;
Re: changing schema using PLSQL [message #191109 is a reply to message #191092] Mon, 04 September 2006 11:39 Go to previous messageGo to next message
rampratap
Messages: 50
Registered: November 2004
Location: New Delhi
Member

Hi,

Sorry dear this will work provided singh has select right on scott.emp. I wanted with out using qualifier i shold be able to
change my schema to scott.
Re: changing schema using PLSQL [message #191111 is a reply to message #191109] Mon, 04 September 2006 11:54 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
So you think you can create a procedure like that as user sing, without him having select rights?
Oracle's security mechanism is not THAT easy to fool!
Re: changing schema using PLSQL [message #191112 is a reply to message #191111] Mon, 04 September 2006 12:24 Go to previous messageGo to next message
rampratap
Messages: 50
Registered: November 2004
Location: New Delhi
Member

Hi,

Sorry Frank my aim is not like that . But this question was asked in an interview so I tried. Here are my steps . I want to is it possible to change schema and use anothers objects ?

Please go Through and help me .

SQL> select user from dual;

USER
------------------------------
SINGH

Elapsed: 00:00:00.00

SQL> begin
2 execute immediate 'alter session set current_schema=scott';
3 end;
4 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL> set serveroutput on
SQL> declare
2 cursor c1 is select * from emp;
3 begin
4 for r1 in c1 loop
5 dbms_output.put_line(r1.ename);
6 end loop;
7 end;
8 /
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

PL/SQL procedure successfully completed.



Thanks
Re: changing schema using PLSQL [message #191163 is a reply to message #191112] Tue, 05 September 2006 01:46 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If you would create a stored function as user scott, HIS tables will be used by default:
SQL> connect scott/tiger@xe
Connected.
SQL> drop table my_table
  2  /

Table dropped.

SQL> create table my_table
  2  ( owner varchar2(10))
  3  /

Table created.

SQL> insert into my_table values ('SCOTT')
  2  /

1 row created.

SQL> commit
  2  /

Commit complete.

SQL> create or replace function my_tab_contents
  2  return varchar2
  3  is
  4    l_retval varchar2(10);
  5  begin
  6    select owner
  7    into   l_retval
  8    from   my_table
  9    ;
 10    return l_retval;
 11  end my_tab_contents;
 12  /

Function created.

SQL> drop public synonym my_tab_contents
  2  /

Synonym dropped.

SQL> create public synonym my_tab_contents for my_tab_contents
  2  /

Synonym created.

SQL> grant execute on my_tab_contents to singh
  2  /

Grant succeeded.

SQL> connect singh/singh@xe
Connected.
SQL> drop table my_table
  2  /

Table dropped.

SQL> create table my_table
  2  ( owner varchar2(10))
  3  /

Table created.

SQL> insert into my_table values ('SINGH')
  2  /

1 row created.

SQL> commit
  2  /

Commit complete.

SQL> select my_tab_contents
  2  from   dual
  3  /

MY_TAB_CONTENTS
------------------
SCOTT
Previous Topic: LANGUAGE SETTING
Next Topic: OUT JOIN
Goto Forum:
  


Current Time: Thu Dec 08 08:36:10 CST 2016

Total time taken to generate the page: 0.09300 seconds