During storing of sql Skripts as CompQuery,Procedures or View-Records, it throughs away any additional whitespace and comments. On top of that it also encode most SQL-commands into a byte code to be interpreted by thy according TSK_IQ/TSK_SQL_SERVER processes.
Is there a possibility to decompress these records back readable?
Here is an sqlplus-Skript, which decodes CompQuery,Procedures and View-Records. The Output of the skript is accurate, but needs some formatting (returns, whitespaces) and potentialls comments to get it in a good human readable form.
-- Skript Decode_CompQueryDefs
-- initial version 11.12.2002
-- changes 09.08.2017 added Comments on english
-- 31.07.2018 code 9b changed to DESC, tested with ASC and DESC.
MACRO m_record = PROMPT('Which CompQuery,Stored Procedure or View?');;
-- codelist for the interpreted short cuts
DECLARE LOCAL TEMPORARY TABLE module.codelist
(m_code INT,m_decode CHAR(40),m_return INT,m_mode INT);
LOCAL ix INT,l_LINE CHAR(512),l_bx CHAR(2),l_cx INT,
l_result CHAR(255),iy INT,iz INT,l_stringmode INT;
SET LOG_ROWS = 0;
-- fill the code list
INSERT INTO module.codelist
(m_code,m_decode,m_return)
VALUES
(CAST('c2' AS INT USING 'Z5'),'MACRO',3),
(CAST('c1' AS INT USING 'Z5'),' LOCAL',25),
(CAST('af' AS INT USING 'Z5'),'FUNCTION',3),
(CAST('b3' AS INT USING 'Z5'),'IF',3),
(CAST('d0' AS INT USING 'Z5'),'RETURN',3),
(CAST('ea' AS INT USING 'Z5'),'WHILE',3),
(CAST('9d' AS INT USING 'Z5'),' DO',4),
(CAST('db' AS INT USING 'Z5'),' THEN',4),
(CAST('95' AS INT USING 'Z5'),'CURRENT_TIMESTAMP',1),
(CAST('c7' AS INT USING 'Z5'),' OF',1),
(CAST('ac' AS INT USING 'Z5'),' FOR',1),
(CAST('ec' AS INT USING 'Z5'),' WITH',9),
(CAST('9e' AS INT USING 'Z5'),'ELSE',6),
(CAST('c6' AS INT USING 'Z5'),' OCCNUM',1),
(CAST('a9' AS INT USING 'Z5'),'EXISTS',1),
(CAST('d4' AS INT USING 'Z5'),'SELECT',9),
(CAST('ae' AS INT USING 'Z5'),' FROM',9),
(CAST('e9' AS INT USING 'Z5'),' WHERE',9),
(CAST('86' AS INT USING 'Z5'),' AND',9),
(CAST('9f' AS INT USING 'Z5'),'END',2),
(CAST('d5' AS INT USING 'Z5'),' SET',9),
(CAST('e4' AS INT USING 'Z5'),'UPDATE',3),
(CAST('de' AS INT USING 'Z5'),' TO',1),
(CAST('88' AS INT USING 'Z5'),' AS',1),
(CAST('89' AS INT USING 'Z5'),'BEGIN',6),
(CAST('c9' AS INT USING 'Z5'),' OR',9),
(CAST('c4' AS INT USING 'Z5'),' NOT',9),
(CAST('e6' AS INT USING 'Z5'),' USING',1),
(CAST('b0' AS INT USING 'Z5'),' GROUP',9),
(CAST('8d' AS INT USING 'Z5'),' BY',1),
(CAST('ca' AS INT USING 'Z5'),' ORDER',9),
(CAST('8a' AS INT USING 'Z5'),' BETWEEN',1),
(CAST('b4' AS INT USING 'Z5'),' IN',1),
(CAST('ba' AS INT USING 'Z5'),' IS',1),
(CAST('c5' AS INT USING 'Z5'),' NULL',1),
(CAST('be' AS INT USING 'Z5'),' LIKE',1),
(CAST('dd' AS INT USING 'Z5'),' TIMESTAMP',1),
(CAST('ad' AS INT USING 'Z5'),' FORMAT',1),
(CAST('a1' AS INT USING 'Z5'),' ERROR_CODE',9),
(CAST('a0' AS INT USING 'Z5'),'ERROR',3),
(CAST('a5' AS INT USING 'Z5'),' ERROR_TEXT',9),
(CAST('a6' AS INT USING 'Z5'),' ERROR_TYPE',9),
(CAST('a4' AS INT USING 'Z5'),' ERROR_LINE',9),
(CAST('a2' AS INT USING 'Z5'),' ERROR_COLUMN',9),
(CAST('a3' AS INT USING 'Z5'),' ERROR_FILE',9),
(CAST('ed' AS INT USING 'Z5'),'WRITE',3),
(CAST('92' AS INT USING 'Z5'),'COMMIT',3),
(CAST('9b' AS INT USING 'Z5'),' DESC',1),
(CAST('bc' AS INT USING 'Z5'),' LEADING',1),
(CAST('b6' AS INT USING 'Z5'),'INSERT',3),
(CAST('b9' AS INT USING 'Z5'),' INTO',1),
(CAST('8f' AS INT USING 'Z5'),'CASE',3),
(CAST('e8' AS INT USING 'Z5'),'WHEN',3),
(CAST('80' AS INT USING 'Z5'),' ACTIVATION_FIELD',1),
(CAST('82' AS INT USING 'Z5'),' ACTIVATION_RECORD',1),
(CAST('81' AS INT USING 'Z5'),' ACTIVATION_PRIORITY',1),
(CAST('e7' AS INT USING 'Z5'),' VALUES',25),
(CAST('97' AS INT USING 'Z5'),'DECLARE',3),
(CAST('da' AS INT USING 'Z5'),' TEMPORARY',1),
(CAST('d9' AS INT USING 'Z5'),' TABLE',1),
(CAST('c3' AS INT USING 'Z5'),' MODULE',1),
(CAST('d7' AS INT USING 'Z5'),'START',3),
(CAST('9a' AS INT USING 'Z5'),'DELETE',3),
(CAST('98' AS INT USING 'Z5'),' DEFAULT',1),
(CAST('b2' AS INT USING 'Z5'),' HISTSEQNUM',1),
(CAST('bf' AS INT USING 'Z5'),' LINE',1),
(CAST('c0' AS INT USING 'Z5'),' LINENUM',1),
(CAST('d8' AS INT USING 'Z5'),' SYSTEM',1),
(CAST('df' AS INT USING 'Z5'),' TRAILING',1),
(CAST('85' AS INT USING 'Z5'),' ALL_RECORDS',1),
(CAST('ce' AS INT USING 'Z5'),' RECID',1),
(CAST('eb' AS INT USING 'Z5'),' WIDTH',1),
(CAST('87' AS INT USING 'Z5'),' ANY',1),
(CAST('8b' AS INT USING 'Z5'),' BIT',1),
(CAST('8c' AS INT USING 'Z5'),' BREAK',1),
(CAST('90' AS INT USING 'Z5'),' CENTER',1),
(CAST('93' AS INT USING 'Z5'),' CORRESPONDING',1),
(CAST('94' AS INT USING 'Z5'),' CREATE',1),
(CAST('96' AS INT USING 'Z5'),' DATE_FORMAT',1),
(CAST('9c' AS INT USING 'Z5'),' DIAGNOSTICS',1),
(CAST('a8' AS INT USING 'Z5'),' EXECUTE',1),
(CAST('aa' AS INT USING 'Z5'),' FALSE',1),
(CAST('b8' AS INT USING 'Z5'),' INTERVALL',1),
(CAST('bb' AS INT USING 'Z5'),' JOIN',1),
(CAST('bd' AS INT USING 'Z5'),' LEVEL',1),
(CAST('c8' AS INT USING 'Z5'),' ON',1),
(CAST('cb' AS INT USING 'Z5'),' PAGE',1),
(CAST('cc' AS INT USING 'Z5'),' RAND',1),
(CAST('d1' AS INT USING 'Z5'),' RIGHT',1),
(CAST('d2' AS INT USING 'Z5'),' ROLLBACK',1),
(CAST('d3' AS INT USING 'Z5'),' ROWID',1),
(CAST('e0' AS INT USING 'Z5'),' TRUE',1),
(CAST('d6' AS INT USING 'Z5'),' SKIP',1),
(CAST('e2' AS INT USING 'Z5'),' UNION',1),
(CAST('e3' AS INT USING 'Z5'),' UNKNOWN',1),
(CAST('99' AS INT USING 'Z5'),' DEFINITION',1),
(CAST('b1' AS INT USING 'Z5'),' HAVING',9),
(CAST('ef' AS INT USING 'Z5'),'WAIT',3),
(CAST('83' AS INT USING 'Z5'),'ACTTSK',3),
(CAST('8e' AS INT USING 'Z5'),' CALCULATE',1),
(CAST('09' AS INT USING 'Z5'),' ',0),
(CAST('a7' AS INT USING 'Z5'),'EXCEPTION',6),
(CAST('b7' AS INT USING 'Z5'),' INT',1),
(CAST('cf' AS INT USING 'Z5'),' RECORD',1),
(CAST('cd' AS INT USING 'Z5'),' REAL',1),
(CAST('ab' AS INT USING 'Z5'),' FIELD',1),
(CAST('91' AS INT USING 'Z5'),' CHAR',1)
;
-- doing some preparations with codelist.
UPDATE module.codelist SET m_mode = 1;
FOR ix = 32 TO 126 DO
INSERT INTO module.codelist
(m_code,m_decode,m_return,m_mode)
VALUES
(ix,CHR(ix),0,0);
END;
UPDATE module.codelist
SET m_mode = 8
WHERE m_code = 39;
UPDATE module.codelist
SET m_return = BIT_OR(m_return,16)
WHERE BIT_AND(m_return,5) <> 0;
UPDATE module.codelist
SET m_return = BIT_OR(m_return,8)
WHERE BIT_AND(m_return,2) <> 0;
iy = 0;
l_stringmode = 0;
WRITE '-- Record: &m_record';
FOR (SELECT NAME,ONUM,QUERY_LINE FROM
( SELECT NAME,OCCNUM AS ONUM,QUERY_LINE FROM CompQueryDef WHERE NAME = '&m_record'
UNION
SELECT NAME,OCCNUM AS ONUM,QUERY_LINE FROM ProcedureDef WHERE NAME = '&m_record'
UNION
SELECT NAME,OCCNUM AS ONUM,QUERY_LINE FROM ViewDef WHERE NAME = '&m_record'
)
ORDER BY ONUM
) DO
l_LINE = QUERY_LINE;
ix = 1;l_cx = 1;
l_bx = SUBSTRING(l_line FROM 1 FOR 2);
WHILE l_bx <> '' DO
ix = ix + 2;
l_cx = CAST(l_bx AS INT USING 'Z5');
IF l_cx = 39 THEN
l_stringmode = BIT_XOR(l_stringmode,1);
END;--IF
CASE l_stringmode
WHEN 1 THEN
l_result = l_result || CHR(l_cx);
WHEN 0 THEN
iz = iy;
iy = COALESCE( (SELECT m_return FROM module.codelist
WHERE m_code = l_cx),0);
CASE
WHEN l_cx = 0 AND l_result <> '' THEN
WRITE l_result;
l_result = '';
iy = 0;
WHEN l_cx = 0 AND l_result = '' THEN
iy = 0;
WHEN BIT_AND(iy,2)=2 AND l_result <> '' THEN -- Return before
WRITE l_result;
l_result = COALESCE( (SELECT m_decode FROM module.codelist
WHERE m_code = l_cx),'<<'||l_bx||'>>');
WHEN (CHAR_LENGTH(l_result) > 80)
AND BIT_AND(iy,8)=8 THEN-- Break is allowed before
WRITE l_result;
l_result = COALESCE( (SELECT m_decode FROM module.codelist
WHERE m_code = l_cx),'<<'||l_bx||'>>');
WHEN BIT_AND(iz,1)= 1 THEN -- blank towards predecessor
l_result = l_result || ' ' ||
COALESCE( (SELECT m_decode FROM module.codelist
WHERE m_code = l_cx),'<<'||l_bx||'>>');
ELSE
l_result = l_result ||
COALESCE( (SELECT m_decode FROM module.codelist
WHERE m_code = l_cx),'<<'||l_bx||'>>');
END;--IF
CASE
WHEN BIT_AND(iy,4)=4 THEN
WRITE l_result;
l_result = '';
iy = BIT_XOR(iy,BIT_AND(iy,1));--supress blank
WHEN BIT_AND(iy,16) =16
AND CHAR_LENGTH(l_result) >80 THEN
WRITE l_result;
l_result = '';
iy = BIT_XOR(iy,BIT_AND(iy,1));--supress blank
END;--CASE
IF l_cx = 32 THEN
iy = BIT_OR(iy,1);
END;--IF
END;--if l_stringmode = 0
l_bx = SUBSTRING(l_line FROM ix FOR 2);
END;--WHILE
END;--FOR
EDITED 31-JUL-2018: changed code 9b to DESC and tested ok.