Using CLOBs in oracle Report Builder


I want to select all the data in a CLOB column using SQL Navigator by just doing
select entry_note
from chartentries;
just as you say in your example by it just gives me back a result of (ORACLOB).
If I do
select to_char(entry_note)
from chartentries;
I get this error.
If I do
select dbms_lob.substr(entry_note,4000,1)
from chartentries
I get this (there’s more up to 32000 characters in a clob) and these weird squares which I think are CR (carriage returns) or LF (line feeds). I don’t want to see those characters and I would like to retrieve the entire CLOB in one swoop without doing data manipulations of substringing.
Using the same data in Oracle Report Builder, I want to select all the data in a CLOB column into a field in a Paper report layout but I keep getting errors, ora-6502, and I've found out that I am restricted to 4000 characters and in Report 9i it looks like the largest field you can have is 4000 characters. My CLOB information can be up to 32000 characters long or longer and I do not want to substring out 4000 characters at a time into separate fields. In Crystal reports there is no problem just selecting the clob in a simple query and it is there in one shot. I would have thought by now that Oracle would have changed its development tools to be able to handle Clobs simply without any manipulation. Does anyone know how to do this in Report builder? I just have a simple query:
select entry_datetime,
to_char(replace((entry_note),chr(13))) as entry_note
from chartentries
where patient_id = 49741
Another thing I want to do, is call this Oracle report from a PL/SQL block passing several parameters to the report and also call a Crystal report from a PL/SQL block passing several parameters to the report. Does anyone know what the commands are?


What I had to do is create a query using query builder. This works fine; it's when trying to add the column to an existing query is where the data inconstancy error occurs. One thing you have to keep in mind is it will only work using the query builder in conjunction with the report wizard; I am not sure why. To incorporate the comments into an existing frame you will have to cut the frame created by the wizards and copy it into the existing frame. Otherwise the column name will not show in the source list of the property Inspector.
Good Luck

October 11, 2015

Using CLOBs in oracle Report Builder

I want to select all the data in a CLOB column using SQL Navigator by just doing select entry_note from chartentries; just as you say in your example by it just gives me back a result of (ORACLOB). If I do select to_char(entry_note) from chartentries

Exception while opening a JSP file created using oracle report builder

I have created a JSP page which contains embedded Oracle reports build using Oracle Report Builder. I have deployed the JSP created above to the Oracle Application Server but when i try to open the JSP file it gives the following error: 500 Internal

ORACLE reports Build 10g - Data Model - query - If statement in Alias ?

I have the following select statement. It has the alias Survivors, Deaths and "All With the ORACLE reports Build 10g - Data Model - I have the following query statement. I require the alias to change. Can the following be done. Cases". Is it pos

Requirement with Oracle report builder

Hi All,, I had a requirement like this...i am very new to Oracle report builder tool and Oracle APPS i need to develop a sample report in this format.......please help me which tables i need to use?? i know how to create user parameters,field columns

How to start Oracle Report Builder in Linux

Hi all Can someone please help by telling how can i start Oracle Report Builder in Linux???? Thank you and best regards to allHello, there should be a script in ORACLE_HOME/bin for 10.1.2. in ORACLE_INSTANCE/config/reports/bin for 11g Of

How to install oracle report builder in windows 7

Hi Team, Could you guide me to install oracle report 11g in my PC . Previously i was using 10g . OS : win 7 DATABASE : 11gFollow How to install forms builder? | Oracle Community Hope read all and give a try. HamidRead other 2 answers

Using JNI in Oracle Reports

Hi. I have some difficulties understanding the Oracle Reports mechanism of JNI. The problem I have is the report job is crashed when I use the imported java class. I am using Oracle Reports on Oracle Linux The scenario of generating report i

Running Web Layout Of JDBC Query in Oracle Report Builder 11g.

javax.servlet.jsp.JspException: REP-4100: An attempt to execute data source failed. JDBCPDS-62008:A SQL error occurred: {0}. S1000 [Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt javax.servlet.jsp.JspException: RE

How to Invoke Oracle report builder installed on a server from a client

Hi, I am using rwrun60 command to invoke report builder through a shell script. In this case the report builder is installed on the same machine where the shell script exists. But now i have shell script which is on different server (say, Server1) an

Error when open Oracle Report Builder 10g (Windows 7 Ultimate 32-bit)

Hi all, I installed Oracle Developer 10g on windows 7 ultimate 32bit. Form builder running well, but the report got problem when i opened. This is the error message: Problem signature: Problem Event Name: APPCRASH Application Name: rwbuilder.exe Appl

Retrieve All records and display in Report using CAML query in Report Builder if Parameter value is blank

Hello Experts, i have created a report where i have one parameter field where user will pass parameter(e.g. EmpId). As per parameter record will fetched to report if no parameter is passed then it will display all records. I have done it by taking Sq

Mail Program used by Oracle reposts builder

I'am trying to send mail through oracle reports builder. But it seems like Virus Scan has blocked port 25 on my computer so I cannot send mails and I 'am getting the following error. REP -0069;Internal error REP -57054:In Process job terminate:Termin

Comma separated output using report builder

Hi Experts, I am working with EBS 11.5.10, database 9i, and report builder 6i. I have a XML Publisher report, with output type EXCEL, which is working fine. But if i get huge data like 1million, 2million, it's not able accommodate the output in EXCEL

Unix command in Report Builder

Is it possible to use UNIX command in Report builder because I am trying to move one file in UNIX directory to some other directory in UNIX also. Thanks Shishu Paul ChandigarhHye, Basically , I have attached this report in Oracle Apps 11.5.10 and wan

Report builder not printing

HI all, we were using Oracle Report 6i on windows XP Service pack 2, Oracle Database On AIX 5.2 and SEDCO Magina L2200c printer was being used to print the reports from report Builder 6i and it was working fine. but now just we change the

Images in Report Builder 6i

I am using the Oracle Report Builder version 6i . I am unable to display images in the generated reports in RTF (Rich Text Format) . I was suggested by someone to download a patch to ugrade the reports builder to version which is reports 6

Call Report Builder from Jdeveloper 10g with parameters

hello , how are you i am in oracle forms for long , today i am work in jdeveloper 10g, i want some questions 1- how call oracle report builder from oracle jdeveloper 10g with parameters 2- if there aren't call from jdeveloper 10g, what is good report

Problem with Report Builder 10g

Dear Members, I am using Oracle Report Builder for developing reports. I see that the option "Find and Replace in PL/SQL" in the EDIT Menu is Greyed out completely. This is a great feature which enables me to find the pl/sql code in v

Testing Reports within Reports Builder

I am using Oracle Reports Builder 9i I have created a simple report using a JDBC query and J_JDBCPDS. I have saved the file as a RDF and a JSP. I want to test this from within Reports Builder. I created a simple selection.HTM file that has

New documents:  Configure SSL and Integrate Oracle Reports

All, Be sure to check out these new documents from Oracle Support on the Oracle9iAS Portal Center. Support page: 1. Configure Oracle9iAS Portal to use SSL with Glob