Dynamically using different schema names in Oracle

I want to access some Views in Oracle on machine <M01> with a user <user01>.
The views belong to different schemas e.g. user01.view01 and user02.view02.

When I transport my package to another machine <M02> I am facing a different situation:
The viewnames remain the same but the schemas have changed, e.g. user05.view01
and user06.view02.

I have tried to parametrize my Source SQL query but it is restricted to use parameters in the
WHERE-clause and not in the FROM-clause where I would place something like
SELECT *
FROM ?.view01

The main problem here are the differences between machine M01 and M02!
How would you handle this?
Hm, I think the best would be using an Expression in the DataFlow Source task wherein I use a
package variable. The expression would be something like:

"SELECT * FROM " + @.[SchemaName01] + ".view01"

The value of the package variable would be saved in a configuration. The configuration can be machine-dependant so the package doesn't need to be re-compiled.

The only disadvantage is, that I have plenty of SELECT statements which are stored in expressions. Not quite comfortable...

If someone has a better idea I would be graetful.
Fridtjof
|||I haven't found any practical solution.
I believe that this could be a common problem. Any suggestion on this?
|||

Your solution of using expressions sounds like the best way to go for sure. As you have observed this means you may have alot of expressions to handle but is this really so much of a problem?

-Jamie

|||Well I have plenty of SELECTs in my Oracle Data Sources. As you will know, it is not very comfortable developing SQL-Statements and then transforming them into expressions and vice-versa it's even worse.

By the way: I can imagine that one can meet this situation with an SQL Server having different
Schemas although I didn't see that so far.

Fridtjof
|||

Friedel wrote:

As you will know, it is not very comfortable developing SQL-Statements and then transforming them into expressions and vice-versa it's even worse.

I completely agree. What I always do is construct the expression elsewhere. I usually use the expression editor attached to the package's Description property. This is the safest bet as it won't do any real damage in case you press the OK button instead of the Cancel button after building the expression.

Once you have a working expression you can copy/paste it into the Expression property of your variable.

Its far from perfect I know, but it works!

By the way, I know it doesn't help you now but you'll be pleased to know that SP1 will provide an expression editor for the Expression property of a variable.

-Jamie

sql

Dynamically using different schema names in Oracle

I want to access some Views in Oracle on machine <M01> with a user <user01>.
The views belong to different schemas e.g. user01.view01 and user02.view02.

When I transport my package to another machine <M02> I am facing a different situation:
The viewnames remain the same but the schemas have changed, e.g. user05.view01
and user06.view02.

I have tried to parametrize my Source SQL query but it is restricted to use parameters in the
WHERE-clause and not in the FROM-clause where I would place something like
SELECT *
FROM ?.view01

The main problem here are the differences between machine M01 and M02!
How would you handle this?
Hm, I think the best would be using an Expression in the DataFlow Source task wherein I use a
package variable. The expression would be something like:

"SELECT * FROM " + @.[SchemaName01] + ".view01"

The value of the package variable would be saved in a configuration. The configuration can be machine-dependant so the package doesn't need to be re-compiled.

The only disadvantage is, that I have plenty of SELECT statements which are stored in expressions. Not quite comfortable...

If someone has a better idea I would be graetful.
Fridtjof
|||I haven't found any practical solution.
I believe that this could be a common problem. Any suggestion on this?
|||

Your solution of using expressions sounds like the best way to go for sure. As you have observed this means you may have alot of expressions to handle but is this really so much of a problem?

-Jamie

|||Well I have plenty of SELECTs in my Oracle Data Sources. As you will know, it is not very comfortable developing SQL-Statements and then transforming them into expressions and vice-versa it's even worse.

By the way: I can imagine that one can meet this situation with an SQL Server having different
Schemas although I didn't see that so far.

Fridtjof
|||

Friedel wrote:

As you will know, it is not very comfortable developing SQL-Statements and then transforming them into expressions and vice-versa it's even worse.

I completely agree. What I always do is construct the expression elsewhere. I usually use the expression editor attached to the package's Description property. This is the safest bet as it won't do any real damage in case you press the OK button instead of the Cancel button after building the expression.

Once you have a working expression you can copy/paste it into the Expression property of your variable.

Its far from perfect I know, but it works!

By the way, I know it doesn't help you now but you'll be pleased to know that SP1 will provide an expression editor for the Expression property of a variable.

-Jamie

Dynamic views

I havea archive of tables and i want to make a dynamic view for the tables. like a view that shows the last 90 days of data.
is that possible without runnning a script every day or sumthin of that sort?
plz helpI think this will do,

create view myview
as
select * from yourarchivetable where date < dateadd(day, -90, getdate())|||correction,

