Populating an Oracle database, using GCC or TOC, does anybody know this?



  • In the past, I've worked with Oracle databases.
    In order to populate those, I was using commands, like:

    Program.Object.Create, Field1="Value1", Field2="Value2", ...
    

    The name of that technology was called "GCC" or "TOC" (depending of the used version).

    I would like to use that technology (or a similar one) for populating other databases (like SQL-Server), because currently we are using SQL for this, something like:

    INSERT INTO Table1 (Field_1, Field_2, ..., Field_n) VALUES (Value_1, Value_2, ..., Value_n)
    

    As you can see, the link between Field_i and Value_i is not that straightforward.

    I have no idea about the name of that technology, I don't even if that technology was something public or written in-house.

    In case this technology is something public and if it is usable for other database technologies (like SQL-server), can anybody give me a hint?

    Thanks in advance

    Edit: What do I mean, not straightforward?

    Imagine a table with ten columns and I would like to modify some values. In SQL, this leads to SQL update queries like:

    UPDATE Table1 SET Name, ID, Cause, SubCause, Feature, SubFeature, Call, SubCall, Type, SubType VALUES (8, 9, 6, 2, 4, 3, 8, 5, 3, 1);
    UPDATE Table1 SET Name, ID, Cause, SubCause, Feature, SubFeature, Call, SubCall, Type, SubType VALUES (2, 6, 8, 1, 3, 8, 5, 3, 1, 5);
    UPDATE Table1 SET Name, ID, Cause, SubCause, Feature, SubFeature, Call, SubCall, Type, SubType VALUES (9, 6, 5, 2, 4, 8, 6, 3, 2, 4);
    

    In order to know which are the subfeatures in those statements, I need to count in which column the subfeatures are present. That seems to be sixth one. Then I need to look for the sixth values and I see the results (3, 8 and 8 again).

    In GCC/TOC technology this is far more straightforward (after scrolling 🙂 😞

    Program.Table1.Modify,Name="8", ID="9", Cause="6", SubCause="2", Feature="4", SubFeature="3", Call="8", SubCall="5", Type="3", SubType="1";
    Program.Table1.Modify,Name="2", ID="6", Cause="8", SubCause="1", Feature="3", SubFeature="8", Call="5", SubCall="3", Type="1", SubType="5";
    Program.Table1.Modify,Name="9", ID="6", Cause="5", SubCause="2", Feature="4", SubFeature="8", Call="6", SubCall="3", Type="2", SubType="4";
    


  • The native T-SQL language also allows inserting / updating via a SELECT statement as opposed to hard-coding values. Then you can utilize aliases for better readability to emulate what you're used to in Oracle. For example:

    INSERT INTO Table1 (Field_1, Field_2, Field_n)
    SELECT Value_1 AS Field_1, Value_2 AS Field_2, Value_n AS Field_n;
    

    Or my personal preference on formatting to increase readability:

    INSERT INTO Table1 (Field_1, Field_2, Field_n)
    SELECT 
        Value_1 AS Field_1, 
        Value_2 AS Field_2, 
        Value_n AS Field_n;
    

    As far as updates, the "not straightforward" example you provided is not valid syntax so it wouldn't work on SQL Server anyway. The correct syntax in T-SQL is:

    UPDATE Table1 
    SET 
        Name = 9, 
        ID = 6, 
        Cause = 5, 
        SubCause = 2, 
        Feature = 4, 
        SubFeature = 8, 
        Call = 6, 
        SubCall = 3, 
        Type = 2, 
        SubType = 4;
    

    This naturally is similar to what you're familiar with in Oracle.

    Also, if you only wanted to update a subset of the records, such as the ones with ID = 6 already, then the following example would be how to accomplish that:

    UPDATE Table1 
    SET 
        Name = 9, 
        Cause = 5, 
        SubCause = 2, 
        Feature = 4, 
        SubFeature = 8, 
        Call = 6, 
        SubCall = 3, 
        Type = 2, 
        SubType = 4
    WHERE ID = 6; -- Updates only the records whose ID already = 6
    



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2