This discussion is archived
11 Replies Latest reply: Feb 21, 2013 6:55 AM by TurlochO'Tierney RSS

SQL Developer - Sybase copy to oracle....

990721 Newbie
Currently Being Moderated
Hi,

Have anyone tried to use the copy to oracle function on a sybase table using SQL Developer 3.2.20.09? I get the error "Table oracle Failed. Message: Incorrect syntax near the keyword 'CREATE'."

Thanks for your assistance.
SA
  • 1. Re: SQL Developer - Sybase copy to oracle....
    wkobargs Journeyer
    Currently Being Moderated
    Hello,

    I just made a quick test and it works fine. Does that happen to all your tables or only to some? In the latter case, is there anything special to those tables? Can you do an online migration instead of "Copy to Oracle", or does that fail, too?

    Regards
    Wolfgang
  • 2. Re: SQL Developer - Sybase copy to oracle....
    990721 Newbie
    Currently Being Moderated
    Hi Wolfgang,

    Thank you for the quick response. I tried about 5 of Sybase tables, they all came back with the same error on "Copy to Oracle". I am not exactly sure on the online migration. Can I do online migration of a specific table from Sybase to oracle? Could you please give me the menu path to do the online migration for a table?

    Thanks,
    SA
  • 3. Re: SQL Developer - Sybase copy to oracle....
    wkobargs Journeyer
    Currently Being Moderated
    Hello,

    online migration is explained in detail in the online help of SQL Developer. No, it does not give you the option to migrate one single table.

    I had a look at the documentation of COPY to ORACLE, it says:

    To copy selected tables, follow these steps:

    Create and open a database connection for the third-party database. (For migrations other than from Microsoft Access, you should set the third party JDBC driver preference before creating the connection.)

    That is clear, otherwise you wouldn't be able to connect to Sybase which you obviously did. But what is shown if you click on Preferences - Database - Third Party JDBC Drivers. Does it show your jtds driver? And which version?

    Regards
    Wolfgang
  • 4. Re: SQL Developer - Sybase copy to oracle....
    990721 Newbie
    Currently Being Moderated
    Hi Wolfgang,

    Correct, I had to add the drivers in order to connect to Sybase. Here are all the entries I added, not sure if I need all of them though.

    ..\jtds-1.2-dist\jtds-1.2.jar
    ..\sybase\jConnect-5_5\devclasses\jTDS2d.jar
    ..\sybase\jConnect-5_5\classes\jTDS2.jar
    ..\sybase\jConnect-6_0\classes\jTDS3.jar
    ..\sybase\jConnect-6_0\devclasses\jTDS3d.jar
    ..\sybase\JS-12_5\lib\jconn2.jar

    Thanks,
    SA
  • 5. Re: SQL Developer - Sybase copy to oracle....
    wkobargs Journeyer
    Currently Being Moderated
    Hello,

    that looks okay.

    I found out that there was a bug if using Copy to Oracle with Sybase tables. That bug should be fixed in the version of SQL Developer that you are using, but I think it's worth to do this quick check whether that is true.

    The Sybase database from which you want to copy tables, is it the default database? If not, then right click on the database from which you want to copy tables, then click on "Select Default Database" and retry the Copy To Oracle again. If it works then the bug wasn't completely fixed or resurfaced.

    Best regards
    Wolfgang
  • 6. Re: SQL Developer - Sybase copy to oracle....
    990721 Newbie
    Currently Being Moderated
    Hi Wolfgang,

    I came across that solution too in another forum post. I had already tried that with no help. I also tried another suggestion that was mentioned with no luck: In the properties tab of the connection for sybase, add the appropriate database under Retrieve Database.

    Thanks,
    SA
  • 7. Re: SQL Developer - Sybase copy to oracle....
    990721 Newbie
    Currently Being Moderated
    Hi Wolfgang,

    I just noticed one more fact. For the sake of it, I tried the same steps on one small table from Sybase to another oracle (v10.2.0.4.0 destination), it works fine and the tables/rows are populated correctly. But, when I try it on the oracle db that I need it populated (v11.2.0.3.0 destination), it gives me that error. I compared the NLS_DATABASE_PARAMETERS table between the two dbs, they are exactly the same except for the NLS_RDBMS_VERSION (10.2.0.4.0 and 11.2.0.3.0). Even though a higher version should be better, I wonder if the bug was fixed half way on certain version but not all the way to 11i.

    Thanks,
    SA
  • 8. Re: SQL Developer - Sybase copy to oracle....
    wkobargs Journeyer
    Currently Being Moderated
    Hello,

    my Oracle database is 11.2.0.3 and the Copy to Oracle works fine for me. I have no idea why it does not work for you.

    I suggest that you open a Service Request with Oracle. Then we can investigate your issue much deeper as it is possible through this forum.

    Best regards
    Wolfgang
  • 9. Re: SQL Developer - Sybase copy to oracle....
    990721 Newbie
    Currently Being Moderated
    Thanks for looking into this, Wolfgang.

    SA
  • 10. Re: SQL Developer - Sybase copy to oracle....
    TurlochO'Tierney Journeyer
    Currently Being Moderated
    Hi SA,

    The error coming back is in a create statement (presumably create table)- if you trace the session you can see what it is i.e.:
    (This may be too much costly in your time as there are large files to look through)
    Either way you can still open a Service Request with Oracle.

    Introduction to tracing:
    http://totierne.blogspot.co.uk/2013/02/introduction-to-tracing.html

    You may find the command that failed, there might be
    1/Unexpected spacing or
    2/Unexpected characters in the name (table name, column name or oracle type) or
    3/Database types that are not handled.

    Copy to Oracle code generic i.e. works across many databases - so non generic vendor types or user defined types are not handled.

    If the information is not sensitive please post the statement that failed.

    -Turloch
    SQLDeveloper forum
  • 11. Re: SQL Developer - Sybase copy to oracle....
    TurlochO'Tierney Journeyer
    Currently Being Moderated
    Hi SA,

    If possible it would be simplest to post a testcase
    i.e. a Sybase create statement and a dummy row that recreates the issue
    when you try 'copy to Oracle'.

    -Turloch
    SQLDeveloper forum

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points