Remove duplicate row with rank() analytical function.

 

SELECT

    DECODE(ROW_NUM,ROW_RANK,SAL,NULL) AS AVOID_DUPLICATE

(

)

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

if you use the below logic like 

CREATE OR REPLACE PROCEDURE AUTO_REPORT AS 

V_FILE_NAME VARCHAR2(4000);

BEGIN

  FOR I in (SELECT 'XYZ' || COLUMN_NAME || 'XYZ'  as Query_data FROM  ABLE1) 

    LOOP

        WRITE_FILE (TO_CHAR(I.Query_data),'V_FILE_NAME');

    END LOO;

END AUTO_REPORT;

/

Above block will gives error if XYZ text length is bigger then 4000 character. so need to change it with TO_CLOB to resolve this error.

CREATE OR REPLACE PROCEDURE AUTO_REPORT AS 

V_FILE_NAME VARCHAR2(4000);

BEGIN

  FOR I in (SELECT  TO_CLOB('XYZ') || COLUMN_NAME || TO_CLOB('XYZ')    as Query_data FROM  TABLE1) 

    LOOP

        WRITE_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;

/

 

  

Export to excel converter text to scientific number solution

 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.

Jasper report - Export to excel force cell to convert as text.

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.

how to create a multi/separate worksheet excel - Search Criteria in separate sheet

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>
    

How to use IN/NOT IN Using Parameter

Example

SELECT * FROM EMPLOYEE WHERE EMP_NAME IN($P{P_STUDENT_NAME})
'SANDIP','VIJAY' <-- Not Work As Considering As A Single Value.

SELECT * FROM EMPLOYEE WHERE EMP_NAME IN ($P!{P_STUDENT_NAME})
'SANDIP','VIJAY' <-- Work As Per Expected

SELECT * FROM ORDERS WHERE $X{IN,EMP_NAME,P_STUDENT_NAME} 
Work As Per Expected But Need To Pass Array

$P!{P_STUDENT_NAME} : This will not be treated as a SQL parameter. 
JasperReports will consider this parameter value will be a part to sql statement.
So you can also pass the query part from parameter.

Example: How to make order by as a parameterised.
select * from employee ORDER BY $P!{P_STUDENT_NAME}

Pass Parameter Value as : EMP_NAME DESC 
Note : Without " or ' sign as there is no quate in query.

JasperServer: How to generate/export report using url

http://<host>:<port>/<context>/flow.html?_flowId=viewReportFlow&reportUnit=/supermart/Details/XXX&employee=123&output=pdf

&output=pdf : Add this parameter in url to get a pdf file.
&output=xls  : Add this parameter in url to get a xls file.

Tags: 

Export PDF
Export Excel

Jasperserver.