SQL Server DTS and Oracle
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: SQL Server DTS and Oracle

Hybrid View

  1. #1
    Join Date
    Oct 2000
    Posts
    449
    Hello DBAs:
    I am using SQL Server and Oracle for testing and development. I came across a pretty interesting problem. I am using DTS package in SQL server (Configured OLE for SQL server and Oracle Server) and transformed one sql server table called "employees" that has about 9 rows into Oracle. SQL Server window message said successful. I log into Oracle on Unix and logged in and when I said select * from cat, I can see the tables listed, but when I query it says the table is not existing with ORA 4043.. Any clues ??

    SQL> select * from cat;

    TABLE_NAME TABLE_TYPE
    ------------------------------ -----------
    Employees TABLE
    Employees1 TABLE
    TABLE1 TABLE
    TABLE2 TABLE

    SQL> desc employees
    ERROR:
    ORA-04043: object employees does not exist


    SQL> analyze table employees compute statistics
    2 ;
    analyze table employees compute statistics
    *
    ERROR at line 1:
    ORA-00942: table or view does not exist


    SQL> desc Employees
    ERROR:
    ORA-04043: object Employees does not exist

    Tables Employees and Employees1 are created through DTS and DTS automatically creates. Table1and2 are directly created in Oracle with Create table table1 and so on.. They are normal..

    Thanks, ST

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    how about desc "Employees"

    Code:
    SQL> create table "Employees" as select * from emp;
    
    Table created.
    
    SQL> desc employees
    ERROR:
    ORA-04043: object employees does not exist
    
    SQL> desc "Employees"
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     EMPNO                                     NOT NULL NUMBER(4)
     ENAME                                              VARCHAR2(10)
     JOB                                                VARCHAR2(9)
     MGR                                                NUMBER(4)
     HIREDATE                                           DATE
     SAL                                                NUMBER(7,2)
     COMM                                               NUMBER(7,2)
     DEPTNO                                             NUMBER(2)
    [Edited by pando on 10-12-2001 at 04:57 PM]

  3. #3
    Join Date
    Oct 2000
    Posts
    449
    U the man..

    By the way, how did you figure that out, pando??

    Now I am able to do the transformation of data and object creation with DTS package from SQL server to Oracle.., also retrieval of them..

    Now let me xplain the idea behind this, since I have only crossed stage 1..

    We will have siebel staging db on SQL/NT and prod in ORCL/Unix. We will have a lot of tables in SQL server and swap data betweeen staging and prod db and we need a tool like Saegent or SQL.DTS to achieve that.. Now that I know for 1 table, how can I do for an entire schema, references as a database in SQL server..

    Thanks, ST2000

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    well not figured it out really, kidn of deduced it because a few months back I was playing around creating tables with same names but with upper and lower cases

Posting Permissions

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



Click Here to Expand Forum to Full Width