Home » SQL & PL/SQL » SQL & PL/SQL » DBMS_SQL usages (Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production)
DBMS_SQL usages [message #578187] Mon, 25 February 2013 08:53 Go to next message
manubatham20
Messages: 432
Registered: September 2010
Location: Champaign, IL
Senior Member

Hi,

This is the first time I came across through DBMS_SQL.

I have a scenario which is:

1. User A have a table "test_tbl".
2. Another user B, that has the select priviledge on "test_tbl" of A user.
I just want to execute query from user B without giving schema prefix
select * from A.test_tbl


From User B, can I set the schema for table A, in DBMS_SQL while executing the query?
So the query should be look like-
select * from test_tbl


Thanks,
Manu
Re: DBMS_SQL usages [message #578188 is a reply to message #578187] Mon, 25 February 2013 08:59 Go to previous messageGo to next message
BlackSwan
Messages: 21938
Registered: January 2009
Senior Member
CREATE SYNONYM

http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_7001.htm#SQLRF01401
Re: DBMS_SQL usages [message #578191 is a reply to message #578188] Mon, 25 February 2013 09:21 Go to previous messageGo to next message
manubatham20
Messages: 432
Registered: September 2010
Location: Champaign, IL
Senior Member

I am Sorry, but synonym is not an option for me.
Re: DBMS_SQL usages [message #578192 is a reply to message #578191] Mon, 25 February 2013 09:27 Go to previous messageGo to next message
BlackSwan
Messages: 21938
Registered: January 2009
Senior Member
>From User B, can I set the schema for table A, in DBMS_SQL while executing the query?
I give up.
can you?
What happens when you do it?
Re: DBMS_SQL usages [message #578193 is a reply to message #578191] Mon, 25 February 2013 09:28 Go to previous messageGo to next message
Michel Cadot
Messages: 57603
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
alter session set current_schema=A;


Regards
Michel
Re: DBMS_SQL usages [message #578197 is a reply to message #578193] Mon, 25 February 2013 09:44 Go to previous messageGo to next message
manubatham20
Messages: 432
Registered: September 2010
Location: Champaign, IL
Senior Member

Okay.

Thanks Michel.

Now coding in PL/SQL, should I use this thorugh "execute immediate"?
Will it be effective with execute immediate?

Manu
Re: DBMS_SQL usages [message #578199 is a reply to message #578197] Mon, 25 February 2013 09:47 Go to previous messageGo to next message
manubatham20
Messages: 432
Registered: September 2010
Location: Champaign, IL
Senior Member

With effective I mean.

If I alter the session thorugh execute immediate, and then ran the query using dbms_sql.

Query should be executed with the priviledge of user B while schema should be A.

Thanks,
Manu
Re: DBMS_SQL usages [message #578200 is a reply to message #578197] Mon, 25 February 2013 09:47 Go to previous messageGo to next message
cookiemonster
Messages: 10571
Registered: September 2008
Location: Rainy Manchester
Senior Member
yes
Re: DBMS_SQL usages [message #578201 is a reply to message #578199] Mon, 25 February 2013 10:00 Go to previous messageGo to next message
Michel Cadot
Messages: 57603
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just try it, easy, fast and reliable:
SQL> declare
  2     handle int := dbms_sql.open_cursor; 
  3  begin 
  4     dbms_sql.parse (handle, 'select * from bonus', dbms_sql.native); 
  5     dbms_sql.close_cursor (handle); 
  6  exception
  7    when others then 
  8      if dbms_sql.is_open(handle) then dbms_sql.close_cursor (handle); end if;
  9      raise;
 10  end; 
 11  / 
declare
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at line 9

SQL> declare
  2     handle int := dbms_sql.open_cursor; 
  3  begin 
  4     execute immediate 'alter session set current_schema=scott';
  5     dbms_sql.parse (handle, 'select * from bonus', dbms_sql.native); 
  6     dbms_sql.close_cursor (handle); 
  7  exception
  8    when others then 
  9      if dbms_sql.is_open(handle) then dbms_sql.close_cursor (handle); end if;
 10      raise;
 11  end; 
 12  / 

PL/SQL procedure successfully completed.

Regards
Michel
Re: DBMS_SQL usages [message #578202 is a reply to message #578201] Mon, 25 February 2013 10:11 Go to previous messageGo to next message
manubatham20
Messages: 432
Registered: September 2010
Location: Champaign, IL
Senior Member

I got it, but I got another confusion.

Does oracle reads the contents inside "execute immediate" on compile time???
Because in execute immediate, we can write anything... So now oracle knows at compile time that schema has been changed to scott.
And now it validates the bonus table inside Scott schema.

Ahhhhh, if its true, I have to revise all concepts for "execute immediate"

Thanks,
Manu
Re: DBMS_SQL usages [message #578204 is a reply to message #578202] Mon, 25 February 2013 10:14 Go to previous messageGo to next message
Michel Cadot
Messages: 57603
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Does oracle reads the contents inside "execute immediate" on compile time???


No.

Quote:
And now it validates the bonus table inside Scott schema.


In my example, it does it at execution time. ("it" is dbms_sql.parse function)

Regards
Michel

[Updated on: Mon, 25 February 2013 10:15]

Report message to a moderator

Re: DBMS_SQL usages [message #578207 is a reply to message #578204] Mon, 25 February 2013 10:57 Go to previous messageGo to next message
cookiemonster
Messages: 10571
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you want to do this without dynamic sql then your only choices are:
1) add the schema prefix in the code
2) use synonyms
Re: DBMS_SQL usages [message #578215 is a reply to message #578207] Mon, 25 February 2013 13:04 Go to previous message
manubatham20
Messages: 432
Registered: September 2010
Location: Champaign, IL
Senior Member

Thanks Michel.
Previous Topic: Difference between variables declared under Begin and Declare
Next Topic: Is employee is manager?
Goto Forum:
  


Current Time: Wed Apr 16 14:53:24 CDT 2014

Total time taken to generate the page: 0.09383 seconds