0 Replies Latest reply on Apr 23, 2014 2:22 PM by Paavo

    Howto create table with import from xls with apex sqlworkshop data upload style

    Paavo

      I like the Apex sqlworkshop when creating new tables in its sqlworkshop data upload wizards because it does several things fast:

      1. column names and types
      2. upload data
      3. id added automatically
      4. sequence for the id, no checking of the nulls just force the nextval from sequence to id
      5. triggers for the table + enabling
      6. index for the pk
      7. + shows report about failed rows in import

       

      But the SqlDeveloper has very neat feature of being able to import data from the xls worksheets and creating the table with the data.

      Also SqlDeveloper is very handy with its Carts and it would be a bit faster to refine further the tables in SqlDeveloper (+ Datamodeler) once the table has been 'created'.

       

      So to speed-up things I have wish to be able to fully exploit both Apex and SqlDevelopor for creating tables initially.

      How to make 'clear favorite setup' for the SqlDeveloper which allows to create the tables like via apex sqlworkshop data upload?

       

      So any ideas how to have the following tasks in SqlDeveloper could be done as smoothly as in apex sqlworkshop data upload? Currently there are several things to be set in SqlDeveloper:

      1. column names and types  --->
        • add ID to the import Excel worksheet columns, but leave the data cells empty for the 'example data',
        • types perhaps not so important in this step because they can be changed via Edit Table
      2. upload data -->
        • ignore format errors, if you can remember what was the Example data type
      3. id added automatically
        • that was added manually directly to XLSX, just be sure that it gets 'first' in the column list
        • Edit Table --> Constraints --> New Primary Key Constraint  + ID
          • oops, can't do it -- null values found <-- so it is easier to to empty the table
          • ouch the constraints looks ugly, maybe I should not have ticked the 'not null' for id
            • TAPAS_PK Primary_Key

            • SYS_C0030883 Check "ID" IS NOT NULL

      4. sequence for the id, no checking of the nulls just force the nextval from sequence to id
        • there is no sequence yet, so create sequence -->
          • Name: SEQUENCE  -->
          • ouch, I want the name to be like APEX naming convention --> TAPAS_SEQ
          • start with: 1
          • min value: 1
          • max value:    ???? 
          • ...btw, cache size defaults to 20
        • refresh the views in SqlDeveloper
      5. triggers for the table + enabling
        • Trigger --> Create (PK from Sequence)
          • Trigger Name: --> ouch, I want the same naming convention like in APEX -->
          • Sequence Name: TAPAS
          • _SEQ
          • Column Name: ID
        • refresh
        • the views in SqlDeveloper  (note! the frefresh is applied only to gui-region where the button is and on the hierarchy branch currently active - so the newly create trigger is not visible necessarily in all views)
      6. index for the pk
        • this was automagically created with name TAPAS_PK in step 3.) when said using 'DEFAULT' as index
      7. + shows report about failed rows in import
        • hmm gotta try inserting
        • oooppss -- ablet to insert values to ID column
        • look trigger --> ouch has "if :NEW."ID" is null then"
        • fix the trigger manually
        • clean the test data

       

      Hmm.. also if someone knows good trick to do this via staging table which is then used to generate the 'target table ddl' then that would also be good thing.

      And then continue cleaning the names etc.. -- with DataModeler.

       

      rgrds Paavo

      ps. of course once the naming conventions start to settle down and DataModeler starts to govern the design, it is tempting to hope that one day the naming conventions could be taught to both apex and sqldeveloper e.g. based on 'model*