SQL & PL/SQL :: NULL Index Table Key Value
Sep 26, 2010
We are getting the below error on a piece of code that chas been functioning fine for over 4 years..
ORA-06502: PL/SQL: numeric or value error: NULL index table key value
ORA-06512: at "pd.pf33", line 148
The line it's pointing at is merely a FOR loop that opens a cursor (select query) and loops through the items.
FOR rec IN csr_cash
LOOP
END LOOP
This piece of code has been functioning fine for years.. and never got this kind of error on a for loop. Could it be something internal to Oracle/underlying memory issues?
View 8 Replies
ADVERTISEMENT
Aug 12, 2013
so im running this script
DECLARE
STQ_ID NUMBER;
MESSAGE VARCHAR2(500);
[Code].....
and getting this error ORA-06502: PL/SQL: numeric or value error: NULL index table key value
the code where it is inserting and getting out of it values is:
IF vt_Prs_Code_Ipr.COUNT <> 0 THEN
IF vt_Ist_Code_Ipr(1) IS NOT NULL THEN
IF vr_data.insurance_kind = 'B' THEN
[Code]....
Im running script at PL/SQL Developer and using debugger, i see that it's inserting dates but at some point between point 5.3.2.1.1 till 5.3.2.1.5 it returns no dates :/ basically it needs to stop at 5.3.2.1.2 and in form need return date 02.02.1996, but it returns 01.01.1996 + gives that ORA-06502: PL/SQL: numeric or value error: NULL index table key value
edit: vt_SnpStartDate(v_Idx)is 01.01.1996
vt_SnpEndDate(v_Idx) is 31.12.1996
vt_prd_tbl(v_Idx2).Start_Date is 01.01.1996
vt_prd_tbl(v_Idx2).End_Date is 01.02.1996
at p.5.3.2.1.3 it shows that there is no date in vt_prd_tbl(v_Idx2).Start_Date and is returning ora error, why?
View 10 Replies
View Related
Oct 31, 2012
I have a table with column A which contains very few null values. I need to select these rows. I am considering two options:
a) create function based index on NVL(A, 0) and use this in where clause NVL(A, 0)=0 (column doesn't have values 0)
b) create function based index on NVL2(A, 0, NULL) and and use this in where clause NVL2(A, 0, NULL) = 0
First idea was option A. But I realized in option B the index will be much smaller, because most of values of column A isn't NULL so NVL2 will return NULL and index will not have as much leafs as in NVL. It is good idea to use NVL2? Is there any against to use option B instead of A?
View 1 Replies
View Related
Aug 23, 2013
The below query is not performing good.
SELECT * FROM wedb_master
WHERE c_group IS NOT NULL;
The table is having 200000 records.
I have index on c_group but not used. FTS is happening.
how improve the performance of IS NOT NULL predicate.
View 2 Replies
View Related
Oct 5, 2013
Let's consider such table that all rows fit into single block:
SQL> create table test as select rownum id, '$'||rownum name from dual connect by level <= 530;
Table created.
SQL> create index i_test on test(id);
Index created.
SQL>
SQL> begin
[code].....
why does approach with full scan take longer even if table occupies only one data block? PS. 11gR2
View 8 Replies
View Related
Jun 28, 2011
I have a huge table (about 60 gb) partition over range. The index on this table is global index created on 4 columns together. I have a query which is running very slowly. The explain plan is showing the use of this global index.Explain plan is not showing pstart and pend because the index is global.
View 6 Replies
View Related
Aug 9, 2013
How to force an index if the table not using the index?
View 10 Replies
View Related
Aug 29, 2012
I have the following query:
select col_1,col_9 from
book_temp b
where b.col_1 is not null
order by to_number(b.col_16)
;
What I want to add is the following:
COL_9
=====
NULL
A
B
NULL
C
D
E
F
NULL
G
I need to connect the NON-NULL rows to the preceding NULL row.
View 15 Replies
View Related
Jan 5, 2011
Let me know in PL/SQL how can we set a Table Type record to NULL?
Also, if .delete makes it NULL?
View 1 Replies
View Related
Feb 7, 2012
We deleted millions of records from a table.
1.Is it necessary to reorganize a table and index after the deletion of records from table ? Because i see some change in table size after table and index reorganization.
2.Will re org table and index improve the database performance ?
View 7 Replies
View Related
Jun 21, 2010
I am running a GROUP BY query on a few columns of enumerated data like:
select count(*), Condition, Size
group by Condition, Size;
COUNT(*) CONDITION SIZE
-------- ---------- --------
3 MINT L
2 FAIR L
4 FAIR M
1 MINT S
Well, let's say I also have a timestamp field in the database. I cannot run a group by with that involved because the time is recorded to the milisec and is unique for every record. Instead, I want to include this in my group by function based on whether or not it is NULL.
For example:
COUNT(*) CONDITION SIZE SOLDDATE
-------- ---------- -------- ----------
3 MINT L ISNULL
2 FAIR L NOTNULL
2 FAIR M NOTNULL
2 FAIR M ISNULL
1 MINT S ISNULL
View 9 Replies
View Related
Mar 16, 2011
I have a table which has a not null column. the column is date field. I am trying to change it to Null. But it is giving a error.
I am using below query.
ALTER TABLE T_test
modify (paid_to_date null)
View 9 Replies
View Related
May 17, 2012
how can I reduce the size of ------------- when table is null. I m in sqlplus I typeSelect A,B,C,D,F,G,H from SOMEHERE where B='GOAT1';
if A is 10 char long
B is 50
c is 10
d is 30
e is 10
f is 50
if any of those don't have data it still outputs ----------------------------- (50) for B and tht covers the whole screenhow can I make is to show less if it null
View 2 Replies
View Related
Feb 20, 2013
I have a data from the source table like this :
IdDate1 No1 Date2 No2
12/15/19959292 8/30/1997 11767
25/21/19932812 4/29/2002 2812
39/22/1978
410/11/19781157381 2/8/1979 929137
58/2/19891698430 11/30/1989 1365528
6
711/20/199541 1/23/1997 677
811/20/1995 1/23/1997 680
I want to insert into two separate tables using the following logic :
If date1 is not null or no1 is not null then insert into target_table1(id,date1,no1)
If date2 is not null or no2 is not null then insert into target_table2(id,date2,no2)
View 4 Replies
View Related
May 19, 2011
Why can't we create null constraint on table level?
View 20 Replies
View Related
Aug 8, 2013
Can i use a trim(col_name) to check a NULL value in a table? as IS NULL function is taking a long time.
View 8 Replies
View Related
Jul 5, 2012
How to insert null record (for some columns) in table using loop.
sample data of x_tab
order_id order_name
231 xxx
123
345
111 vvvv
View 5 Replies
View Related
Jul 17, 2012
I have few questions regarding PL/SQL tables
1) If I don't specify the "INDEX BY" clause, it is indexed by PLS_INTEGER by default, right?
2) Consider this package specification
CREATE OR REPLACE PACKAGE Testxyz AS
TYPE tab_Numbers IS TABLE OF PLS_INTEGER;
PROCEDURE TestNumber(ptab_Numbers IN tab_Numbers := NULL);
END Testxyz;So I created a table (I hope it is defaultly indexed by pls_integer data type) and I am passing it as a parameter to a procedure. Because I want this parameter to be optional I am assigning null into it.
Now I change the definition of the table to:
CREATE OR REPLACE PACKAGE Testxyz AS
TYPE tab_Numbers IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
PROCEDURE TestNumber(ptab_Numbers IN tab_Numbers := NULL);
[code]....
3) Because I need this parameter to be optional, I use the first declaration:
CREATE OR REPLACE PACKAGE Testxyz AS
TYPE tab_Numbers IS TABLE OF PLS_INTEGER;
PROCEDURE TestNumber(ptab_Numbers IN tab_Numbers := NULL);
END Testxyz;
Now I create an anonymous block and want to assign something into the table
DECLARE
vtab_Numbers TESTXYZ.tab_Numbers;
BEGIN
vtab_Numbers(1) := 5;
END;
/When trying to run this, I got:
06531. 00000 - "Reference to uninitialized collection"
Is it possible to have input parameter of PL/SQL table type and have defaultly null assigned to it?
View 5 Replies
View Related
Feb 3, 2012
when i am Executing the following statement
SELECT DISTINCT EXPOSURE_REF FROM KBNAS.VW_EXPOSUREDETS_FOR_CCYREVAL
WHERE EXPOSURE_CURRENCY='THB' AND BASE_TXN_CCY='USD' AND BRANCH_CODE='7000'
AND (REVAL_STATUS='O') AND CONV_RATE<>'62' AND (EXPOSURE_AMOUNT<>0)
UNION
SELECT DISTINCT ED.EXPOSURE_REF FROM KBNAS.EXPOSURE_DETAILS ED,
[code].....
I have attached DDL for table EXPOSURE_DETAIL(PARTITION),LEDGERCARD,LEDGERCARDDETAILS, DDL for INDEX on those tables and DDL for Views..
Issue: we have created the Indexes but when we check the explain plain .. full table scan is going on..I have attached the explain plan ..
View 11 Replies
View Related
Jun 27, 2013
We need to load data from index by table to table.Below code is working fine.
declare
query varchar2(200);
Type l_emp is TABLE OF emp%rowtype INDEX BY Binary_Integer;
rec_1 l_emp;
begin
[Code]....
But data from source table and target table is dynamic.Ex:In above code, emp(source) and target table is emp_b are static. But for our scenario is depends on the source table , target would change as below.If source is emp then target is emp_bIf source is emp1 then target is emp_b1 ............
create or replace procedure p(source in varchar2, target in varchar2)
as
query varchar2(200);
source varchar2(200);
Type l_emp is TABLE OF emp%rowtype INDEX BY Binary_Integer;
rec_1 l_emp;
[Code]....
Its throwing. How to implement this scenario .
View 2 Replies
View Related
Oct 24, 2013
create table test
(
id int ,
dat date
)
/
I want to implement a business rule such as we have for each id at most 1 dat null. So, I've created this unique index on test.
create unique index x_only_one_dat_cess_null on test(id, case when dat_cess is null then 'NULL' else to_char(dat_cess, 'dd/mm/yyyy') end);
insert into test values (1, sysdate);
insert into test values (1, sysdate - 1);
insert into test values (1, null);
insert into test values (1, null);
-- -----
insert into test values (2, sysdate);
insert into test values (2, sysdate - 1);
insert into test values (2, null);
The 4th insert will cause an error and this is what I wanted to implement. OK. Now the problem is that for non-null values of dat, we can't have data like this
iddat
------------
124/10/2013
123/10/2013
123/10/2013
1
because of the unique index (the 2nd and the 3rd row are equal). So just for learning purposes, how could we allow at most one null value of dat and allow duplicates for non-null values of dat.
View 2 Replies
View Related
Jan 9, 2012
when i follow this steps mention on this website
[URL].........
to modify column from null to not null i got this error and on this website its show successful
my steps are
first i create a table
SQL> create table Stu_Table(Stu_Id varchar(2), Stu_Name varchar(10),
2 Stu_Class varchar(10));
Table created.
Then insert some rows into Stu_Table
SQL> insert into Stu_Table (Stu_Id, Stu_Name) values(1,'Komal');
1 row created.
SQL> insert into Stu_Table (Stu_Id, Stu_Name) values(2,'Ajay');
1 row created.
SQL> insert into Stu_Table (Stu_Id, Stu_Name) values(3,'Rakesh');
1 row created.
SQL> insert into Stu_Table (Stu_Id, Stu_Name) values(4,'Bhanu');
1 row created.
SQL> insert into Stu_Table (Stu_Id, Stu_Name) values(5,'Santosh');
1 row created.
SQL> select * from Stu_Table;
ST STU_NAME STU_CLASS
-- ---------- ----------
1 Komal
2 Ajay
3 Rakesh
4 Bhanu
5 Santosh
Table Structure is like this
SQL> Describe Stu_Table
Name Null? Type
----------------------------------------- -------- ----------------------------
STU_ID VARCHAR2(2)
STU_NAME VARCHAR2(10)
STU_CLASS VARCHAR2(10)
now when i try to modify this Stu_id column to not null its give me error.
SQL>ALTER TABLE Stu_Table MODIFY Stu_Id int(3)not null;
ALTER TABLE Stu_Table MODIFY Stu_Id int(3)not null
*
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option
and when i try to add new column with not null its also gives me error
SQL> ALTER TABLE Stu_Table add C1_TEMP integer NOT NULL;
ALTER TABLE Stu_Table add C1_TEMP integer NOT NULL
*
ERROR at line 1:
ORA-01758: table must be empty to add mandatory (NOT NULL) column
View 6 Replies
View Related
Feb 10, 2012
Am pasting the sample code here, which i got from some site..
DECLARE
TYPE population_type IS TABLE OF NUMBER INDEX BY VARCHAR2(64);
country_population population_type;
continent_population population_type;
howmany NUMBER;
[code]...
Here we are fetching indexes (like Antartica/Australia) from these two statements continent_population.FIRST or continent_population.LAST. If there are three or more indexes in the same table, how to fetch all of them?
I have tried using this, but doesnt work because loop variables are by default integers:
for i in continent_population.FIRST .. continent_population.LAST loop
dbms_output.put_line('i:'||i);
end loop;
View 2 Replies
View Related
Jul 12, 2012
I have 8 columns. Some of them might be null.I want to display all 8 columns in my result. Not null columns will be first and null at the end.Here is a sample data :
Employee table :
Employee_id Emp_fname emp_lname emp_mname dept salary emp_height emp_weight
1 aaa ddd d1 100 6 180
2 bbb ccc 120 169
3 dfe d2 5.9 223
The expected result is :
result1 result2 result3 result4 result5 result6 result7 result8
1 aaa ddd d1 100 6 180
2 bbb ccc 120 169
3 dfe d2 5.9 223
View 8 Replies
View Related
Oct 24, 2011
how to partition and index my table for a special problem.
The table:
CREATE TABLE TEST (
ID NUMBER PRIMARY KEY,
U_VALUE NUMBER NOT NULL, -- Ranges from 0 - 30.000.000
O_VALUE NUMBER NOT NULL, -- Ranges from U_VALUE - 30.000.000
CREATE_TS TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
UPDATE_TS TIMESTAMP NOT NULL,
ITEM_TYPE NUMBER NOT NULL --<< Only 4 different values >>
);
As you can see, U_VALUE is ALWAYS lower than O_VALUE.I need to have the CREATE_TS in either main- or subpartition do drop the partitions after some time so i don,t have to use DELETE statements. The table has 360 millions rows.
The application has only 8 query which will always use a WHERE clause like this:
SELECT * FROM TEST
WHERE U_VALUE <= :1 AND O_VALUE => :2 AND ITEM_TYPE = :3
1. Is there any good technique how to create a good index for the queries (application will execute 120 queries per second)?
2. how to partition this table?
View 2 Replies
View Related
Jun 30, 2011
how to know weather procedure is working or not in a database ..
how to know index is created on table or not.
View 2 Replies
View Related
Apr 19, 2011
Can there be an impact on performance if below two index exists on the same table:
t_idx(col1,col2, nvl(col7,col6));
t_idx1(col1,col2,nvl(col6,col7));
View 5 Replies
View Related
Jul 10, 2012
I have 2 similar tables. Same columns. Data inserted to second table from first for a condition and first table is truncated. Then data moved from second to first and second table truncated. Second table is to store data for a small period of time. First table has indexes. Do I need any index on second table?
The query on second table is always insert into second select * from first. Similarly insert into first select * from second.
View 3 Replies
View Related
Jun 27, 2012
I am on 11.2.0.3 Enterprise Edition. We are using the new feature "Composite Domain Index" for a Domain index on a very large table (>250.000.000 rows). It really works with mixed queries. We added two number columns using FILTER BY.We have lots of DML on this table. Therefore, we are executing synchronize and optimize once the week. The synch behaves pretty normal. But "optimize_index" takes a very very long time to complete. I have switsched on 'logging' for the optimize process. The $I table takes some time but is finished normally. But the optimization of the $S table (that is the table created for the CDI feature) is running over 12 hours now - and far from being finished. From the logfile, I can see that it optimizes 1000 rows every 20 minutes. Here is the output of the logfile:
Oracle Text, 11.2.0.3.0
14:33:05 06/26/12 begin logging
14:33:05 06/26/12 event
14:33:05 06/26/12 process $N for optimize: SEQDEV.GEN_GES_DESCRIPTION_CTX_I
14:33:16 06/26/12
14:33:16 06/26/12
[code]....
I haven't found a recommendation from Oracle not to use "optimize_index" for Domain Indexes with CDI. But in my case, it would be much faster just to drop and recreate the Domain Index in question.
View 5 Replies
View Related
May 14, 2007
1) how to find a primary key of a table in oracle, for example if I want to drop a index in table , how do I find what is the primary key in a table so that I can issue that command.
P/s: I don't have OEM installed so I must use SQL command
Example of dropping the index
ALTER TABLE table_name
drop CONSTRAINT constraint_name;
For example:
ALTER TABLE supplier
drop CONSTRAINT supplier_pk;
View 2 Replies
View Related
