search

zorantica / plsql-qr-code

Oracle PL/SQL Package and APEX plugin for QR Code Generation
MIT License
53 stars 20 forks source link

Errors in p_print_clob on large strings #5

Closed vadimonus closed 3 years ago

vadimonus commented 3 years ago

Awesome code! You saved my day

In p_print_clob you use substr, instr and length functions. They accepts only varchar, so clob is automiticaly casted to varchar (and stripped if it is larger). Try to print svg for large string like (https://github.com/zorantica/plsql-qr-code/commit/09cc1a57253fc983e11aac1f2acaf9e2b4cca491), i've got error on 11.2 xe. generated sql should be larger than 32kb to reproduce.

You should use dbms_lob functions. I use next code and it works perfect for svg. Sorry, have no time to create pull requests and fix this code to stop on chr(10) for compilance with your current logic.

PROCEDURE p_print_clob_htp( p_clob IN OUT NOCOPY clob ) IS

L_POSITION NUMBER;
L_LENGTH NUMBER;
L_BUFFER VARCHAR(8000 CHAR);

BEGIN L_LENGTH := DBMS_LOB.GETLENGTH(p_clob); L_POSITION := 1; LOOP EXIT WHEN L_POSITION > L_LENGTH ; L_BUFFER := DBMS_LOB.SUBSTR(p_clob, 8000, L_POSITION); HTP.PRN(L_BUFFER); L_POSITION := L_POSITION + 8000; END LOOP; END p_print_clob_htp;

zorantica commented 3 years ago

Hi @vadimonus and thank You for noticing this BUG and thank You for proposed solution.

Problem with my code was that I forgot to add chr(10) at the end of each line of SVG image... it left commented in procedure code 😞

image

Now I uncommented it and existing p_print_clob_htp is working... plus generated HTML code in page is much nicer readable. Example below (previously it has thrown an error).

image

I also added Your code into package body (just under different name "p_print_clob_htp_dbms_lob") - It can get helpful in some scenarios.