Inserting Clob via PL SQL in Qlik View

[Steele]

Hello,

I need to insert lengthy scripts into our Oracle database as readable text, using a Clob. The problem is that some of the scripts are 200,000+ characters long. Far too long for the string literal max.

I've tried  using the below script, but I believe it fails because the semicolons terminate the SQL statement in Qlik. I can't use a stored procedure without using 60+ arguments, since they're limited to 4,000 characters at a time.

SET vSqlTooLong = "very long string";

LET vSub1 = Mid(vSqlTooLong,1,30000);
LET vSub2 = Mid(vSqlTooLong,30001,30000);

SQL

declare
    vstr_part1 varchar2(32767) := '$(vSub1)';
    vstr_part2 varchar2(32767) := '$(vSub2)';
    vClobVal clob;
begin
    vClobVal := vstr_part1;
    vClobVal := "vClobVal" || vstr_part2;

// Insert into database using vClobVal variable
end;

[Brett_Bleess · Accepted solution]

So let me try to explain the one thing that may get you pointed in the right direction here, as I suspect the issue is in the Oracle Driver/Connector you are using, not Qlik per se.  We are simply taking the SQL code and passing that directly to the DB vendor connector/driver, so if there is something not working as you like there, I am pretty sure it is going to be an issue in the driver/connector.  I would probably run this by Oracle Support/Forums and see if you can get something there.  If someone says we are doing something wrong on our side, then definitely open a support case and let us know, as we can get that passed on to the engineers, but I am pretty sure if the issue is in the SQL code section, this is going to be the driver/connector not handling things the way you want...

Regards,
Brett

[Steele · Accepted solution]

Brett,

You are right. It was an issue with the driver. We ultimately worked around this by splitting the string into 100 different arguments, each 3,000 charters long, and concatenating them together in a stored procedure. Not elegant, but it worked.

Another issue we ran into was needing to sanitize ; and $, due to how Qlik interacted with them during $(). We just replaced them with [sc] for semicolon and [ds] for dollar sign, then ran a Regex against them in the future to re add them in our app.

Sample code:

CREATE OR REPLACE PROCEDURE SOME_PROCEDURE (
p_SQL_1 VARCHAR,
p_SQL_2 VARCHAR,
p_SQL_3 VARCHAR,
p_SQL_4 VARCHAR,
p_SQL_5 VARCHAR)
IS
vstr_part1 varchar(4000) := p_SQL_1;
vstr_part2 varchar(4000) := p_SQL_2;
vstr_part3 varchar(4000) := p_SQL_3;
vstr_part4 varchar(4000) := p_SQL_4;
vstr_part5 varchar(4000) := p_SQL_5;
vClobVal clob;
BEGIN
vClobVal := vClobVal || vstr_part1;
vClobVal := vClobVal || vstr_part2;
vClobVal := vClobVal || vstr_part3;
vClobVal := vClobVal || vstr_part4;
vClobVal := vClobVal || vstr_part5;

INSERT INTO TABLE ("SQL_STATEMENT")
VALUES(vClobVal);
END;

Thanks,

Stone