create view myview
as
select * from yourarchivetable where date > dateadd(day, -90, getdate|||that works ok if the archival table is known. but the archival tables are usally dated (ie name contains the date or year in it) in that case what do u do? the archival tables are monthly tables

say if the view wants last 90 days and today is the 12dec so the view should capture dec, nov, oct, and spet data.

now is this possible ?|||I think what you are looking for here is a partitioned view. It doesn't work exactly the way you want it to, but it should give you what you need with minimal administrative overhead and good performance.

Basically, each of your underlying tables needs to have a check contstraint applied (that should be easy, since you indicate in your post that they are grouped logically by month).

Then you create a view that unions together all the data from the various tables (I'm assuming that the table definitions are the same).

You could probably even figure out a way to programmatically update the view definition on the first day of each month to add the new table (and eliminate the oldest table if you're only going for 90 days).

The check constraints in the underlying tables are important for performance.

Check out Partitioned Views in SQL BOL.

hmscott|||Well rite now i have scripts running running as jobs periodically that keep the views updated for say fiscal year, fiscal calendar year, last 90 days, last 60 days etc.

Dynamic Views

Hi,
I created view like this: "SELECT * FROM TABLE_NAME"
But after change the table structure (adding, deleting, modifiying fields);
when I use select statement (SELECT * FROM VIEW_NAME) view doesn't see new
table structure.
I always drop and create view after structure change.
Is there any way to create "dynamic" view?
Sereza
It is strongly recomended to avoid using SELECT * in the production.
Run sp_refreshview 'view'
"Sergey Amanov" <a@.a.com> wrote in message
news:eyI1mh25EHA.2180@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I created view like this: "SELECT * FROM TABLE_NAME"
> But after change the table structure (adding, deleting, modifiying
fields);
> when I use select statement (SELECT * FROM VIEW_NAME) view doesn't see new
> table structure.
> I always drop and create view after structure change.
> Is there any way to create "dynamic" view?
>
>

Dynamic views

Is it possible to use an IF statement or CASE when creating a view?
I want to something like.
IF Myvariable=n
selelect * from mytable where X=n
Else
selelect * from mytable where X=aYou can use CASE in a view but not IF. You cannot use variables or
parameters in views though. Do this in the WHERE clause when you query
the view.
BTW, don't use SELECT * in views. The results can be unreliable if the
base table changes. List all the required columns by name.
David Portas
SQL Server MVP
--|||no. but you can use stored procedure instead
--
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Geo" wrote:

> Is it possible to use an IF statement or CASE when creating a view?
> I want to something like.
> IF Myvariable=n
> selelect * from mytable where X=n
> Else
> selelect * from mytable where X=a
>
>|||Thanks guys, can I call a SPROC from within a view?
"Geo" <noSpamgbarr@.ibigroup.com> wrote in message
news:e4jdd5TtFHA.1204@.TK2MSFTNGP15.phx.gbl...
> Is it possible to use an IF statement or CASE when creating a view?
> I want to something like.
> IF Myvariable=n
> selelect * from mytable where X=n
> Else
> selelect * from mytable where X=a
>|||Nope. If you need a 'parametrized view' you can achieve that by creating a
table function.
What exactly is the purpose of this view?
ML|||> Thanks guys, can I call a SPROC from within a view?
No. A view is no more than a select statement, you can not use variables,
parameters, dml statements other than "select", etc.
Can you tell us what are you trying to accomplish?
AMB
"Geo" wrote:

> Thanks guys, can I call a SPROC from within a view?
> "Geo" <noSpamgbarr@.ibigroup.com> wrote in message
> news:e4jdd5TtFHA.1204@.TK2MSFTNGP15.phx.gbl...
>
>|||No. You might try using a table-valued function. Table-valued functions
can't call procs either but they do work quite like views and they can
contain procedural code and make use of parameters. See the CREATE
FUNCTION topic in Books Online.
David Portas
SQL Server MVP
--|||Try out UDF returning a TABLE.
UDF will let you pass parameters & can then be used in the FROM clause of a
SELECT statement same way you use tables & views.
Rakesh
"Geo" wrote:

> Is it possible to use an IF statement or CASE when creating a view?
> I want to something like.
> IF Myvariable=n
> selelect * from mytable where X=n
> Else
> selelect * from mytable where X=a
>
>|||You are missing the concept of a VIEW. It is a virtual table, not a
procedure. Do you expect other tables to change on the fly? And CASE
is an expression, not a statement.|||hi geo,
its the other way around
A stored procedure can call a view
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Geo" wrote:

> Thanks guys, can I call a SPROC from within a view?
> "Geo" <noSpamgbarr@.ibigroup.com> wrote in message
> news:e4jdd5TtFHA.1204@.TK2MSFTNGP15.phx.gbl...
>
>

Dynamic Views

Hi,
I created view like this: "SELECT * FROM TABLE_NAME"
But after change the table structure (adding, deleting, modifiying fields);
when I use select statement (SELECT * FROM VIEW_NAME) view doesn't see new
table structure.
I always drop and create view after structure change.
Is there any way to create "dynamic" view?Sereza
It is strongly recomended to avoid using SELECT * in the production.
Run sp_refreshview 'view'
"Sergey Amanov" <a@.a.com> wrote in message
news:eyI1mh25EHA.2180@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I created view like this: "SELECT * FROM TABLE_NAME"
> But after change the table structure (adding, deleting, modifiying
fields);
> when I use select statement (SELECT * FROM VIEW_NAME) view doesn't see new
> table structure.
> I always drop and create view after structure change.
> Is there any way to create "dynamic" view?
>
>

Dynamic view

Is it possible to create dynamic sql views in Sql Server 2005?

I have a requirement of having to change the Sql View depending on the user selection criteria.
Is it possible?

No but you can create a stored procedure with dynamic sql kludge

If you would change the view (with an alter view statement) what would happen when 2 users would hit the view at the same time (problem)

sp with dynamic sql is your best bet (probably including a temp table, I don't know your requirements so that's tough to answer)

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Thanks.

I cannot use stored procedure, any other option to programattically alter the view?|||What is your requirement that means that you have to "dynamically change the view"?|||

Krutika wrote:

Thanks.

I cannot use stored procedure, any other option to programattically alter the view?

No. Well, you can use CASE statements in your view if that helps.|||No, A "view" is always the same list of fields.

Also, if you create a view which says:

CREATE VIEW V_TABLEA AS
SELECT * FROM TABLEA

When you create the view, it caches the list of fields it is going to display. This means if you add or delete a field from TABLEA, these changes will not show in the view. You need to recreate or alter the view to get a new field list.

Your only option is a stored procedure.