Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Steele
Contributor
Contributor

Inserting Clob via PL SQL in Qlik View

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;

Labels (3)
2 Solutions

Accepted Solutions
Brett_Bleess
Support (Former)
Support (Former)

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.

View solution in original post

Steele
Contributor
Contributor
Author

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

View solution in original post

2 Replies
Brett_Bleess
Support (Former)
Support (Former)

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.

View solution in original post

Steele
Contributor
Contributor
Author

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

View solution in original post