Home » SQL & PL/SQL » SQL & PL/SQL » How to use DBMS_LOB package ..Please help
How to use DBMS_LOB package ..Please help [message #186232] Mon, 07 August 2006 02:39 Go to next message
sreehari
Messages: 101
Registered: May 2006
Senior Member
Hi all

i have the data with 25000 characters in XML format..
i want to insert this data in a table which has the column defined as CLOB..

DECLARE
MSG CLOB;
BEGIN
MSG:= 25000 Characters in XML format
insert into table values(msg);
end;

when i execute the above command, i am still getting the error like string literal is too large..

Some how i came to know that DBMS_LOB package in Pl/sql can solve this problem..but i never worked on this package..

can any one please come up with the solution..pls

help on this is greatly appreciated..

Thanks
Re: How to use DBMS_LOB package ..Please help [message #186266 is a reply to message #186232] Mon, 07 August 2006 04:03 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, it works for me.
SQL> CREATE TABLE clob_test  (col_1  VARCHAR2(10), col_2 CLOB);

Table created.

SQL> DECLARE
  2  MSG CLOB;
  3  BEGIN
  4  MSG:= RPAD('V',25000,'A');
  5  INSERT INTO clob_test VALUES('A',msg);
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL> SELECT col_1, LENGTH(col_2) FROM clob_test;

COL_1     |LENGTH(COL_2)
----------|-------------
A         |        25000


Are you absolutely sure that the field in your table is a CLOB?



Re: How to use DBMS_LOB package ..Please help [message #186272 is a reply to message #186266] Mon, 07 August 2006 04:36 Go to previous messageGo to next message
sreehari
Messages: 101
Registered: May 2006
Senior Member
Hi

This is how i worked

DECLARE
MSG CLOB;
BEGIN
MSG:= '<?xml version="1.0" encoding="UTF-8"  ?> 
<!-- Created with iReport - A designer for JasperReports --> 
<!DOCTYPE jasperReport PUBLIC "//JasperReports//DTD Report Design//EN" "http://jasperreports.sourceforge.net/dtds/jasperreport.dtd">

<jasperReport 
                 name="ActivatedLicAssetReportTemplate" 
                 language="java" 
                 columnCount="1" 
                 printOrder="Vertical" 
                 orientation="Portrait" 
                 pageWidth="842" 
                 pageHeight="595" 
                 columnWidth="782" 
                 columnSpacing="0" 
                 leftMargin="30" 
                 rightMargin="30" 
                 topMargin="20" 
                 bottomMargin="20" 
                 whenNoDataType="AllSectionsNoDetail" 
                 isTitleNewPage="false" 
                 isSummaryNewPage="false" 
                 resourceBundle="JasperReportsLocalization" 
                 whenResourceMissingType="Empty"> 
        <property name="ireport.scriptlethandling" value="0" /> 
        <property name="ireport.encoding" value="UTF-8" /> 
        <import value="java.util.*" /> 
        <import value="net.sf.jasperreports.engine.*" /> 
        <import value="net.sf.jasperreports.engine.data.*" /> 

        <parameter name="DataFile" isForPrompting="true" class="java.lang.String"/> 
        <parameter name="CompanyName" isForPrompting="true" class="java.lang.String"/> 
        <parameter name="CertStatus" isForPrompting="true" class="java.lang.String"/> 

        <field name="COMPANY_NAME" class="java.lang.String"/> 
        <field name="CITY" class="java.lang.String"/> 
        <field name="TARGET_ID" class="java.lang.String"/> 
        <field name="LKP(PRODUCT_LINE)PRODUCT_LINE" class="java.lang.String"/> 
        <field name="LKP(FAMILY)PRODUCT_FAMILY_CODE" class="java.lang.String"/> 
        <field name="LKP(COUNTRY)COUNTRY_CODE" class="java.lang.String"/> 
        <field name="EMAIL" class="java.lang.String"/> 
        <field name="LTU" class="java.math.BigDecimal"/> 

        <variable name="ltuGroupSum" class="java.math.BigDecimal" resetType="Group" incrementType="Group" incrementGroup="COMPANY_NAME" resetGroup="COMPANY_NAME" calculation="Sum">

                <variableExpression><![CDATA[$F{LTU}]]></variableExpression> 
        </variable> 
        <variable name="LtuSum" class="java.math.BigDecimal" resetType="Group" resetGroup="GROUP_LTU" calculation="Sum">

                <variableExpression><![CDATA[$F{LTU}]]></variableExpression> 
        </variable> 

                <group  name="COMPANY_NAME" > 
                        <groupExpression><![CDATA[$F{COMPANY_NAME}]]></groupExpression> 
                        <groupHeader> 
                        <band height="20"  isSplitAllowed="true" > 
                                <rectangle> 
                                        <reportElement 
                                                x="0" 
                                                y="0" 
                                                width="780" 
                                                height="20" 
                                                forecolor="#000000" 
                                                backcolor="#0066CC" 
                                                key="rectangle"/> 
                                        <graphicElement stretchType="NoStretch" pen="Thin"/> 
                                </rectangle> 
                                <textField isStretchWithOverflow="false" pattern="" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" >

                                        <reportElement 
                                                x="0" 
                                                y="0" 
                                                width="100" 
                                                height="20" 
                                                forecolor="#000000" 
                                                key="staticText-1_COMPANY_NAME"/> 
                                        <box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/>

                                        <textElement> 
                                                <font fontName="Helvetica" pdfFontName="Helvetica-Bold" size="12" isBold="true" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="CP1252" isStrikeThrough="false" />

                                        </textElement> 
                                <textFieldExpression   class="java.lang.String"><![CDATA[$R{COMPANY_NAME}]]></textFieldExpression>

                                </textField> 
                                <textField isStretchWithOverflow="true" pattern="" isBlankWhenNull="true" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" >

                                        <reportElement 
                                                x="105" 
                                                y="0" 
                                                width="144" 
                                                height="20" 
                                                forecolor="#000000" 
                                                key="textField-1_COMPANY_NAME"/> 
                                        <box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/>

                                        <textElement> 
                                                <font fontName="Helvetica" pdfFontName="Helvetica" size="12" isBold="false" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="CP1252" isStrikeThrough="false" />

                                        </textElement> 
                                <textFieldExpression   class="java.lang.String"><![CDATA[$F{COMPANY_NAME}]]></textFieldExpression>

                                </textField> 
                                <textField isStretchWithOverflow="false" pattern="" isBlankWhenNull="true" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" >

                                        <reportElement 
                                                x="250" 
                                                y="0" 
                                                width="42" 
                                                height="20" 
                                                forecolor="#000000" 
                                                key="staticText-2_EMAIL"/> 
                                        <box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/>

                                        <textElement> 
                                                <font fontName="Helvetica" pdfFontName="Helvetica" size="12" isBold="false" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="CP1252" isStrikeThrough="false" />

                                        </textElement> 
                                <textFieldExpression   class="java.lang.String"><![CDATA[$R{EMAIL}]]></textFieldExpression>

                                </textField> 
                                <textField isStretchWithOverflow="false" pattern="" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" >

                                        <reportElement 
                                                x="296" 
                                                y="0" 
                                                width="49" 
                                                height="20" 
                                                forecolor="#000000" 
                                                key="textField-2_EMAIL"/> 
                                        <box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/>

                                        <textElement> 
                                                <font fontName="Arial" pdfFontName="Helvetica" size="12" isBold="false" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="Cp1252" isStrikeThrough="false" />

                                        </textElement> 
                                <textFieldExpression   class="java.lang.String"><![CDATA[$F{EMAIL}]]></textFieldExpression>

                                </textField> 
                                <textField isStretchWithOverflow="false" pattern="" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" >

                                        <reportElement 
                                                x="350" 
                                                y="0" 
                                                width="29" 
                                                height="20" 
                                                forecolor="#000000" 
                                                key="staticText-3_CITY"/> 
                                        <box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/>

                                        <textElement> 
                                                <font fontName="Helvetica" pdfFontName="Helvetica-Bold" size="12" isBold="true" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="CP1252" isStrikeThrough="false" />

                                        </textElement> 
                                <textFieldExpression   class="java.lang.String"><![CDATA[$R{CITY}]]></textFieldExpression>

                                </textField> 
                                <textField isStretchWithOverflow="true" pattern="" isBlankWhenNull="true" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" >

                                        <reportElement 
                                                x="381" 
                                                y="0" 
                                                width="149" 
                                                height="20" 
                                                forecolor="#000000" 
                                                key="textField-3_CITY"/> 
                                        <box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/>

                                        <textElement> 
                                                <font fontName="Helvetica" pdfFontName="Helvetica" size="12" isBold="false" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="CP1252" isStrikeThrough="false" />

                                        </textElement> 
                                <textFieldExpression   class="java.lang.String"><![CDATA[$F{CITY}]]></textFieldExpression>

                                </textField> 
                                <textField isStretchWithOverflow="false" pattern="" isBlankWhenNull="true" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" >

                                        <reportElement 
                                                x="538" 
                                                y="0" 
                                                width="65" 
                                                height="20" 
                                                forecolor="#000000" 
                                                key="textField-4_COUNTRY_CODE"/> 
                                        <box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/>

                                        <textElement> 
                                                <font fontName="Helvetica" pdfFontName="Helvetica" size="12" isBold="false" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="CP1252" isStrikeThrough="false" />

                                        </textElement> 
                                <textFieldExpression   class="java.lang.String"><![CDATA[$F{LKP(COUNTRY)COUNTRY_CODE}]]></textFieldExpression>

                                </textField> 
                                <textField isStretchWithOverflow="false" pattern="" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" >

                                        <reportElement 
                                                x="608" 
                                                y="0" 
                                                width="50" 
                                                height="20" 
                                                forecolor="#000000" 
                                                key="staticText-5_LTU"/> 
                                        <box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/>

                                        <textElement> 
                                                <font fontName="" pdfFontName="Helvetica-Bold" size="12" isBold="true" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="Cp1250" isStrikeThrough="false" />

                                        </textElement> 
                                <textFieldExpression   class="java.lang.String"><![CDATA[$R{LTU}]]></textFieldExpression>

                                </textField> 
                                <textField isStretchWithOverflow="false" pattern="#0" isBlankWhenNull="false" evaluationTime="Group" evaluationGroup="COMPANY_NAME"  hyperlinkType="None"  hyperlinkTarget="Self" >

                                        <reportElement 
                                                x="665" 
                                                y="0" 
                                                width="50" 
                                                height="20" 
                                                forecolor="#000000" 
                                                backcolor="#FFCCCC" 
                                                key="textField-5_ltuGroupSum"/> 
                                        <box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/>

                                        <textElement> 
                                                <font fontName="Helvetica" pdfFontName="Helvetica" size="12" isBold="false" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="CP1252" isStrikeThrough="false" />

                                        </textElement> 
                                <textFieldExpression   class="java.math.BigDecimal"><![CDATA[$V{ltuGroupSum}]]></textFieldExpression>

                                </textField> 
                                <textField isStretchWithOverflow="false" pattern="#0" isBlankWhenNull="true" evaluationTime="Group" evaluationGroup="COMPANY_NAME"  hyperlinkType="None"  hyperlinkTarget="Self" >

                                        <reportElement 
                                                x="719" 
                                                y="0" 
                                                width="50" 
                                                height="20" 
                                                forecolor="#000000" 
                                                backcolor="#FFCCCC" 
                                                key="textField-5_LtuSum"/> 
                                        <box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/>

                                        <textElement> 
                                                <font fontName="Helvetica" pdfFontName="Helvetica" size="12" isBold="false" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="CP1252" isStrikeThrough="false" />

                                        </textElement> 
                                <textFieldExpression   class="java.math.BigDecimal"><![CDATA[$V{LtuSum}]]></textFieldExpression>

                                </textField> 
                        </band> 
                        </groupHeader> 
                        <groupFooter> 
                        <band height="2"  isSplitAllowed="true" > 
                                <line direction="BottomUp"> 
                                        <reportElement 
                                                x="0" 
                                                y="-6" 
                                                width="1" 
                                                height="1" 
                                                key="line"/> 
                                        <graphicElement stretchType="NoStretch" pen="Thin"/> 
                                </line> 
                        </band> 
                        </groupFooter> 
                </group> 
                <group  name="TARGET_ID" > 
                        <groupExpression><![CDATA[$F{TARGET_ID}]]></groupExpression> 
                        <groupHeader> 
                        <band height="20"  isSplitAllowed="true" > 
                                <rectangle> 
                                        <reportElement 
                                                x="35" 
                                                y="0" 
                                                width="745" 
                                                height="20" 
                                                forecolor="#000000" 
                                                backcolor="#0099CC" 
                                                key="rectangle-1" 
                                                positionType="FixRelativeToBottom"/> 
                                        <graphicElement stretchType="NoStretch" pen="Thin"/> 
                                </rectangle> 
                                <textField isStretchWithOverflow="false" pattern="" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" >

                                        <reportElement 
                                                x="35" 
                                                y="0" 
                                                width="100" 
                                                height="20" 
                                                forecolor="#000000" 
                                                key="staticText"/> 
                                        <box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/>

                                        <textElement> 
                                                <font fontName="Helvetica" pdfFontName="Helvetica-Bold" size="12" isBold="true" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="CP1252" isStrikeThrough="false" />

                                        </textElement> 
                                <textFieldExpression   class="java.lang.String"><![CDATA[$R{TARGET_ID}]]></textFieldExpression>

                                </textField> 
                                <textField isStretchWithOverflow="false" pattern="" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" >

                                        <reportElement 
                                                x="140" 
                                                y="0" 
                                                width="300" 
                                                height="20" 
                                                forecolor="#000000" 
                                                key="textField"/> 
                                        <box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/>

                                        <textElement> 
                                                <font fontName="Helvetica" pdfFontName="Helvetica" size="12" isBold="false" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="CP1252" isStrikeThrough="false" />

                                        </textElement> 
                                <textFieldExpression   class="java.lang.String"><![CDATA[$F{TARGET_ID}]]></textFieldExpression>

                                </textField> 
                        </band> 
                        </groupHeader> 
                        <groupFooter> 
                        <band height="2"  isSplitAllowed="true" > 
                                <line direction="BottomUp"> 
                                        <reportElement 
                                                x="0" 
                                                y="-5" 
                                                width="1" 
                                                height="1" 
                                                key="line"/> 
                                        <graphicElement stretchType="NoStretch" pen="Thin"/> 
                                </line> 
                        </band> 
                        </groupFooter> 
                </group> 
                <group  name="PRODUCT_LINE" > 
                        <groupExpression><![CDATA[$F{LKP(PRODUCT_LINE)PRODUCT_LINE}]]></groupExpression> 
                        <groupHeader> 
                        <band height="20"  isSplitAllowed="true" > 
                                <rectangle> 
                                        <reportElement 
                                                x="35" 
                                                y="0" 
                                                width="745" 
                                                height="20" 
                                                forecolor="#000000" 
                                                backcolor="#009999" 
                                                key="rectangle-2" 
                                                positionType="FixRelativeToBottom"/> 
                                        <graphicElement stretchType="NoStretch" pen="Thin"/> 
                                </rectangle> 
                                <textField isStretchWithOverflow="false" pattern="" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" >

                                        <reportElement 
                                                x="35" 
                                                y="0" 
                                                width="100" 
                                                height="20" 
                                                forecolor="#000000" 
                                                key="staticText"/> 
                                        <box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/>

                                        <textElement> 
                                                <font fontName="Helvetica" pdfFontName="Helvetica-Bold" size="12" isBold="true" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="CP1252" isStrikeThrough="false" />

                                        </textElement> 
                                <textFieldExpression   class="java.lang.String"><![CDATA[$R{PRODUCT_LINE}]]></textFieldExpression>

                                </textField> 
                                <textField isStretchWithOverflow="false" pattern="" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" >

                                        <reportElement 
                                                x="140" 
                                                y="0" 
                                                width="300" 
                                                height="20" 
                                                forecolor="#000000" 
                                                key="textField"/> 
                                        <box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/>

                                        <textElement> 
                                                <font fontName="Helvetica" pdfFontName="Helvetica" size="12" isBold="false" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="CP1252" isStrikeThrough="false" />

                                        </textElement> 
                                <textFieldExpression   class="java.lang.String"><![CDATA[$F{LKP(PRODUCT_LINE)PRODUCT_LINE}]]></textFieldExpression>

                                </textField> 
                        </band> 
                        </groupHeader> 
                        <groupFooter> 
                        <band height="10"  isSplitAllowed="true" > 
                        </band> 
                        </groupFooter> 
                </group> 
                <group  name="GROUP_LTU" > 
                        <groupExpression><![CDATA[$F{LTU}]]></groupExpression> 
                        <groupHeader> 
                        <band height="0"  isSplitAllowed="true" > 
                        </band> 
                        </groupHeader> 
                        <groupFooter> 
                        <band height="0"  isSplitAllowed="true" > 
                        </band> 
                        </groupFooter> 
                </group> 
                <background> 
                        <band height="0"  isSplitAllowed="true" > 
                        </band> 
                </background> 
                <title> 
                        <band height="30"  isSplitAllowed="true" > 
                                <line direction="TopDown"> 
                                        <reportElement 
                                                x="0" 
                                                y="0" 
                                                width="782" 
                                                height="1" 
                                                forecolor="#808080" 
                                                key="line"/> 
                                        <graphicElement stretchType="NoStretch" pen="2Point"/> 
                                </line> 
                                <textField isStretchWithOverflow="false" pattern="" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" >

                                        <reportElement 
                                                x="0" 
                                                y="5" 
                                                width="782" 
                                                height="20" 
                                                key="staticText"/> 
                                        <box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/>

                                        <textElement textAlignment="Center"> 
                                                <font fontName="Helvetica" pdfFontName="Helvetica" size="14" isBold="true" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="CP1252" isStrikeThrough="false" />

                                        </textElement> 
                                <textFieldExpression   class="java.lang.String"><![CDATA[$R{activation_report_title}]]></textFieldExpression>

                                </textField> 
                                <line direction="TopDown"> 
                                        <reportElement 
                                                x="0" 
                                                y="25" 
                                                width="782" 
                                                height="1" 
                                                forecolor="#808080" 
                                                key="line"/> 
                                        <graphicElement stretchType="NoStretch" pen="2Point"/> 
                                </line> 
                        </band> 
                </title> 
                <pageHeader> 
                        <band height="0"  isSplitAllowed="true" > 
                        </band> 
                </pageHeader> 
                <columnHeader> 
                        <band height="0"  isSplitAllowed="true" > 
                        </band> 
                </columnHeader> 
                <detail> 
                        <band height="45"  isSplitAllowed="true" > 
                                <textField isStretchWithOverflow="false" pattern="" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" >

                                        <reportElement 
                                                x="50" 
                                                y="0" 
                                                width="100" 
                                                height="20" 
                                                key="staticText-1_FAMILY"/> 
                                        <box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/>

                                        <textElement> 
                                                <font fontName="" pdfFontName="Helvetica" size="12" isBold="false" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="Cp1250" isStrikeThrough="false" />

                                        </textElement> 
                                <textFieldExpression   class="java.lang.String"><![CDATA[$R{FAMILY}]]></textFieldExpression>

                                </textField> 
                                <textField isStretchWithOverflow="false" pattern="" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" >

                                        <reportElement 
                                                x="150" 
                                                y="0" 
                                                width="335" 
                                                height="20" 
                                                key="textField-1_FAMILY"/> 
                                        <box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/>

                                        <textElement> 
                                                <font fontName="Helvetica" pdfFontName="Helvetica" size="12" isBold="false" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="CP1252" isStrikeThrough="false" />

                                        </textElement> 
                                <textFieldExpression   class="java.lang.String"><![CDATA[$F{LKP(FAMILY)PRODUCT_FAMILY_CODE}]]></textFieldExpression>

                                </textField> 
                                <textField isStretchWithOverflow="false" pattern="" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" >

                                        <reportElement 
                                                x="50" 
                                                y="20" 
                                                width="100" 
                                                height="20" 
                                                key="staticText-2_LTU"/> 
                                        <box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/>

                                        <textElement> 
                                                <font fontName="" pdfFontName="Helvetica" size="12" isBold="false" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="Cp1250" isStrikeThrough="false" />

                                        </textElement> 
                                <textFieldExpression   class="java.lang.String"><![CDATA[$R{LTU}]]></textFieldExpression>

                                </textField> 
                                <textField isStretchWithOverflow="false" pattern="" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" >

                                        <reportElement 
                                                x="150" 
                                                y="20" 
                                                width="335" 
                                                height="20" 
                                                key="textField-2_LTU"/> 
                                        <box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/>

                                        <textElement> 
                                                <font fontName="Helvetica" pdfFontName="Helvetica" size="12" isBold="false" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="CP1252" isStrikeThrough="false" />

                                        </textElement> 
                                <textFieldExpression   class="java.math.BigDecimal"><![CDATA[$F{LTU}]]></textFieldExpression>

                                </textField> 
                        </band> 
                </detail> 
                <columnFooter> 
                        <band height="0"  isSplitAllowed="true" > 
                        </band> 
                </columnFooter> 
                <pageFooter> 
                        <band height="40"  isSplitAllowed="true" > 
                                <line direction="TopDown"> 
                                        <reportElement 
                                                x="0" 
                                                y="0" 
                                                width="782" 
                                                height="1" 
                                                key="line"/> 
                                        <graphicElement stretchType="NoStretch" pen="2Point"/> 
                                </line> 
                                <image  scaleImage="FillFrame" isUsingCache="false" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" >

                                        <reportElement 
                                                mode="Opaque" 
                                                x="0" 
                                                y="5" 
                                                width="140" 
                                                height="35" 
                                                key="image-1"/> 
                                        <box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/>

                                        <graphicElement stretchType="NoStretch"/> 
                                        <imageExpression class="java.lang.String"><![CDATA[$R{image_ocs_logo}]]></imageExpression>

                                </image> 
                                <textField isStretchWithOverflow="false" pattern="" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" >

                                        <reportElement 
                                                x="145" 
                                                y="0" 
                                                width="200" 
                                                height="15" 
                                                key="textField"/> 
                                        <box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/>

                                        <textElement textAlignment="Right"> 
                                                <font fontName="Helvetica" pdfFontName="Helvetica" size="10" isBold="false" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="CP1252" isStrikeThrough="false" />

                                        </textElement> 
                                <textFieldExpression   class="java.lang.String"><![CDATA["Page " + $V{PAGE_NUMBER} + " of "]]></textFieldExpression>

                                </textField> 
                                <textField isStretchWithOverflow="false" pattern="" isBlankWhenNull="false" evaluationTime="Report" hyperlinkType="None"  hyperlinkTarget="Self" >

                                        <reportElement 
                                                x="351" 
                                                y="0" 
                                                width="127" 
                                                height="15" 
                                                key="textField"/> 
                                        <box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/>

                                        <textElement> 
                                                <font fontName="Helvetica" pdfFontName="Helvetica" size="10" isBold="false" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="CP1252" isStrikeThrough="false" />

                                        </textElement> 
                                <textFieldExpression   class="java.lang.String"><![CDATA["" + $V{PAGE_NUMBER}]]></textFieldExpression>

                                </textField> 
                                <textField isStretchWithOverflow="false" pattern="" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" >

                                        <reportElement 
                                                x="607" 
                                                y="0" 
                                                width="69" 
                                                height="15" 
                                                key="textField-5"/> 
                                        <box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/>

                                        <textElement> 
                                                <font fontName="Arial" pdfFontName="Helvetica" size="10" isBold="false" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="Cp1252" isStrikeThrough="false" />

                                        </textElement> 
                                <textFieldExpression   class="java.lang.String"><![CDATA[$R{execution_date}]]></textFieldExpression>

                                </textField> 
                                <textField isStretchWithOverflow="false" pattern="" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" >

                                        <reportElement 
                                                x="679" 
                                                y="0" 
                                                width="101" 
                                                height="15" 
                                                key="textField"/> 
                                        <box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/>

                                        <textElement> 
                                                <font fontName="Helvetica" pdfFontName="Helvetica" size="10" isBold="false" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="CP1252" isStrikeThrough="false" />

                                        </textElement> 
                                <textFieldExpression   class="java.util.Date"><![CDATA[new Date()]]></textFieldExpression>

                                </textField> 
                                <textField isStretchWithOverflow="false" pattern="" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" >

                                        <reportElement 
                                                x="145" 
                                                y="15" 
                                                width="350" 
                                                height="10" 
                                                key="textField-3"/> 
                                        <box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/>

                                        <textElement textAlignment="Center"> 
                                                <font fontName="Arial" pdfFontName="Helvetica" size="8" isBold="false" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="Cp1252" isStrikeThrough="false" />

                                        </textElement> 
                                <textFieldExpression   class="java.lang.String"><![CDATA[$R{disclaimer_line_1}]]></textFieldExpression>

                                </textField> 
                                <textField isStretchWithOverflow="false" pattern="" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" >

                                        <reportElement 
                                                x="145" 
                                                y="30" 
                                                width="350" 
                                                height="10" 
                                                key="textField-4"/> 
                                        <box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/>

                                        <textElement textAlignment="Center"> 
                                                <font fontName="Arial" pdfFontName="Helvetica" size="8" isBold="false" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="Cp1252" isStrikeThrough="false" />

                                        </textElement> 
                                <textFieldExpression   class="java.lang.String"><![CDATA[$R{disclaimer_line_2}]]></textFieldExpression>

                                </textField> 
                        </band> 
                </pageFooter> 
                <summary> 
                        <band height="0"  isSplitAllowed="true" > 
                        </band> 
                </summary> 
</jasperReport>'; 
 
insert into report_info_detail (REPORT_INFO_ID, REPORT_EXPORT_TYPE_CODE, PLUGIN_CLASS_NAME, IS_ACTIVE,REPORT_TEMPLATE) 
select r.report_info_id, --REPORT_INFO_ID 
        'pdf', --REPORT_EXPORT_TYPE_CODE (htm, pdf,csv) 
        'com.ocs.poetic.reports.JRActivatedLicAssetReportPlugin',--PLUGIN_CLASS_NAME 
        -1, --IS_ACTIVE 
        MSG --REPORT_TEMPLATE
		from report_info r where security_resource_name = 'ACTIVATED_LIC_ASSET_REPORT'
END;		
 


when i execute the above command..i am still getinng the following errors..

ORA-06550: line 4, column 7:
PLS-00172: string literal too long
ORA-06550: line 677, column 82:
PL/SQL: ORA-00933: SQL command not properly ended
ORA-06550: line 671, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 679, column 0:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the follow

begin case declare end exception exit for goto if loop mod
null pragma raise return select update while with
<an identifier> <a double-quoted



How to solve this issue...

Thanks
Re: How to use DBMS_LOB package ..Please help [message #186278 is a reply to message #186272] Mon, 07 August 2006 05:00 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
PLEASE!!!
Don't paster 25k of text into your replies any more. It makes them very difficult to read. If you have to, just add it as an attachment.

Still works for me I'm afraid.
I replaced your insert statement with my one into CLOB_TEST and it works just fine in TOAD and SQL*Plus.

Is the target datatype CLOB or XMLTYPE?
Re: How to use DBMS_LOB package ..Please help [message #186285 is a reply to message #186278] Mon, 07 August 2006 05:24 Go to previous messageGo to next message
sreehari
Messages: 101
Registered: May 2006
Senior Member
First of all sorry for my lengthy reply..

For me it is still giving the same error...

ORA-06550: line 4, column 6:
PLS-00172: string literal too long


The target data type is CLOB in oralce table

Thanks
Re: How to use DBMS_LOB package ..Please help [message #186287 is a reply to message #186285] Mon, 07 August 2006 05:39 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What version of Oracle are you using.

First:
Can you try running your code, but replace the INSERT ... SELECT with an INSERT ... VALUES. Let me know what happens.

Second:
Can you get me a CREATE TABLE script for the table you use.
After you've written the script, just run it and check that you definitely get the error against the table it creates.
If you do, then post it here and I'll check to see if I get the same error

[edited to finish a sentence I got distracted during]

[Updated on: Mon, 07 August 2006 05:40]

Report message to a moderator

Re: How to use DBMS_LOB package ..Please help [message #186290 is a reply to message #186287] Mon, 07 August 2006 05:58 Go to previous messageGo to next message
sreehari
Messages: 101
Registered: May 2006
Senior Member
The Oracle version we are using is 9i

i tried replacing SELECT statement in INSERT command as u mentioned.still, the problem exists.

The table structure of report_info_detail is as follows..
Column Name ID Pk Null? Data Type Default

REPORT_INFO_ID 1 N NUMBER
REPORT_EXPORT_TYPE_CODE 2 N NVARCHAR2 (10)
PLUGIN_CLASS_NAME 3 N NVARCHAR2 (300)
IS_ACTIVE 4 Y NUMBER (1)
UPD_DATE 5 Y DATE
UPD_VERSION 6 Y INTEGER
UPD_USER 7 Y NVARCHAR2 (20)
REPORT_TEMPLATE 8 Y CLOB


When i checked the MAXDATA parameter in Environment varialble in SQL*Plus, it was only 32767,but when i checked the wordcount of whole XML data, it is around 44000 (including spaces)..

Is that in any way causing the problem..I suspect..

Thanks
Re: How to use DBMS_LOB package ..Please help [message #186292 is a reply to message #186290] Mon, 07 August 2006 06:13 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
MAXDATA shouldn't make a difference.

It would have been a nice touch if you could have actually posted the CREATE TABLE script that I asked for, rather than just doing a DESC and expecting me to convert it into SQL.
Still, thats about par for the course round here.

I've run the code inserting into the table with a select of data from dual, and it works fine in TOAD, and SQL*Plus with MAXDATA set to 0 and 32767 just in case.

Can you check that the insert statement works if you replace the MSG in the insert with a null - that will confirm if the problem is definitely with the CLOB or not.
Re: How to use DBMS_LOB package ..Please help [message #186297 is a reply to message #186292] Mon, 07 August 2006 06:23 Go to previous messageGo to next message
sreehari
Messages: 101
Registered: May 2006
Senior Member

if i remove the some of the data and make it close to around 32767 characters,,the insert statement is working fine..

Why it is not accepting more data..?

Thanks

Re: How to use DBMS_LOB package ..Please help [message #186299 is a reply to message #186297] Mon, 07 August 2006 06:38 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Are you doing an XMLTYPE conversion on the data anywhere. Metalink has bug 358437.1 which sounds similar to your problem if you are.

It works over here - I suspect it's something to do with your setup.
Can you try running the code from TOAD or SQL Developer and see if you still get the problem - that would let you know whether it is SQL*Plus that's tripping you up.
Re: How to use DBMS_LOB package ..Please help [message #186304 is a reply to message #186299] Mon, 07 August 2006 06:53 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Is the code you're running the exact code that you posted in your OP?
Is there ANY possibility that you're trying to put the text into a Varchar2 at any point?
Re: How to use DBMS_LOB package ..Please help [message #186313 is a reply to message #186304] Mon, 07 August 2006 07:41 Go to previous message
sreehari
Messages: 101
Registered: May 2006
Senior Member
Hi

Finally it worked..i suspect the issue is with the insert statement i received through the local mail system from the developer..i copied and pasted that insert statement in TOAD and executed where i think it attained lot of spaces and thrown the erros like sting literal too long..

now i went to the developer system and executed the above command, and it is working fine inserting all the data..


any comments on my assumption for the cause of problem are most welcome.

Thanks a lot for ur valuabe support..

Previous Topic: function parameters
Next Topic: Insert New Line in Database Trigger
Goto Forum:
  


Current Time: Fri Dec 02 14:14:09 CST 2016

Total time taken to generate the page: 0.12633 seconds