How many links can be put inside a report? - Oracle Application Server Portal

We have a report which has a varchar2(4000) note field, and we
also put 4 links in this report. When the note field is empty or
contains very few characters, the report runs OK. But when the
note field is filled up (around 2000 characters), we get this
error:
Unable to perform query (WWV-10202)
ORA-06502: PL/SQL: numeric or value error: character string
buffer too small (WWV-11230)
No row returns
Can anyone explain why this occurs? Thanks in advance!

Shu,
From ur post I can not make out the exact problem. There is no
limitation on the number of links that you can attach. Most
probably something is wrong with the note field. Pls post ur
portal version with table structure so that we can test it here.
Thanx,
Chetan. 

Chetan,
I am using Portal 3.09. The table structure is like this:
SQL> desc person_contact
Name Null? Type
----------------------------------------- --------
----------------------
PERSON_ID NOT NULL NUMBER(10)
SALUTATION NOT NULL NUMBER(10)
FIRST_NAME NOT NULL VARCHAR2(30)
LAST_NAME NOT NULL VARCHAR2(30)
MIDDLE_NAME VARCHAR2(30)
SUFFIX NUMBER(10)
NICK_NAME VARCHAR2(20)
NOTES VARCHAR2(4000)
OFFICE_PHONE VARCHAR2(20)
TOLL_FREE_PHONE VARCHAR2(20)
FAX VARCHAR2(20)
MOBIL_PHONE VARCHAR2(20)
SECRETARY VARCHAR2(50)
PAGER_PHONE VARCHAR2(20)
HOME_PHONE VARCHAR2(20)
OTHER_PHONE VARCHAR2(20)
EMAIL VARCHAR2(60)
WEB VARCHAR2(50)
MAIL_ADD_1 NOT NULL VARCHAR2(50)
MAIL_ADD_2 VARCHAR2(50)
MAIL_CITY NOT NULL VARCHAR2(30)
MAIL_COUNTY VARCHAR2(30)
MAIL_STATE VARCHAR2(30)
MAIL_ZIP VARCHAR2(10)
MAIL_COUNTRY NOT NULL VARCHAR2(30)
STREET_ADD_1 VARCHAR2(50)
STREET_ADD_2 VARCHAR2(50)
STREET_CITY VARCHAR2(30)
STREET_COUNTY VARCHAR2(30)
STREET_STATE VARCHAR2(30)
STREET_ZIP VARCHAR2(10)
STREET_COUNTRY VARCHAR2(30)
TITLE VARCHAR2(50)
LEAD_STAFF_ID NUMBER(10)
RECEIVE_MAIL VARCHAR2(1)
LAST_CHANGED_BY VARCHAR2(50)
LAST_CHANGED_DATE DATE
I made a report from Query Wizard, the note field can be up to
1986, Once above this length, I will get the error. If I take off
parameters which pass to the links, the report run fine, Once I
add a parameter to any of the link, I will get this same error
message:
Unable to perform query (WWV-10202)
ORA-06502: PL/SQL: numeric or value error: character string
buffer too small (WWV-11230)
I notice the limit for the note field has nothing to do with
other fields, when the note field is under 1986 characters, other
fields can be all filled and the report is still working with
links and parameters passing. Please let me know when you figure
it out. Thank you very much!
Shu 

Chetan,
The portal version I am using is 3.0.9.8.1
Thanks 

Shu,
from what I can make out the report uses htp.p package for
which the limitation is 2000.
You can try spliting your Note field of varchar2(4000) into two
Notes field of 2000 each.
Sorry, for now I have only this workaround. Will get back to you
if I find a better solution.
Thanx,
Chetan. 

I think I'm having the same problem ('character string buffer too small (WWV-11230)'- error in my report). Is there another workaround available?
Edward
Shu,
from what I can make out the report uses htp.p package for
which the limitation is 2000.
You can try spliting your Note field of varchar2(4000) into two
Notes field of 2000 each.
Sorry, for now I have only this workaround. Will get back to you
if I find a better solution.
Thanx,
Chetan.

Related

blob image is not displaying in interactive report

