Multiple Oracle Owners


G

Guest

We want to start using VB.2005 while adding new functionality to one of our
large VB.6 applications that supports multiple clients. To segment data, we
have an "application" owner, and multiple "client_ID" owners in the Oracle 9i
database. Tables that are shared between all companies are put under the
application owner. Each client has its own set of tables. They have the
same structure, but different data.

In our current VB 6 code, we have statements like:
strSql_stmt = "select owner from application.client_sec_tbl where client_id
= ?"

This returns the oracle "owner" which we store in the variable Clientdb.
This is then used to create queries later in our code:

strSql_stmt = "SELECT COUNTRY_CD" & _
", PROFIT_CTR" & _
", COUNTRY_DESC" & _
", TAX_TYPE" & _
" FROM " & Clientdb & ".intl_country_tbl" & _
" ORDER BY country_cd"

When we use the dataset designer in VB.2005, and drag tables from one of the
"owners", it hard codes the database owner name in the code generated.

<CommandText>SELECT COUNTRY_CD, PROFIT_CTR, COUNTRY_DESC, TAX_TYPE,
ADDR_HYGIENE_IND, STATE_IN_CITY_IND FROM
COMPANYX.INTL_COUNTRY_TBL</CommandText>

I want to be able to use the VALUE from the Clientdb variable. Is there an
easy way to do this using the dataset designer in Visual Studio 2005?

When I try to add the clientdb as a variable, in the TableAdapter
Configuration Wizard, I can't use the variable substitution syntax to create
the statement:
SELECT COUNTRY_CD, PROFIT_CTR, COUNTRY_DESC, TAX_TYPE, ADDR_HYGIENE_IND,
STATE_IN_CITY_IND FROM :clientdb.INTL_COUNTRY_TBL

I receive errors when trying to generate the SELECT statement, or when
trying to then start the query builder.

-Roy
 
Ad

Advertisements

K

Kevin Yu [MSFT]

Hi Roy,

It seems the query builder in TableAdapter wizard does not support owner
name as variable in the SQL statement. Can you do it as a parameter? Or
maybe you have to write the SELECT/INSERT/UPDATE/DELETE command manually
without using the wizard.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Ad

Advertisements

G

Guest

I guess when you say onwers you mean scheamas.

Have you thought of using ALTER SESSION SET CURRENT_SCHEMA = 'OWENR' That
should mean that you don't have to prefix the schema name on your tables.

Dion.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Multiple Oracle Clients 0
Multiple Values To Oracle Parametter Object (IN) 3
Multiple oracle homes - need to choose 2
Execute multiple DDL statements for Oracle... 1
Oracle 3
Oracle 4
Oracle.... 1
ORACLE 3

Top