This discussion is archived
9 Replies Latest reply: Oct 2, 2013 12:02 PM by 1003664 RSS

SQL Developer export forgot to check to include semicolons in DDL

1003664 Newbie
Currently Being Moderated

Hi,

 

I used the SQL Developers Export-function (select tables and right click on them, then select export) to backup tables.

 

However, I forgot to set the 'Abschlusszeichen' (sorry for the german). That apparently means my create table statements are not finished by an ';', and they sinmply don't execute using the @script.sql method, or the sql developers import-function.

 

One sql-file is 2 GB in size, so I currently have no idea how to edit it and add the semicolons manually. Is there any hope to rescue the data?

 

I'm running sql developer 3.2.20.09, oracle Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production as well as Windows 8.1

 

For clarification, this is how the create table part looks:

 

--------------------------------------------------------

--  Datei erstellt -Montag-September-30-2013  

--------------------------------------------------------

--------------------------------------------------------

--  DDL for Table ZZ_BEN_0

--------------------------------------------------------

 

  CREATE TABLE "PIERUSCHM"."ZZ_BEN_0"

   (    "BENUTZER_ID" NVARCHAR2(30),

    "NAME" NVARCHAR2(30),

    "GUELT_VON" NVARCHAR2(30),

    "GUELT_BIS" NVARCHAR2(30),

    "IST_KORREKTUR" NVARCHAR2(30),

    "IST_HALBTAGSKRAFT" NVARCHAR2(30),

    "LADE_DAT" DATE

   )

--------------------------------------------------------

--  DDL for Table ZZ_BEN_1

--------------------------------------------------------

 

  CREATE TABLE "PIERUSCHM"."ZZ_BEN_1"

   (    "BENUTZER_ID" NVARCHAR2(30),

    "NAME" NVARCHAR2(30),

    "GUELT_VON" NVARCHAR2(30),

    "GUELT_BIS" NVARCHAR2(30),

    "IST_KORREKTUR" NVARCHAR2(30),

    "IST_HALBTAGSKRAFT" NVARCHAR2(30),

    "LADE_DAT" DATE

   )

--------------------------------------------------------

--  DDL for Table ZZ_BEN_10

  • 1. Re: SQL Developer export forgot to check to include semicolons in DDL
    User286067 Journeyer
    Currently Being Moderated

    You probably could fix it with regex but wouldn't it be easier to re-generate by specifying proper settings? it just would save time for you.

  • 2. Re: SQL Developer export forgot to check to include semicolons in DDL
    1003664 Newbie
    Currently Being Moderated

    You might be right. I'll look into regular expressions. Regeneration is tough since i left my workplace (I'm a student putting the last finishing touches on my thesis). I was however able to extract the beginning of the huge file using excel, so there is still some hope.

     

    Thanks

  • 3. Re: SQL Developer export forgot to check to include semicolons in DDL
    riedelme Expert
    Currently Being Moderated

    Some editors (like Textpad) support regular expressions with global search and replace as do unix-based stream edit utilities like awk and sed.

     

    'Abschlusszeichen' sounds a lot more impressive than "comma"

  • 4. Re: SQL Developer export forgot to check to include semicolons in DDL
    User286067 Journeyer
    Currently Being Moderated

    sed 's/)$/\)\;/' test.sql

    You might want to use something like this for a better performance than editor, of course I am no regex expert so you will have to test it out.

  • 5. Re: SQL Developer export forgot to check to include semicolons in DDL
    rp0428 Guru
    Currently Being Moderated
    You might be right. I'll look into regular expressions. Regeneration is tough since i left my workplace (I'm a student putting the last finishing touches on my thesis). I was however able to extract the beginning of the huge file using excel, so there is still some hope.

     

    You say you 'left my workplace'? Then does that data and file belong to them?

     

    The DDL for those two tables you posted is only 1k in size. To reach 1 GB you would need to have 2 million tables. That hardly sounds like the size a school project would be so something sounds a  little suspicious to me.

     

    If you have your workplaces authorization to have the file then just contact them and have them regenerate it for you.

  • 6. Re: SQL Developer export forgot to check to include semicolons in DDL
    Jim Smith Expert
    Currently Being Moderated

    Another option is to use something like split to break the file up into manageable chunks.

  • 7. Re: SQL Developer export forgot to check to include semicolons in DDL
    1003664 Newbie
    Currently Being Moderated

    Hi,

     

    just to ease your mind a little, it's a thesis in data warehousing (specifically: evaluating the possibility of switching from Inmons Snowflake Schema to Linstedts Data Vault or Rönnbäcks Anchor Modelling). These come witch e with performance comparisons which in data warehouses means a larger volume of data.

     

    Also i'd like to add that the file begins with table definitions but then contains the actual (and randomly generated) data as well.

  • 8. Re: SQL Developer export forgot to check to include semicolons in DDL
    rp0428 Guru
    Currently Being Moderated

    Also i'd like to add that the file begins with table definitions but then contains the actual (and randomly generated) data as well.

    You still need to have the file regenerated or you should expect to have problems with ANY method you use to try to automatically 'fix' the problem.

     

    1. No experienced developer that I have worked with would EVER try to execute such a monolithic script because there is virtually NO chance that a 2 GB script will EVER run successfully without an error of some sort. Objects have to be created in the proper order when dependencies exist between them. For example, you can't create an index if the table hasn't been created yet. There can even be circular references that will prevent all objects from being VALID even if they are created.

     

    2. There is virtually NO chance that ANY method you use will put terminators in EXACTLY the right place without causing a problem of some sort.

     

    3. When there is an error the script may continue to try to executed and that usually causes further errors. It also creates garbage objects that are not valid but will keep the script from working correctly the next time since the objects will already exist.

     

    You need to create a SET of scripts that are of manageable size so that you can control the process of executing them in the proper order.

     

    If you don't take the time to do that, up front, you are going to wasted a tremendous amount of time when the script fails trying to cut and paste the pieces you need out of that 'master' script.

     

    There are NO SHORTCUTS: take the time to do it right the first time.

  • 9. Re: SQL Developer export forgot to check to include semicolons in DDL
    1003664 Newbie
    Currently Being Moderated

    I see that now. I fought my way through it since it was only the "stupid" source tables that don't contain any indices, keys etc. In the end, I used the split command and cygwin (you can add its /BIN folder to the windows Path variable and enjoy all those magnificent Linux commands in 8) to manually correct my error. A little word of advice for those after me: split takes the byte-parameter seriously, and therefore will split the file mid-line, possibly breaking the last command of a file, and the first of the next. Therefore, the Line-parameter is a better choice.

     

    I would like to thank you all for your great support, not only have you improved my windows command line, you also taught me the important lesson that graphical user interfaces should be analysed with the same care usually reserved for bash-commands and their man-pages.

     

    Thank you all,

    A very happy student

Legend

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