Dear all, i am using Application Express 4.2.6.00.03 on windows 8 using oracle database 11g and internet explorer 10. i have create a form with report on emp table.my emp table structure is below
SQL> desc emp
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
EMP_ID                                    NOT NULL NUMBER
EMP_NAME                                  NOT NULL VARCHAR2(60)
EMP_FNAME                                 NOT NULL VARCHAR2(60)
POST_ID                                   NOT NULL NUMBER
COUNTRY_ID                                NOT NULL NUMBER
CITY_ID                                   NOT NULL NUMBER
DOB                                                DATE
NIC                                                VARCHAR2(20)
GENDER                                             CHAR(1)
MARITAL_STATUS                                     CHAR(1)
BLOOD_GROUP                                        VARCHAR2(4)
PERMANENT_ADDRESS                                  VARCHAR2(200)
TEMPORARY_ADDRESS                                  VARCHAR2(200)
PHONE                                              NUMBER
MOBILE                                             NUMBER
EMAIL                                              VARCHAR2(50)
DRIVING_LICENSE_NUM                                VARCHAR2(25)
JOINING_DATE                              NOT NULL DATE
STATUS                                             VARCHAR2(8)
REMARKS                                            VARCHAR2(200)
PHOTO                                              BLOB
FILENAME                                           VARCHAR2(255)
MIMETYPE                                           VARCHAR2(255)
LAST_UPDATE_DATE                                   DATE
 when it ask for column in the report, i put all the columns in report, but at runtime or even in the design itself, the following columns are not displayedPHOTO, FILENAME,MIMETYPE,LAST_UPDATE_DATE why?i have aslo use the source for my report as belowselect "EMP_ID", "EMP_NAME","POST_ID","CITY_ID","PHONE","MOBILE","JOINING_DATE",'<img src="'|| apex_util.get_blob_file_src('P23_PHOTO',EMP.EMP_ID)||'"height="100" width="100"/>' "PHOTO","FILENAME","MIMETYPE","LAST_UPDATE_DATE"from "#OWNER#"."EMP"  and then change the attribute of column PHOTO to STANDARD REPORT COLUMN, but invain.I have also change the Number / Date Format for the PHOTO column and provide the MIMETYPE etc. still not displaying.how to display them? i have read a many threads in this forms as well as search youtube but failed. kinely guide me. Regards.
check my application,its done!woskpace:ram_r&duser/password:aramani/apexapp page : https://apex.oracle.com/pls/apex/f?p=19617:111:3568927772329::NO::: demo: https://apex.oracle.com/pls/apex/f?p=39061:1:2367830160025:::::related thread: Display a single BLOB image stored in  table in an HTML region

oracle Explorer plugin creating invalid SQL for table create (ORA-00984)

I used the "Oracle Developer Tools for Visual Studio .NET 10.2.0.1.0" oracle explorer to create a table. When I saved, I got a ORA-00984 error. The problem seams to be that the tool is not enclosing default string/char values in quotes, so it is getting interpreted as a column name.
My fix was to enclose the default values in quotes (example: DEFAULT "W" instead of DEFAULT W).
here is the DB output from the tool:
CREATE TABLE "PELDEV"."SLACR_JOB" ("SLA_ID" VARCHAR2(10) NOT NULL,"TIME_START_SCHED" DATE NOT NULL,"STATUS" CHAR(1) DEFAULT W NOT NULL,"IS_WAIVED" CHAR(1) DEFAULT N NOT NULL,"TIME_START_ACTUAL" DATE NULL,"TIME_END_SCHED" DATE NULL,"TIME_END_ACTUAL" DATE NULL,"SYSTEM" VARCHAR2(10) NULL,"APPL_ID" VARCHAR2(4) NULL,"NAME_START" VARCHAR2(40) NULL,"NAME_END" VARCHAR2(40) NULL,"TICKET" VARCHAR2(20) NULL,"TICKET_PRIORITY" INT DEFAULT 0 NOT NULL,"ONLINE_IMPACTING" CHAR(1) DEFAULT N NOT NULL,"BILLING_RELATED" CHAR(1) DEFAULT N NOT NULL,"IN_WARRANTY" CHAR(1) DEFAULT N NOT NULL,"LAST_UPDATED_BY" VARCHAR2(8) NULL,"TEMPLATE_ID" INT NOT NULL,"NOTES_ID" INT NULL)
Failed: 1 errors, 0 warnings
An error occurred while saving the table:
ORA-00984: column not allowed here 
Thank you very much for the bug report. I have alerted the engineering staff.
Christian

htmldb_item.select_list_from_query-report error: ORA-01722: invalid number

