Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

using substitution variables for a schema owner? 1

Status
Not open for further replies.

sjh

Programmer
Joined
Oct 29, 2001
Messages
263
Location
US
Hi,

I would like to use a substitution variable to store the name of thw schema owner. I have something like this:

define sv_SchemaName = 'SUSIE';
CREATE VIEW &&sv_SchemaName.TEST_VIEW
blah blah

BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname =>'&&sv_SchemaName', tabname => 'TEST_VIEW');
END;

The variable comes out as SUSIETEST_VIEW without the dot ("."), so it breaks the CREATE VIEW code. It does work for the DBMS_STATS line, though. Is there a way to remedy this?

Thank you for your help!

Susie
 
Yes, Susie, you can solve this problem by using two successive periods as follows:
Code:
define sv_SchemaName = 'SUSIE';
CREATE VIEW &&sv_SchemaName[b]..[/b]TEST_VIEW
et cetera
Let us know if this resolves your need.


[santa]Mufasa
(aka Dave of Sandy, Utah, USA)

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.
 
Thank you Mufasa for your help! Your advice worked successfully!

Susie
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top