10g - Some VARCHAR2 Fields Empty?
Jul 21, 2011
Once a year in the application we have a specific query that gets used a lot. It's an UPDATE that updates a single record in a single table with a few different datatypes, but the issue is happening with one of the VARCHAR2 fields. It updates one VARCHAR2(2000) and three VARCHAR2(4000) fields at the same time.
This year, 9 of the 95 times it was used resulted in one of the VARCHAR2(4000) fields as null in the database. The users would not want this field to be null and 5 of the 9 have told us they entered something (the form they're filling out is a research proposal and leaving this field empty would be pointless because it's part of the funding request, so they're not doing it). The application isn't doing it because it's not consistent. I've checked the application and these fields can't be nulled any other way.
We just found the issue so I looked back over the past years back to 2005. Last year it didn't happen at all. In 2010 it happened a handful of times. Some years there were even more times. It's not always the same field but it's always a VARCHAR2 of at least 2000 characters.
I have a lot more information but it's all just details (let me know if you need to know more). I'm wondering if there is a bug in 10g with these types of fields. I don't believe it's malicious behavior on an individual's part but I suppose that's always possible.
how to research something like this. I tried to get access to Oracle Support and the Knowledge Base I heard they have but it doesn't look like I can do that
View 2 Replies
ADVERTISEMENT
Nov 10, 2010
I did a search on this topic and did see the ASK TOM response that storing all varchar2 fields as (2000) or what not is a bad idea based on an array fetch that developers may use etc. However I'm not sure that applies to my specific question, and the other examples he gave certainly didn't apply. So I'll pose the question a different way:
Question #1: Is there, for example, a performance difference between setting a field as varchar(2000) and varchar(25) if I was just running a native SQL query using a front end tool like TOAD?
Question #2: If I also need to index that field, will it take longer to index a varchar(2000) field than a varchar(25) field, assuming the same data is in both fields?
View 2 Replies
View Related
Oct 24, 2007
i am fairly new in the oracle arena, but what would cause a statement such as
ALTER TABLE TEST_TABLE MODIFY text_field1 varchar2(100) DEFAULT 'testval' NULL
to change a column's type from VARCHAR2(100) to VARCHAR2(100 byte)? i found a few mentions of the 100 byte concept online but nothing that jumped out at me.
View 2 Replies
View Related
Mar 23, 2009
I have a query that will either return one record or zero records. When it returns zero records I want to replace my attributes with a sentinel, like 'N/A'. I tried the CASE statement but couldn't get anything to work
Sample (does not work):
select
(case when exists (select product from tbl_product where productid = '123') then product else 'N/A' end) product
from tbl_product
where productid= '123';
If one record exists it should produce: 'My Widget' (or whatever)
If zero records exist it should produce: 'N/A'
View 3 Replies
View Related
May 10, 2011
While I was exporting ( schema level ) ,I am getting following errors :
vaughn$ exp parfile=qar808par.txt
Export: Release 11.1.0.7.0 - Production on Tue May 10 10:52:34 2011
Copyright © 1982, 2007, Oracle. All rights reserved.
Username: system
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
Export done in WE8ISO8859P9 character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
About to export specified users .
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user TOXFIRE_TO_TSFDA
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user TOXFIRE_TO_TSFDA
About to export TOXFIRE_TO_TSFDA's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
EXP-00056: ORACLE error 25153 encountered
ORA-25153: Temporary Tablespace is Empty
EXP-00000: Export terminated unsuccessfully
'Parfile' contents :
owner=TOXFIRE_TO_TSFDA
file=exp_TOXFIRE_TO_TSFDA_QAR808.dmp
consistent=y
direct=y
recordlength=65535
log=exp_TOXFIRE_TO_TSFDA_QAR808.log
What is the reason, and It is saying that temporary tablespace is empty ( temp tablespace have almost free space ) why it is saying like that? And users default temp tablespace is that tablespace only ( database have only on temp tablespace
View 1 Replies
View Related
May 29, 2013
While i am inserting data into a specific table that table assigns empty spaces.
For example:
CREATE TABLE DEALER_MST
("DEALER_CODE" CHAR(10) NOT NULL ENABLE,
"REGION_CODE" CHAR(3),
"SUB_REGION_CODE" CHAR(3),
"DEALER_NAME" VARCHAR2(80),
[Code]....
But it is giving result as DS with 8 spaces. It assigns spaces in db automatically while fetching.
View 8 Replies
View Related
Jan 13, 2013
I used a dynamic item_list. This list should contain only the result of the query but I found an empty element in the list. So, how to eliminate this element empty list?
see the picture:
[URL]........
View 4 Replies
View Related
Jul 3, 2010
there this is my insert loop but the problem is there is row added more than the normal count ..
DECLARE
var_record_count NUMBER;
var_p_request NUMBER;
[Code]....
View 4 Replies
View Related
Jul 25, 2011
There is one table with data in ORCL1 database.I have created the table using create table statement in ORCL2 database.Now i want to insert only the data into table.
I know one method ... drop the table i created using drop table statement and then create the table with data using export/import.
Is there any other way we can load data into empty table?
View 29 Replies
View Related
Aug 3, 2010
I have a table with thirty column for attendance like Day_A01,Day_A02......Day_A31.
I need a query that should return the column which is empty but its next and previous column is not empty.
View 13 Replies
View Related
May 20, 2010
How can i check if the result of a defined cursor is empty?
View 6 Replies
View Related
Dec 20, 2010
My requirement is that under table name like A I have to update remaining empty columns as A and under B remaining null columns as B and so on.
View 14 Replies
View Related
Oct 9, 2013
My db version: Oracle 11g I have an empty csv file.I created a external table for the empty csv file.When I run:select count(*) from externaltblname;It returns 1. It should return 0 right. In the definition, I specified "SKIP 1"But still it returns 1. When I use this external table to load into a target table. It loads a single row with null values.How to fix this.
View 2 Replies
View Related
Jan 4, 2011
what empty blocks are, and how to remove them.What I'd like to do is not have empty blocks in the first place on loading a table. I load a lot of "static" tables and would like to not have any wasted space at the end, with minimal shinanigans.
I've set pctfree 0
I"ve set initial to close to the end table size
I've set next to 1M
I've set pctincrease 0
blocksize is 8k
Yet I still need to at least do an alter table deallocate unused
View 8 Replies
View Related
Mar 13, 2009
i have project for hotel , i can't solve query to search empty room between date . i have table for room , when room record under reservation between date . then i want to found empty room between date, which is query to make do.
Table Room
Room_No
Type_Room
From_Date
To_Date
Status
Now i need to found empty room between date search room empty between
11/03/2009 - 13/03/2009
select Room_No from Room where From_Date Not between '11/03/2009' and '13/03/2009' i found hard to query.
View 3 Replies
View Related
Apr 14, 2010
I have text file with data in UNIX. I want to clear only records(make empty the file) using UTL_FILE..
I know we can remove the text file from UNIX and create file again with same name...
Is there any other way??
View 5 Replies
View Related
Sep 24, 2010
I am select list of ids;
cursor c1 is
select c_id from ctab1 where login = l_id
union
select c_id from ctab2 where login = i_id;
now I need to return err_code and err_message if this combined list is empty ! Can not use exit%notfound.
View 5 Replies
View Related
Mar 31, 2012
①SQL> SELECT OBJECT_ID FROM DBA_OBJECTS WHERE OBJECT_NAME='T2012';
OBJECT_ID
---------
57082
②SQL> SELECT HEADER_BLOCK,BLOCKS FROM DBA_SEGMENTS WHERE SEGMENT_NAME = 'T2012';
HEADER_BLOCK BLOCKS
------------- --------
683 8
③SQL> SELECT DBMS_ROWID.rowid_block_number(ROWID)USED_BLOCK_NUMBER FROM SCOTT.T2012;
USED_BLOCK_NUMBER
----------------
684
④SQL> SHUTDOWN IMMEDIATE;
⑤SQL> STARTUP;
⑥SQL> SELECT BLOCK#,CLASS# FROM V$BH WHERE OBJD = '57082';
no data found
⑦SQL> SELECT * FROM SCOTT.T2012;
ID
-----
1
⑧SQL> SELECT BLOCK#,CLASS# FROM V$BH WHERE OBJD='57082';
BLOCK# CLASS#
------- ----------
686 1
684 1
687 1
685 1
688 1
683 4
⑨SQL> SELECT EMPTY_BLOCKS FROM DBA_TABLES WHERE TABLE_NAME='T2012';
EMPTY_BLOCKS
------------
3
QUESTIONS ONE:
in the ⑧ step,why block#685,block#686,block#687,block#688 in the buffer cache after i query data from scott.T2012?
QUESTIONS TWO:
in the ⑨ step,what's the block number of the empty block?just like DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID).
View 2 Replies
View Related
Jun 29, 2012
When I try to run the form by clicking Program/Run form, it opens a webpage(explorer) and displays ?ORACLE FORMS? label, but does not show the form only blank screen appear. When I try to run the form by clicking Program/Run form, it opens a webpage(Firefox), but does not show the form only blank screen appear.
I have noticed that in Start OC4J Instance it has some thing like as below:
SYSDATE TIME FormsServlet init():
configFileName: E:DevSuiteHome_2/forms/server/formsweb.cfg
testMode: false
pop-ups is not blocked in my browser I have allow active content to run in files on your computer.directory name (where the form is stored) also doesn't contain any spaces.
Also I have change the
baseHTMLjinitiator=basejini.htm
to this
baseHTMLjinitiator=basejpi.htm.
I have done all the things mention in this forum to overcome this error.But, still the same problem is there.Also please take a note that I am using JRE not JINITIATOR. Beacuse when I am using the JINITIATOR it give me the problem of browser crash in both IE and firefox.Also I am using Windows XP and oracle form 10g.
View 9 Replies
View Related
Aug 8, 2010
I was wondering can i check if a text item in another block is empty when a user try to create a record using a button in another block.
/*creating a program that will check to see if the customer id in the customer block is null it will then display the the stop alert and tell the user that he/she cant create a new order without having a selected a customer*/
--this is the block_item im checking to see if it null
IF (:customer.c_id) = NULL THEN
DISPLAY_ORD_STOP_ALERT;
GO_ITEM('CUSTOMER.C_ID');
[code]....
View 5 Replies
View Related
Jun 22, 2010
I need to send email notifications with attachments. Below is my testing codes.
declare
text varchar2(32000);
crlf VARCHAR2(2) := chr(13)||chr(10);
begin
text := 'Test only'||crlf||'test 2'||crlf||'test 3';
UTL_MAIL.SEND_ATTACH_VARCHAR2(
[code]......
I notice that, there is an empty line at the top of the content of this file. I don't want the empty line to be included in my file.
View 4 Replies
View Related
Oct 15, 2012
db 10.2.0.4
AIX 5.2
I am trying to drop a index tablespace I moved all indexes to new tablespace there is no indexes in that tablespace even any objects but still it is not dropping.it is giving error as below.
SQL> drop tablespace ret_index including contents;
drop tablespace ret_index including contents
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-02429: cannot drop index used for enforcement of unique/primary key
SQL> alter tablespace ret_index drop datafile '/path/index3.dbf';
alter tablespace ret_index drop datafile '/path/ret_index3.dbf'
*
ERROR at line 1:
ORA-03262: the file is non-empty
try to resize
SQL> alter database datafile 4 resize 100m;
alter database datafile 4 resize 100m
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
Tablespace size is 280gb
View 32 Replies
View Related
Feb 21, 2011
I have a column named "col1" with datatype "varchar2(10)" and row wise entries like "1,1A, 2,3...,10,2A,..." like. I want to order it like "1, 1A ,2,2A, 2B,3... 10...".I tried it with to_number() but it gives me
1,10,11,2,....like that.
View 14 Replies
View Related
Jun 13, 2012
What is the best alternative to using VARCHAR2 if the output gets truncated after 4000 bytes in a PL/SQL function?
View 10 Replies
View Related
Mar 23, 2010
in our application we are using clob column instead of varchar2 because varchar2 does not allow more that 4000 chars, so Using clob allows to put data of any length, will it cause performance issues ? we have this column in almost in all tables .
View 2 Replies
View Related
Sep 28, 2010
create table members(
memberID varchar2(7),
locationID number(5),
roomID varchar2(5)
)
/
[Code]....
MEMBERI LOCATIONID ROOMI
------- ---------- -----
2007209 1 1
2006253 1 1-A
2006253 1 1-A
2006265 1 1-B
2006240 1 1-C
2005064 1 1-D
2007836 1 100
2007211 1 101
2007075 1 102
2007110 1 105
[Code]...
Function created.
SQL> select * from members
2 order by safe_to_number(roomID);
MEMBERI LOCATIONID ROOMI
------- ---------- -----
2007209 1 1
2007185 1 2
2007089 1 3
2007023 1 4
[Code]....
How can I sort the output to get the result like below
2007209 1 1
2006253 1 1-A
2006253 1 1-A
[Code]...
and so on...
View 13 Replies
View Related
May 30, 2011
how to get rid of the first EMPTY line generated by SQL*Plus when spool'ing to a file.
I tried using set pagesize 0 and while that get rid of the empty blank line, it also disabled the heading ...
Currently using sed as a work around. But unfortunately, can't use sed on Windows?
set pagesize 200
set heading on
set trimspool on
set lines 200
set verify off
set echo off
set feedback off
[code].......
As mentioned, using pagesize 0 remove the blank line but I also lost the heading.
View 4 Replies
View Related
May 7, 2012
I have successfully cleaned up our test environment but am having problems with the development environment.
We have a master table, a single materialized view log on that table, and 85 materialized views based on them.
Following is the output from my 'refresh_all_mviews' and query on 'deferror':08:36:04 SQL> SET serverout ON
DECLARE
v_failures NUMBER(12) := 0;
begin
dbms_mview.refresh_all_mviews(v_failures,'F','', refresh_after_errors => true, atomic_refresh => false);
dbms_output.put_line('Failures='||v_failures);
END;
/
08:36:33 SQL> 08:36:33 2 08:36:33 3 08:36:33 4 08:36:33 5 08:36:33 6 08:36:33 7 08
:36:33 8
Failures=5
PL/SQL procedure successfully completed.
08:36:47 SQL> 08:36:47 SQL> select * from deferror;
no rows selected
08:36:56 SQL>
why no rows are being selected? Do I have to 'set' something 'on'?
View 2 Replies
View Related
Sep 16, 2011
I am getting an empty line after every 988 records in the output. There are no pagebreaks defined in the layout.
This is a "|" delimited report.
View 2 Replies
View Related
Aug 5, 2010
Is there a way to get around the "cannot insert NULL" error when inserting a row with an empty string.
For example:
create table t (
col1 varchar2( 8 ),
col2 varchar2( 8 ),
...,
PRIMARY KEY (col1, col2)
);
insert into t (col1, col2, ...) values ('Hi', '', ...);
View 13 Replies
View Related
