Insert Into A Table With 200 Columns

Question

We Have A Table in our Oracle DB With 200 Columns Which we have to do Insert into it and we have an C program which inserts requests in that table and it uses Pro*C to Call an stored program in oracle DB to do the job.

Currently we serialize all fields with TLV format{Tag-Length-Value} and have a function with a Varchar2 parameter(the serialized list of fields). for example:

008603701212345678901201100611111104800622222220000633333320100644444401201420200321164712

more human readable:

0086 037 012 123456789012 011 006 111111 048 006 222222 200 006 333333 201 006 444444 012 014 20200321164712

which means:

String Len:86
Field037 with len 12:123456789012
Field011 with len 6:111111
Field048 with len 6:222222
Field200 with len 6:333333
Field201 with len 6:444444
Field012 with len 14:20200321164712

each of these fields map to a field in the table, the stored program parses the huge string and fills them into a rowtype and finally inserts the row in the table.

For years it was working well, but when we get ready for more requests(thus more inserts) our DBA says we are using a lot of CPU for de-serializing the TLV. So we have to change our insert method.

I Currently created an structure in C and map all fields in it and call:

typedef struct 
{ 
    char  field37[12+1];
    char  field11[6+1];
    char  field48[6+1];
    char  field200[6+1];
    char  field201[6+1];
    char  field12[14+1];
} NewRecord; 
NewRecord newRecord;
TlvToStruct(sTlv, &newRecord);//Mapper Function
EXEC SQL INSERT INTO MY_TABLE(FIELD37, FIELD11, FIELD200, FIELD201, FIELD12) VALUES(:newRecord);
EXEC SQL COMMIT WORK RELEASE;

This approach works well for now, but my question is: Should I continue developing and add all 200 fields to this structure and use this approach or it's better to use PLSQL call(maybe develop and using another insert function) instead of this SQL Insert?

I am currently aware of PLSQL benefits, but there is some problems with it here:

1- Pro*C does not support PLSQL Records

[Oracle Document]1

2- It is not so reasonable to have a function with 200 parameters!
3- We use Types in plsql but never used them in Pro*C(actually I tried once and I failed long ago)

I tried to describe the problem here, if it is unclear please ask

Thanks

==================================================

EDIT:

Here is our old Get_Tag and Put_Tag functions:

FUNCTION GET_TAG(P_TAG_NAME IN VARCHAR2, P_TLV_STRG IN VARCHAR2, P_TAG_LEN OUT NOCOPY PLS_INTEGER, P_TAG_VALUE OUT NOCOPY VARCHAR2)
    RETURN PLS_INTEGER IS
    V_COUNTER_LOOP                          PLS_INTEGER := 1;

    V_TLV_STRG                              VARCHAR2(4096) := SUBSTR(P_TLV_STRG, 5);
BEGIN
    P_TAG_VALUE   := NULL;

    P_TAG_LEN     := 0;

    WHILE V_COUNTER_LOOP < LENGTH(V_TLV_STRG) LOOP
        IF SUBSTR(V_TLV_STRG, V_COUNTER_LOOP, 3) = P_TAG_NAME THEN
            P_TAG_LEN     := TO_NUMBER(SUBSTR(V_TLV_STRG, V_COUNTER_LOOP + 3, 3));

            P_TAG_VALUE   := SUBSTR(V_TLV_STRG, V_COUNTER_LOOP + 6, P_TAG_LEN);

            RETURN (DECLARATION_CST.OK);
        END IF;

        V_COUNTER_LOOP   := V_COUNTER_LOOP + 6 + TO_NUMBER(SUBSTR(V_TLV_STRG, V_COUNTER_LOOP + 3, 3));
    END LOOP;

    RETURN 0;
EXCEPTION
    WHEN OTHERS THEN
        RETURN -1;
END GET_TAG;

===========================================================

FUNCTION PUT_TAG(P_TAG_NAME IN VARCHAR2, P_TAG_VALUE IN VARCHAR2, P_TLV_STRG IN OUT NOCOPY VARCHAR2)
    RETURN PLS_INTEGER IS
    V_COUNTER_LOOP                          PLS_INTEGER := 0;

    TMP_VARCHAR                             VARCHAR2(4096);