Hi, i am trying to create a tabular form
with one of the item's value depending on another item's value, but i have an ORA-01722: invalid number error
my application is:
DESC AC6LIS
ACLIS_SEQ_ID NOT NULL NUMBER(10)
ACLIS_TSG_ID NOT NULL VARCHAR2(5)
ACLIS_EDZ_ID NOT NULL VARCHAR2(3)
--
DESC VE1EDZ
EDZ_TSG_ID NOT NULL VARCHAR2(5)
EDZ_ID NOT NULL VARCHAR2(3)
EDZ_DESCRIZ VARCHAR2(50)
select ACLIS_TSG_ID, htmldb_item.select_list_from_query(2,ACLIS_EDZ_ID,'select EDZ_DESCRIZ, EDZ_ID from ve1edz where ve1edz.EDZ_TSG_ID ='||ac6lis.ACLIS_TSG_ID) edz_select_list from ac6lis
Any Help?
Thans in advance
Costantino 
When your inner SELECT statement is firing it is doing a type conversion. It is seeing a number value in 'ac6lis.ACLIS_TSG_ID' and trying to do a number comparison with 've1edz.EDZ_TSG_ID'. 've1edz.EDZ_TSG_ID' has a non-numeric value in one of the rows which results in your error. To correct this place quotes around the value of 'ac6lis.ACLIS_TSG_ID' like:
select ACLIS_TSG_ID,
       htmldb_item.select_list_from_query(2,ACLIS_EDZ_ID,'select EDZ_DESCRIZ, EDZ_ID from ve1edz where ve1edz.EDZ_TSG_ID ='''||ac6lis.ACLIS_TSG_ID||'''') edz_select_list
  from ac6lisMike 
Hi Mike,
thank you for the help
it's work fine!
is there a way to avoid the % (null) showing up ?
Thanks in advance
Costantino 
Try passing in 'NO' for the 5th parameter to SELECT_LIST_FROM_QUERY:
  htmldb_item.select_list_from_query(2,ACLIS_EDZ_ID,'select EDZ_DESCRIZ, EDZ_ID from ve1edz where ve1edz.EDZ_TSG_ID ='''||ac6lis.ACLIS_TSG_ID||'''',NULL,'NO')Mike 
Hi Mike,
it's work!!
Thanks again.
Costantino

sqlldr question:   Field in data file exceeds maximum length

hello friends,
I am struggling with a simple data load using sqlldr and hoping someone can guide me.
Ref: I am running Oracle 11.2 on Linux 5.7.
===========================
Here is my table:
SQL> desc ntwkrep.CARD
Name                                                              Null?    Type
----------------------------------------------------------------- -------- ------------------
CIM_DESCRIPTION                                                            VARCHAR2(255)
CIM_NAME                                                          NOT NULL VARCHAR2(255)
COMPOSEDOF                                                                 VARCHAR2(4000)
DESCRIPTION                                                                VARCHAR2(4000)
DISPLAYNAME                                                       NOT NULL VARCHAR2(255)
LOCATION                                                                   VARCHAR2(4000)
PARTOF                                                                     VARCHAR2(255)
*REALIZES                                                                   VARCHAR2(4000)*
SERIALNUMBER                                                               VARCHAR2(255)
SYSTEMNAME                                                        NOT NULL VARCHAR2(255)
TYPE                                                                       VARCHAR2(255)
STATUS                                                                     VARCHAR2(255)
LASTMODIFIED                                                               DATEWhen I try to load the data from a text file using sqlldr, I get the following errors on some records which do not get loaded.
Example:
=======
Record 1: Rejected - Error on table NTWKREP.CARD, column REALIZES.
Field in data file exceeds maximum length
Looking at the actual data and counting the characters for the "REALIZES" column data, I see that it is roughly slightly over 1000 characters.
So, attempting various ideas to fix the problem, I tried changing nls_length_semantics to "char" and recreating the table, but this still didn't help and still got the same data load errors on the same rows.
Then, I changed nls_length_semantics back to byte and recreated the table again.
This time, I altered the table manually as:
SQL> ALTER TABLE ntwkrep.CARD MODIFY (REALIZES VARCHAR2(4000 char));
Table altered.
SQL> desc ntwkrep.card
Name                                                              Null?    Type
----------------------------------------------------------------- -------- --------------------------------------------
CIM_DESCRIPTION                                                            VARCHAR2(255)
CIM_NAME                                                          NOT NULL VARCHAR2(255)
COMPOSEDOF                                                                 VARCHAR2(4000)
DESCRIPTION                                                                VARCHAR2(4000)
DISPLAYNAME                                                       NOT NULL VARCHAR2(255)
LOCATION                                                                   VARCHAR2(4000)
PARTOF                                                                     VARCHAR2(255)
REALIZES                                                                   VARCHAR2(4000 CHAR)
SERIALNUMBER                                                               VARCHAR2(255)
SYSTEMNAME                                                        NOT NULL VARCHAR2(255)
TYPE                                                                       VARCHAR2(255)
STATUS                                                                     VARCHAR2(255)
LASTMODIFIED                                                               DATEAgain, the data load failed with the same errors on the same rows.
So, this time, I thought I would try changing the column data type to a clob (securefile), and AGAIN, it still failed to load on the same rows.
SQL> desc ntwkrep.CARD
Name                                                              Null?    Type
----------------------------------------------------------------- -------- -----------------------
CIM_DESCRIPTION                                                            VARCHAR2(255)
CIM_NAME                                                          NOT NULL VARCHAR2(255)
COMPOSEDOF                                                                 VARCHAR2(4000)
DESCRIPTION                                                                VARCHAR2(4000)
DISPLAYNAME                                                       NOT NULL VARCHAR2(255)
LOCATION                                                                   VARCHAR2(4000)
PARTOF                                                                     VARCHAR2(255)
REALIZES                                                                   CLOB
SERIALNUMBER                                                               VARCHAR2(255)
SYSTEMNAME                                                        NOT NULL VARCHAR2(255)
TYPE                                                                       VARCHAR2(255)
STATUS                                                                     VARCHAR2(255)
LASTMODIFIED                                                               DATEAny ideas?
Here is a copy of the first row of data which fails to load every time no matter how I change the "REALIZES" column in the table.
other(1)`CARD-mes-fhnb-bldg-137/1`  `other(1)`CARD-mes-fhnb-bldg-137/1 [other(1)]`HwVersion:C0|SwVersion:12.2(40)SE|Serial#:FOC1302U2S6|` Chassis::CHASSIS-mes-fhnb-bldg-137, Switch::mes-fhnb-bldg-137 ` Port::PORT-mes-fhnb-bldg-137/1.23, Port::PORT-mes-fhnb-bldg-137/1.21, Port::PORT-mes-fhnb-bldg-137/1.5, Port::PORT-mes-fhnb-bldg-137/1.7, Port::PORT-mes-fhnb-bldg-137/1.14, Port::PORT-mes-fhnb-bldg-137/1.12, Port::PORT-mes-fhnb-bldg-137/1.6, Port::PORT-mes-fhnb-bldg-137/1.4, Port::PORT-mes-fhnb-bldg-137/1.20, Port::PORT-mes-fhnb-bldg-137/1.22, Port::PORT-mes-fhnb-bldg-137/1.15, Port::PORT-mes-fhnb-bldg-137/1.13, Port::PORT-mes-fhnb-bldg-137/1.18, Port::PORT-mes-fhnb-bldg-137/1.24, Port::PORT-mes-fhnb-bldg-137/1.26, Port::PORT-mes-fhnb-bldg-137/1.17, Port::PORT-mes-fhnb-bldg-137/1.11, Port::PORT-mes-fhnb-bldg-137/1.2, Port::PORT-mes-fhnb-bldg-137/1.8, Port::PORT-mes-fhnb-bldg-137/1.10, Port::PORT-mes-fhnb-bldg-137/1.16, Port::PORT-mes-fhnb-bldg-137/1.9, Port::PORT-mes-fhnb-bldg-137/1.3, Port::PORT-mes-fhnb-bldg-137/1.1, Port::PORT-mes-fhnb-bldg-137/1.19, Port::PORT-mes-fhnb-bldg-137/1.25 `Serial#:FOC1302U2S6`mes-fhnb-bldg-137`other(1)Finally, for reference, here is the controlfile that i am using.
load data
infile '/opt/EMC/data/out/Card.txt'
badfile '/dbadmin/data_loads/logs/Card.bad'
append
into table ntwkrep.CARD
fields terminated by "`"
TRAILING NULLCOLS
(
CIM_DESCRIPTION,
CIM_NAME,
COMPOSEDOF,
DESCRIPTION,
DISPLAYNAME,
LOCATION,
PARTOF,
REALIZES,
SERIALNUMBER,
SYSTEMNAME,
TYPE,
STATUS,
LASTMODIFIED "sysdate"
) 
does every row throw this error?
might you be mistaken about the actual length of the real data? 
The default datatype in sqlldr is char(255)
Amend your control file with the following which I think should work with REALIZES VARCHAR2(4000):
COMPOSEDOF char(4000),
DESCRIPTION char(4000),
LOCATION char(4000),
REALIZES char(4000), 
No, every row does not fail. Only the ones with longer data lengths.
Its very strange... I use vi editor and also checked for hidden characters (set list), but it didn't have any hidden characters either. 
Thank you Freddie. That worked.
That seems counter-intuitive to me.
If varchar2 fields can hold up to 4000 characters, when why isn't that the default for sql loader?
Good grief.! Well, thank you very much for your help.
I don't use sqlldr as much as I used to, and over the years, this has become something the developers usually provided for me.
Thanks again. 
I've had the same issue in the past and yes..I understand your frustration having messed about with different data types.
Why is the default 255? I don't know but I would guess there is some performance reason behind it.

SQL  Workshop bug?

SQL*PLUS in Oracle Server 9014 allows me to create the following table. The SQL Workshop's SQL Command Processor objects to the same DDL with error ORA-911 illegal character.
What's up with that? The DDL contains reserved words (FUNCTION, MODULE, TYPE) but that should be a red herring. In fact, it is. If I rename all the columns to avoid the collision, the same error results.
SQL> CREATE TABLE trms_master(
2 project varchar2(15) not null,
3 trms_no number(15) not null,
4 function varchar2(15) not null,
5 module varchar2(15) null,
6 source varchar2(1) not null,
7 date_entered date not null,
8 status varchar2(1) not null,
9 entered_by varchar2(15) not null,
10 original_severity varchar2(1) not null,
11 severity varchar2(1) not null,
12 duplicate_of varchar2(15) null,
13 key_description varchar2(80) null,
14 attachment_no number(15) null,
15 type varchar2(1) not null,
16 modified_by varchar2(15) null,
17 modified_on date null
18 )
19 /
Table created.
SQL> desc trms_master;
Name Null? Type
------------------------------- -------- ----
PROJECT NOT NULL VARCHAR2(15)
TRMS_NO NOT NULL NUMBER(15)
FUNCTION NOT NULL VARCHAR2(15)
MODULE VARCHAR2(15)
SOURCE NOT NULL VARCHAR2(1)
DATE_ENTERED NOT NULL DATE
STATUS NOT NULL VARCHAR2(1)
ENTERED_BY NOT NULL VARCHAR2(15)
ORIGINAL_SEVERITY NOT NULL VARCHAR2(1)
SEVERITY NOT NULL VARCHAR2(1)
DUPLICATE_OF VARCHAR2(15)
KEY_DESCRIPTION VARCHAR2(80)
ATTACHMENT_NO NUMBER(15)
TYPE NOT NULL VARCHAR2(1)
MODIFIED_BY VARCHAR2(15)
MODIFIED_ON DATE 
you appear to have stumbled into a bug in our sqlWorkshop feature of HTML DB. the issue is that we're getting hung up on your column named "function". i'll go log the bug in just a bit, but for now you can get your create table command to work in either of two ways:
1) create your table as a script by uploading your ddl as a sqlWorkshop script and then running it.
2) change the name of your function column to not include the exact string "function". the string "funct" works fine, for instance.
i'd recommend the first approach above because you should really store your DDL as scripts anyway. that way it's a much cleaner process to migrate your app elsewhere.
hope this helps,
raj 
Raj,
When you get into this further I think you'll find that the column name FUNCTION is not the real issue. I renamed that column along with 2 others that happened to be reserved words and I got the same error.
By the way I was able to create the column FUNCTION using the scratch method. So Oracle Server itself is not objecting to that use of a reserved word.
I created the tables by "scratch" but then ran into the primary key issue that I emailed you separately.
Thanks,
Al 
hey al--
     i'm stickin' to my story on this one. just to be clear, here's what i did:
1) took your ddl from your forum posting above
2) srtripped out the line numbers
3) changed your "function" column name to "funct"
4) dropped the modified ddl into the SQL Command Processor of our sqlWorkshop
5) clicked "run sql"
things worked fine. as you originally said, the db itself is fine with the use of those reserve words for column names (though i'd say you're askin' for trouble), but i was trying to say that HTML DB gets hung up in the SQL Command Processor specifically on the string "function". i've already logged this bug, so i'd imagine it'll get fixed quite soon.
regards,
raj
ps-i'm off to reply to your email now. 
Actually got a similar issue. I could not create a view from a table with a column name of functionid. Seems weird, but by renaming the column as domainid, i was able to create the view. 
thanks, alain. this is pretty much the same bug, but i'm off to make a specific note of it.
regards,
raj

Categories

Resources