Home » SQL & PL/SQL » Client Tools » Dynamic Schema Name at runtime. (Oracle 10.2 [10g] - UNIX)
Dynamic Schema Name at runtime. [message #532401] Tue, 22 November 2011 05:37 Go to next message
fortunethiyagu
Messages: 94
Registered: December 2006
Member

SRCDEV is my source scehma and
TRGDEV is my target schema.

SRCDEV schema table data will be loaded into TRGDEV schema respective tables by applying certain business rules.

After loading SRCDEV tables to TRGDEV tables, i am trying to find eliminated rows in SRCDEV tables due to business rules applied.[requirement].

i am doing it in this way

(SRCDEV.table) minus (TRGDEV.table);


The problem here is my schema name will be changed in every environemnt. for example,

for DEV --> SRCDEV 
for TST --> SRCTST
for PRD --> SRCPRD.


so i tried following; but it is not working.
Could you please advise how to solve this?

--SRCDEV SCHEMA -- SRC TABLE

CREATE TABLE SRC (SNO INT, PROD_CD VARCHAR2(100));

INSERT INTO SRC VALUES (1,'SOOO1');
INSERT INTO SRC VALUES (2,'S1OO1');
INSERT INTO SRC VALUES (3,'SO1O1');
INSERT INTO SRC VALUES (4,'SOO11');
INSERT INTO SRC VALUES (5,'SOOO0');
INSERT INTO SRC VALUES (6,'TOO11');
INSERT INTO SRC VALUES (7,'TOOO0');

--TRGDEV SCHEMA -- TRG TABLE

CREATE TABLE TRG (SNO INT, PROD_CD VARCHAR2(100));

INSERT INTO TRG 
  SELECT * FROM SRC WHERE PROD_CD LIKE 'S%';
  
--QUERY FOR VALIDATION

SELECT * FROM &&src_schema.SRC
MINUS
SELECT * FROM &&trg_schema.TRG


&&src_schema IS THIS CORRECT SYNTAX?
Re: Dynamic Schema Name at runtime. [message #532403 is a reply to message #532401] Tue, 22 November 2011 06:02 Go to previous message
Michel Cadot
Messages: 57612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SELECT * FROM &&src_schema..SRC
MINUS
SELECT * FROM &&trg_schema..TRG


You have to double the dot. The first tells SQL*Plus the variable name ends and the second one for the SQL <schema>.<object> syntax.

Regards
Michel

Previous Topic: supressing output
Next Topic: delete table works in Toad, but not in SQL*Plus on UNIX
Goto Forum:
  


Current Time: Sat Apr 19 23:25:50 CDT 2014

Total time taken to generate the page: 0.09377 seconds