SELECT
DECODE(ROW_NUM,ROW_RANK,SAL,NULL) AS AVOID_DUPLICATE
(
)
This blog is home to share my experiences, views, learning and findings on open source business intelligence tools like Jasper report. This block cover information of Jasper iReport as well jasper server detail.
ORA-01489: Concatenation || not allow to concat long string. Solution TO_CLOB
Error detail: Result of a string concatenation operation exceeded the maximum length allowed
CREATE OR REPLACE PROCEDURE AUTO_REPORT ASV_FILE_NAME VARCHAR2(4000);BEGINFOR I in (SELECT 'XYZ' || COLUMN_NAME || 'XYZ' as Query_data FROM ABLE1)LOOPWRITE_FILE (TO_CHAR(I.Query_data),'V_FILE_NAME');END LOO;END AUTO_REPORT;/
CREATE OR REPLACE PROCEDURE AUTO_REPORT ASV_FILE_NAME VARCHAR2(4000);BEGINFOR I in (SELECT TO_CLOB('XYZ') || COLUMN_NAME || TO_CLOB('XYZ') as Query_data FROM TABLE1)LOOPWRITE_FILE (TO_CHAR(I.Query_data),'V_FILE_NAME');END LOO;END AUTO_REPORT;
FULL CODE
============
create or replace PROCEDURE AUTO_REPORT AS
V_FILE_NAME VARCHAR2(4000);
BEGIN
V_FILE_NAME := 'TEST_ONLY';
FOR I IN (SELECT
TO_CLOB('XYZ SSS') ||
TO_CLOB(EMP_NAME) ||
TO_CLOB('XYZ') AS QUERY_DATA FROM EMPLOYEE)
LOOP
WRITE_FILE(TO_CHAR(I.QUERY_DATA), V_FILE_NAME);
END LOOP;
END AUTO_REPORT;
/
create or replace PROCEDURE WRITE_FILE (P_CONTENT IN LONG, P_FILE_NAME IN VARCHAR) AS
----L_FILE UTL_FILE.FILE_TYPE;
STRING_DATA CLOB;
SPLIT_STRING CLOB;
COUNTER_VALUE NUMBER;
LAST_OCCURANCE NUMBER;
BEGIN
DBMS_OUTPUT.ENABLE (1000000);
---L_FILE: UTL_FILE.FOPEN('FSCM AUTOMIZATION_LOCAL_DIR',P_FILE_NAME, 'A');
STRING_DATA:= REPLACE (P_CONTENT,'``','`') ||'`';
COUNTER_VALUE:=1;
WHILE (1=1)
LOOP
IF COUNTER_VALUE=1 THEN
LAST_OCCURANCE:= INSTR(STRING_DATA,'`',1,1);
SPLIT_STRING:= SUBSTR(STRING_DATA, 1, INSTR (STRING_DATA, '`',1,1)-1);
ELSE
SPLIT_STRING:= SUBSTR(STRING_DATA, LAST_OCCURANCE+1, INSTR(STRING_DATA,'`' ,(LAST_OCCURANCE+1),1));
SPLIT_STRING:= SUBSTR (SPLIT_STRING,1,INSTR(SPLIT_STRING,'`',1)-1);
LAST_OCCURANCE:=INSTR(STRING_DATA,'`', (LAST_OCCURANCE+1),1);
END IF;
--Means considering the string up to the end or in case of error set counter to 5000 to avoid infinite loop.
--Please also note that P_CONTENT line should not more then 5000 line one line means one mark in content.
IF SPLIT_STRING IS NULL OR COUNTER_VALUE = 5000 THEN EXIT;
END IF;
DBMS_OUTPUT.PUT_LINE(SPLIT_STRING);
--DBMS_OUTPUT.PUT_LINE(P_CONTENT);
----UTL_FILE.PUT_LINE(L_FILE, SPLIT_STRING);
SPLIT_STRING:='';
COUNTER_VALUE:=COUNTER_VALUE+1;
END LOOP;
----UTL_FILE.FFLUSH(L_FILE);
--UTL_FILE.FCLOSE (L_FILE);
--EXCEPTION WHEN OTHERS THEN
--UTL_FILE.FFLUSH(L_FILE);
--UTL_FILE.FCLOSE (L_FILE);
--RAISE;
END WRITE_FILE;
/
you can continue using the SQL approach to include the zero-width space before the loanId values:
SELECT UNISTR('\200B') || loanId AS loanId FROM loan;
This SQL query will add an invisible zero-width space before each loanId value, preventing Excel from automatically converting the values to scientific notation when exported.
Issue: some cell having numeric value like field name is TRANSACTION_ID and suppose value is like 1545789348775194 then it auto convert to 4548766+15 something like scientific notation once you click on cell and focus out so below jasper export to excel property help to set the cell property type = text.
The issue is now fixed and will be part of the next JasperReports release.
To have cells set as Text type in Excel, you need to set the following property for that element:
<property name="net.sf.jasperreports.export.xls.pattern" value="@"/>
Also, if the net.sf.jasperreports.export.xls.detect.cell.type property is set to false, the cell type will be set as Text.
Just do copy past it will work. Even if not work just keep gap top of the summary band and first element to make sure it consider break.
set below jasper property in top of the report.
<property name="net.sf.jasperreports.export.xls.sheet.names.all" value="First
sheet/Second sheet/Third sheet" />
Add break to see that part in separate worksheet.
<summary>
<band height="110">
<printWhenExpression><![CDATA[$V{MAIN_REPORT_TABLE1_COUNT}>=1]]></printWhenExpression>
<textField>
<reportElement X="0" y="0" width="1000" height="40" >
<property name="net.sf.jasperreports.export.xls.break.before.row" value="true"/>
</reportElement>
<textElement> <font size="24" isBold="true"/></textElement>
<textFieldExpression><![CDATA["Search Criteria"]]></textFieldExpression>
</textField>
</band>
</summary>