Data Replication 2
Results 1 to 4 of 4
Like Tree1Likes
  • 1 Post By DrorHarari

Thread: Use OracleSource with DataSourceViews

  1. #1
    giladma is offline Junior Member
    Join Date
    Apr 2013
    Posts
    3
    Rep Power
    0

    Use OracleSource with DataSourceViews

    Hey all,

    Env : SQL2008R2, VS2008, attunity 1.2 (compatiable for VS2008), Oracle 10g
    I'm connecting to the oracle schema using OracleSource (creating OracleConnection on the connection manager).
    After connection, i opend the componenet in order to select the reqeusted table and cause the schema has L-O-Ts of tables/views, i can't reach the table i need.
    i know i can write Sql statment and this could be a workaround but it also happens (can't reach the requested table) at the Oracle Destination component.
    How can i use Data source views ??

    10x.
    Gilad

  2. #2
    Hein is offline Senior Member
    Join Date
    Dec 2007
    Location
    Nashua, NH - USA.
    Posts
    159
    Rep Power
    12
    Hmmm... an other, more recent, Attunity products (Replicate!) recognized this potential issue and provides a matching pattern to help table name searc, inclusion and exclusion.

    I'm not familiar with OracleSource, but if I had to solve your problem in the here and now I would consider using a special schema on the Oracle side loaded with synonyms for the potentially 'interesting' tables.
    Just script things along the lines of...
    Code:
    select 'create synonym X.' || table_name  || ' for ' || owner || '.' || table_name || ';'
    from all_tables where owner like 'xxx' and table_name like 'xxx'.
    And similar but for GRANT SELECT ON...
    See also
    https://forums.oracle.com/forums/thr...readID=1016067

    Silly example:

    Code:
    SQL> create synonym scott.xxx for SOE.CUSTOMERS;
    Synonym created.
    SQL> GRANT SELECT ON SOE.CUSTOMERS to scott;
    SQL> connect scott/tiger
    Connected.
    
    SQL> select tname from tab;
    TNAME
    ------------------------------
    BONUS
    DEPT
    EMP
    SALGRADE
    XXX
    SQL> select * from xxx where rownum < 3;
    CUSTOMER_ID CUST_FIRST_NAME                CUST_LAST_NAME...

    Just a thought!
    There may well be major caveats with this suggestion! Significant Source DB access for starters.

    Hope this helps a little,
    Hein.

  3. #3
    Join Date
    Sep 2006
    Posts
    233
    Rep Power
    10
    When defining an Oracle Source Component in SSIS, after providing the name of the connection manager and the 'Table Name' data access mode, there is a text box called 'Name of the table of the view'. This text box has a pulldown which can list up to 1000 table names.

    If you press F1 on that text box you can see the following in the help message:

    ...name of the table or the view: Select an available table or view from the database from the list.
    This list contains the first 1000 tables only. If your database contains more than 1000 tables, you
    can type the beginning of a table name or use the (*) wild card to enter any part of the name to
    display the table or tables you want to use...

    So, if you type the prefix of the table name, e.g. LBEW_APPLICATION_P*, and press the pulldown arrow, you will only see the tables that match that given prefix (like LBEW_APPLICATION_PRIORITY, LBEW_APPLICATION_PRICE, etc.). Hence, even if there are (in general) 25000 tables alphabetically before it, the new list would be shortened to just what you are looking for.
    giladma likes this.
    By Dror Harari

    To Find Out more About Attunity Technology:
    Attunity
    or:
    Contact Us

  4. #4
    giladma is offline Junior Member
    Join Date
    Apr 2013
    Posts
    3
    Rep Power
    0
    10x Dror!! It works :-)

    Now,i have moved to my next issue..
    When i select the requested table it detected a column with data type NUMBER as DT_R8 while if i use OleDB Destination it detected it as DT_WSTR.
    Also, on the Oracle Destination it gives me an error on the component itself with the message : "Validation Error. .... [434]: Datatype conversion of input column "SystemID" (480) is not supported." (the DT_R8 above) and that is even when i havn't mapped nothing to it, when i map some DT_R8 column from the pipe to the destination it still gives me the error message.
    Why ?

    Gilad

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •