Home » Server Options » Replication » Does Golden Gate support Table name starting with number? (Golden Gate 11gR2 - Oracle 11gR2 and SQL Server 2008 Enterprise Edition)
Does Golden Gate support Table name starting with number? [message #621785] Mon, 18 August 2014 06:02 Go to next message
Kaustubham
Messages: 5
Registered: August 2014
Junior Member
Hello

We are trying to replicate data from SQL Server to Oracle and facing issue while replicating data from table having it's name starting with number - 01520Appdata

I tried using using various syntax but it did not work for me.

I got the issues on the Source side (of course target side is out of question at this stage)

Following are few options I tried :

Case 1: using dbo."01520Appdata" for adding trandata & for extract and pump parameter files 

Added trandata using dbo."01520Appdata"	- Successful
mentioned dbo."01520Appdata" in extract & pump parameter files - Extract abended with error as - "ERROR   OGG-00899  Table dbo.dbo.01520Appdata does not exist"

Case 2: using dbo."01520Appdata" for adding trandata & "dbo.01520Appdata"  for extract and pump parameter files

Add trandata using "dbo.01520Appdata"	- Failed with error - ERROR: No tables found matching 'dbo.dbo.01520Appdata'
so used dbo."01520Appdata" to add trandata
mentioned "dbo.01520Appdata" in extract & pump parameter files - Even after couple of inserts the dat file as well as rpt file were Not updated - No error, No trail file update for data generation

Case 3 : using "01520Appdata" for adding trandata & for extract and pump parameter files 

Added trandata using "01520Appdata"	- Successful
mentioned dbo."01520Appdata" in extract & pump parameter files - Extract abdended with error as - "ERROR   OGG-00899  Table dbo.dbo.01520Appdata does not exist"


Unfortunately the following link do not have mention of this scenario
http://docs.oracle.com/cd/E35209_01/doc.1121/e29397.pdf

Any suggestions, please?

Thanks and Regards
Kaustubham
Re: Does Golden Gate support Table name starting with number? [message #621786 is a reply to message #621785] Mon, 18 August 2014 06:20 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I don't know anything about Golden Gate. However, as it is still an Oracle database, here's how it goes (maybe you'll get the idea). If you want to create tables with "unusual" names, you have to enclose them into double quotes:
SQL> create table 01520Appdata (col number);
create table 01520Appdata (col number)
             *
ERROR at line 1:
ORA-00903: invalid table name


SQL> create table "01520Appdata" (col number);

Table created.

So far so good. Disadvantages? From now on, you'll ALWAYS have to reference that table exactly as you created it: double quotes, case sensitive. Have a look:
SQL> insert into "01520Appdata" (col) values (100);

1 row created.

SQL> select * from "01520APPDATA";
select * from "01520APPDATA"
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select * from "01520appdata";
select * from "01520appdata"
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select * from "01520Appdata";

       COL
----------
       100

SQL>
Re: Does Golden Gate support Table name starting with number? [message #621827 is a reply to message #621786] Mon, 18 August 2014 14:05 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

Is that schema replication or table replication ?

Can you post your source & target EXTRACT parameter file as well as PARAM file ? I want to make sure where you used doublequote

From Page 30 in Given link .

Quote:
MAP "fin"."accTAB", TARGET "fin"."accTAB",
COLMAP ("Account" = "Acct",
"Balance" = "Bal",
"Branch" = "Branch");
-- Get INSERT operations
GETINSERTS
-- MAP statement to map source objects to target objects and
-- filter to apply only the 'NY' branch data.
MAP "fin"."teller", TARGET "fin"."tellTAB",
WHERE ("Branch" = 'NY');
Note the use of single and double quote marks in the Replicat example in Figure 5. For
databases that require quote marks to enforce case-sensitive object names, such as Oracle,
you must enclose case-sensitive object names within double quotes in the parameter file as
well. Note that to specify case-sensitive column names in double quotes, you must use the
USEANSISQLQUOTES parameter in the GLOBALS file; otherwise strings in double quotes are
interpreted as literals. For more information about specifying names and literals, see
"Specifying object names in Oracle GoldenGate input" on page 37


thank you, Babu
Re: Does Golden Gate support Table name starting with number? [message #621864 is a reply to message #621827] Tue, 19 August 2014 04:29 Go to previous messageGo to next message
Kaustubham
Messages: 5
Registered: August 2014
Junior Member
Thank you Littlefoot and Babu

@Littlefoot
Thanks for your comments but at this stage tables are already created on the source system by the client, with tablenames starting with numbers

@Babu
The target extract does not come in picture as the trail is not being generated on the source itself

I have tried various options on source nothing worked!
Quote:

"schema.tablename"
schema."tablename"
[schema].[tablename]


Note that during all this I used the parameter 'USEANSISQLQUOTES' in GLOBALS parameter file

Thanks and Regards
Kaustubham
Re: Does Golden Gate support Table name starting with number? [message #622069 is a reply to message #621864] Wed, 20 August 2014 13:50 Go to previous message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

With single quote you added USEANSISQLQUOTES in global file ?

if yes please remove and try.

[Updated on: Wed, 20 August 2014 14:02]

Report message to a moderator

Previous Topic: Golden Gate Replication : Error while using Column with white space (and special character)
Next Topic: How to know the log generation for an update statement
Goto Forum:
  


Current Time: Thu Mar 28 16:16:43 CDT 2024