BEGIN
    P_TLV_STRG       := SUBSTR(P_TLV_STRG, 5);

    V_COUNTER_LOOP   := 1;

    WHILE V_COUNTER_LOOP < LENGTH(P_TLV_STRG) LOOP
        IF SUBSTR(P_TLV_STRG, V_COUNTER_LOOP, 3) = SUBSTR(P_TAG_NAME, 1, 3) THEN
            TMP_VARCHAR   :=
                   SUBSTR(P_TLV_STRG, 1, V_COUNTER_LOOP - 1)
                || SUBSTR(P_TAG_NAME, 1, 3)
                || TO_CHAR(NVL(LENGTH(P_TAG_VALUE), 0), 'FM000')
                || P_TAG_VALUE
                || SUBSTR(P_TLV_STRG, V_COUNTER_LOOP + 6 + TO_NUMBER(SUBSTR(P_TLV_STRG, V_COUNTER_LOOP + 3, 3)));

            P_TLV_STRG   := TO_CHAR(LENGTH(TMP_VARCHAR), 'FM0000') || TMP_VARCHAR;

            RETURN (DECLARATION_CST.OK);
        END IF;

        V_COUNTER_LOOP   := V_COUNTER_LOOP + 6 + TO_NUMBER(SUBSTR(P_TLV_STRG, V_COUNTER_LOOP + 3, 3));
    END LOOP;

    P_TLV_STRG       :=
           P_TLV_STRG
        || SUBSTR(P_TAG_NAME, 1, 3)
        || TO_CHAR(NVL(LENGTH(P_TAG_VALUE), 0), 'FM000')
        || P_TAG_VALUE;

    P_TLV_STRG       := TO_CHAR(LENGTH(P_TLV_STRG), 'FM0000') || P_TLV_STRG;

    RETURN 0;
EXCEPTION
    WHEN OTHERS THEN
        RETURN -1;
END PUT_TAG;

Answer 1

You need to create the mapping table first in the database which maps to the field and its length (in my example I have used CTE as your mapping table: YOUR_MAPPING_TABLE).

Oracle setup:

SQL> CREATE TABLE TEST1234 (
  2      FIELD001   VARCHAR2(4000),
  3      FIELD002   VARCHAR2(4000),
  4      FIELD003   VARCHAR2(4000),
  5      FIELD004   VARCHAR2(4000)
  6  );

Table created.

Solution query:

SQL> INSERT INTO TEST1234
  2  WITH YOUR_MAPPING_TABLE (FIELD_ID, LEN)
  3  AS (
  4  SELECT 'FIELD001', 4 FROM DUAL UNION ALL
  5  SELECT 'FIELD002', 3 FROM DUAL UNION ALL
  6  SELECT 'FIELD003', 3 FROM DUAL UNION ALL
  7  SELECT 'FIELD004', 12 FROM DUAL
  8  )
  9  SELECT * FROM
 10      ( SELECT M.FIELD_ID, -- Your string will go in following substring
 11               SUBSTR('0086037012123456789012', M.STARTPOS + 1, M.TOTALLEN) AS FIELDVALUE 
 12          FROM ( SELECT FIELD_ID,
 13                        SUM(LEN) OVER(ORDER BY FIELD_ID ) - LEN AS STARTPOS,
 14                        LEN   AS TOTALLEN
 15                   FROM YOUR_MAPPING_TABLE
 16               ) M
 17      ) PIVOT (
 18          MIN ( FIELDVALUE )
 19          FOR FIELD_ID IN ( 'FIELD001', 'FIELD002', 'FIELD003', 'FIELD004' )
 20      );

1 row created.

Testing the result

SQL> SELECT * FROM TEST1234;

FIELD001   | FIELD002   | FIELD003   | FIELD004
---------- | ---------- | ---------- | -------------
0086       | 037        | 012        | 123456789012

SQL>

You can pass your big string in the query as per your logic.