search

K2InformaticsGmbH / dderl

Web based Data Explorer / Data Jump Station with Erlang In-Memory Support
Apache License 2.0
23 stars 12 forks source link

Copying text with Tabs from/to Oracle Table #566

Open stoch opened 5 years ago

stoch commented 5 years ago

Copying this table from one DB to another becomes quite difficult. I tried in two ways:

A) DDerl Copy via Clipboard: (fails)

image

image

If I remember correctly, we are not copying the linefeeds in escaped form \u000A for compatibility with Excel. So this would need another Ctrl-C than normal (maybe Ctrl-UC). The paste could then interpret the escaped linefeeds and tabulators while pasting with normal Ctrl-V.

B) Menu Point 'Insert Sql' on Source DB

INSERT INTO
    REVA_HEADER (
        REVAH_ID, REVAH_SRCTYPE, REVAH_PACID, REVAH_BILLINGTYPE,
        REVAH_BILLEDITEM, REVAH_SQL, REVAH_DESC, REVAH_SIGDESC, REVAH_ESID,
        REVAH_INSQL, REVAH_NEWSIGSQL)
VALUES
    ('MMSC-CH', 'MMSC', 'MOMMSCH', 'CU', 'MMS',
    'SELECT /*+ INDEX(BDETAIL6 IDX_BD_BIHID6) */ 
    TRUNC(BD_DATETIME),
    REVASM_ID,
    SUM(DECODE(BD_RECIPINDEX,1,1,0)),
    SUM(DECODE(BD_BILLED || UPPER(BD_PACIDHB),''1'' || ''<REVAH_PACID>'',1,0))
FROM    REVA_SIGMASK, BDETAIL6
WHERE   BD_BIHID = :1
AND BD_DATETIME > sysdate - <MINDATE>
AND DECODE(UPPER(BD_PACIDHB),''<REVAH_PACID>'',''1'',''0'') || BD_SIGNATURE LIKE REVASM_CODE
AND REVASM_REVAHID = ''<REVAH_ID>''
AND REVASM_ESID=''A''
GROUP BY
    TRUNC(BD_DATETIME),
    UPPER(BD_PACIDHB),
    REVASM_ID', 'MMS CDRs going to B4U/esBill/LIDR over AMS CH', '00: OUTPUT 1=matching, 0=not matching
01: ZERO  0=zero charged 1=nonzero charged
02: BILLED 1=billed, 0=not billed, 2=uninitialized, 3=duplicate submit 
03: MAPSID R=Ready, E=Error, M=Mapping
04: CDRTID O=MMSOrecord, R=MMSRrecord, 3=MM3Rrecord, 4=MM4Rrecord, 7=MM7?record, U=unexpected
05: MSISDN T=TFL(42377..), S=others
06: PREPAID Y=yes, N=no, U=Unknown
07: TARID V=free-free, X=internal, S=BN, P=PTS, L=MMS-LA or ''-''=P2P
08: EVENTDISPOSITION 1=delivered, 2=received, 3=expired, 4=rejected network, 7=rejected terminal, 0=others
09: ROAMINGINFO D=41.794998800 (default), S=NULL (home net), F=foreign net 
10: MESSAGETYPE 0=Message, 1=Notification, 2=DeliveryReport, 3=ReadReply
11: RECIPINDEX 1=first, n=added by splitter
12: IMSI D=228010000000, N=NULL, S=22801..., F=others
13: MOPROM   0=no, 1=yes
14: MTPROM   0=no, 1=yes
15: RESERVED ''-''=unused
16: RESERVED ''-''=unused
17: RESERVED ''-''=unused', 'A', 'SELECT  /*+ INDEX(BD1 IDX_BD_BIHID6) */ COUNT(*)
FROM BDETAIL6 BD1
WHERE   BD1.BD_BIHID = :1
AND BD1.BD_DATETIME > sysdate - <MINDATE>
AND BD1.BD_RECIPINDEX = 1', 'SELECT     /*+ INDEX(BDETAIL6 IDX_BD_BIHID6) */ 
    TRUNC(BD_DATETIME),
    DECODE(UPPER(BD_PACIDHB),''<REVAH_PACID>'',''1'',''0'') || BD_SIGNATURE,
    SUM(DECODE(BD_RECIPINDEX,1,1,0)),
    SUM(DECODE(BD_BILLED || UPPER(BD_PACIDHB),''1'' || ''<REVAH_PACID>'',1,0))
FROM    BDETAIL6
WHERE   BD_BIHID = :1
AND BD_DATETIME > sysdate - <MINDATE>
AND not exists (
        SELECT REVASM_CODE FROM REVA_SIGMASK
        WHERE  DECODE(UPPER(BD_PACIDHB),''<REVAH_PACID>'',''1'',''0'') || BD_SIGNATURE LIKE REVASM_CODE
    AND    REVASM_REVAHID = ''<REVAH_ID>''
        AND    REVASM_ESID=''A''
    )
GROUP BY
    TRUNC(BD_DATETIME),
    DECODE(UPPER(BD_PACIDHB),''<REVAH_PACID>'',''1'',''0'') || BD_SIGNATURE');
INSERT INTO
    REVA_HEADER (
        REVAH_ID, REVAH_SRCTYPE, REVAH_PACID, REVAH_BILLINGTYPE,
        REVAH_BILLEDITEM, REVAH_SQL, REVAH_DESC, REVAH_SIGDESC, REVAH_ESID,
        REVAH_INSQL, REVAH_NEWSIGSQL)
VALUES
    ('SMSN-4FL', 'SMSN', 'MOSMS4FL', 'CU', 'SMS',
    'SELECT /*+ INDEX(BDETAIL1 IDX_BD_BIHID1) */ 
    TRUNC(BD_DATETIME),
    REVASM_ID,
    COUNT(*),
    SUM(DECODE(BD_BILLED || UPPER(BD_PACIDHB),''1'' || ''<REVAH_PACID>'',1,0))
FROM    REVA_SIGMASK, BDETAIL1
WHERE   BD_BIHID = :1
AND BD_DATETIME > sysdate - <MINDATE>
AND DECODE(UPPER(BD_PACIDHB),''<REVAH_PACID>'',''1'',''0'') || BD_SIGNATURE LIKE REVASM_CODE
AND REVASM_REVAHID = ''<REVAH_ID>''
AND REVASM_ESID=''A''
GROUP BY
    TRUNC(BD_DATETIME),
    UPPER(BD_PACIDHB),
    REVASM_ID
UNION ALL
SELECT /*+ INDEX(BDETAIL2 IDX_BD_BIHID2) */ 
    TRUNC(BD_DATETIME),
    REVASM_ID,
    COUNT(*),
    SUM(DECODE(BD_BILLED || UPPER(BD_PACIDHB),''1'' || ''<REVAH_PACID>'',1,0))
FROM    REVA_SIGMASK, BDETAIL2
WHERE   BD_BIHID = :2
AND BD_DATETIME > sysdate - <MINDATE>
AND DECODE(UPPER(BD_PACIDHB),''<REVAH_PACID>'',''1'',''0'') || BD_SIGNATURE LIKE REVASM_CODE
AND REVASM_REVAHID = ''<REVAH_ID>''
AND REVASM_ESID=''A''
GROUP BY
    TRUNC(BD_DATETIME),
    UPPER(BD_PACIDHB),
    REVASM_ID', 'IMS & RCS Roaming SMS CDRs going to B4U/esBill/LIDR over AMS FL', '00: OUTPUT   1=matching, 0=not matching
01: ZERO     0=zero charged, 1=nonzero charged or not charged
02: BILLED   1=billed, 0=not billed, 2=uninitialized, 3=ignore-dup, 4=ignore-0, 5=reject-dup, 6=reject-sub, 7=send-frng, 8=recv-frng , 9=ogti-frng (see table BDBSTATE) 
03: MAPSID   E=Error, M=Mapping
04: ORIGIN   S=SS7, I=IMS, E=SMSBroker, R=RCS, A=AO, G=Generated, H=HomeRouted, M=M2M(HRON)
05: ORIGNET  S=System(Swisscom+TFL), C=CH, F=Foreign, U=Unknown
06: DEST     S=SS7, I=IMS, E=Extern, R=RCS, A=AT, U=Unknown
07: DESTNET  S=System(Swisscom+TFL), C=CH, F=Foreign, U=Unknown
08: MSISDN   T=TFL(42377..), S=others(Swisscom)
09: PREPAID  Y=yes, N=no, U=unknown
10: TARID    L=LA, S=BN-IS, P=Portal-IS, I=Tariff i, T=Televote, V=free-free, X=intern or ''-'' =P2P
11: STATUS   0=delivered, 1=expired, 2=deleted, 3=replaced, 4=submitted
12: BIOREQT  0=standalone (chargeable), 1=standalone+link (not chargeable), 2=break_out_delivery, U=unknown
13: RATZONE  0=normal, 1=la-handygroup(SMS-HG-a/SMS-HG-b/SMS-HG-d)
14: MOPROM   0=no, 1=yes
15: RESERVED ''-''=unused
16: RESERVED ''-''=unused
17: RESERVED ''-''=unused
 ', 'A', 'SELECT  /*+ INDEX(BD1 IDX_BD_BIHID1) */ COUNT(*)
FROM BDETAIL1 BD1
WHERE   BD1.BD_BIHID = :1
AND BD1.BD_DATETIME > sysdate - <MINDATE>

UNION ALL

SELECT  /*+ INDEX(BD2 IDX_BD_BIHID2) */ COUNT(*)
FROM BDETAIL2 BD2
WHERE   BD2.BD_BIHID = :2
AND BD2.BD_DATETIME > sysdate - <MINDATE>', 'SELECT     /*+ INDEX(BDETAIL1 IDX_BD_BIHID1) */ 
    TRUNC(BD_DATETIME),
    DECODE(UPPER(BD_PACIDHB),''<REVAH_PACID>'',''1'',''0'') || BD_SIGNATURE,
    COUNT(*),
    SUM(DECODE(BD_BILLED || UPPER(BD_PACIDHB),''1'' || ''<REVAH_PACID>'',1,0))
FROM    BDETAIL1
WHERE   BD_BIHID = :1
AND BD_DATETIME > sysdate - <MINDATE>
AND not exists (
    SELECT revasm_code from reva_sigmask
        WHERE  DECODE(UPPER(BD_PACIDHB),''<REVAH_PACID>'',''1'',''0'') || BD_SIGNATURE LIKE REVASM_CODE
        AND    REVASM_REVAHID = ''<REVAH_ID>''
        AND    REVASM_ESID=''A''
    )
GROUP BY
    TRUNC(BD_DATETIME),
    DECODE(UPPER(BD_PACIDHB),''<REVAH_PACID>'',''1'',''0'') || BD_SIGNATURE
UNION ALL
SELECT  /*+ INDEX(BDETAIL2 IDX_BD_BIHID2) */ 
    TRUNC(BD_DATETIME),
    DECODE(UPPER(BD_PACIDHB),''<REVAH_PACID>'',''1'',''0'') || BD_SIGNATURE,
    COUNT(*),
    SUM(DECODE(BD_BILLED || UPPER(BD_PACIDHB),''1'' || ''<REVAH_PACID>'',1,0))
FROM    BDETAIL2
WHERE   BD_BIHID = :2
AND BD_DATETIME > sysdate - <MINDATE>
AND not exists (
    SELECT REVASM_CODE FROM REVA_SIGMASK
        WHERE  DECODE(UPPER(BD_PACIDHB),''<REVAH_PACID>'',''1'',''0'') || BD_SIGNATURE LIKE REVASM_CODE
        AND    REVASM_REVAHID = ''<REVAH_ID>''
        AND    REVASM_ESID=''A''
    )
GROUP BY
    TRUNC(BD_DATETIME),
    DECODE(UPPER(BD_PACIDHB),''<REVAH_PACID>'',''1'',''0'') || BD_SIGNATURE');

Executing the script on the target DB fails as a whole.

Had to insert every line in a separate statement.

Looks as if Multi-Select statements work in DDerl but not Multi-Insert statements.

stoch commented 5 years ago

@acautin : It seems that \t (Hex 09) is not properly masked (like \n (Hex 0A) inside double quotes when pasting cells into a grid.

What works for \n must also be handled similarly for pasting \t characters inside double quotes.

This becomes relevant now for code comparison and therfore should be fixed a.s.a.p.