Home
All Oracle Error Codes
Oracle DBA Forum

Frequent Oracle Errors

TNS:could not resolve the connect identifier specified
Backtrace message unwound by exceptions
invalid identifier
PL/SQL compilation error
internal error
missing expression
table or view does not exist
end-of-file on communication channel
TNS:listener unknown in connect descriptor
insufficient privileges
PL/SQL: numeric or value error string
TNS:protocol adapter error
ORACLE not available
target host or object does not exist
invalid number
unable to allocate string bytes of shared memory
resource busy and acquire with NOWAIT specified
error occurred at recursive SQL level string
ORACLE initialization or shutdown in progress
archiver error. Connect internal only, until freed
snapshot too old
unable to extend temp segment by string in tablespace
Credential retrieval failed
missing or invalid option
invalid username/password; logon denied
unable to create INITIAL extent for segment
out of process memory when trying to allocate string bytes
shared memory realm does not exist
cannot insert NULL
TNS:unable to connect to destination
remote database not found ora-02019
exception encountered: core dump
inconsistent datatypes
no data found
TNS:operation timed out
PL/SQL: could not find program
existing state of packages has been discarded
maximum number of processes exceeded
error signaled in parallel query server
ORACLE instance terminated. Disconnection forced
TNS:packet writer failure
see ORA-12699
missing right parenthesis
name is already used by an existing object
cannot identify/lock data file
invalid file operation
quoted string not properly terminated

Range function

Yechiel Adar

2006-03-17

Replies:
I got several requests for the function so I decided to post it.
I do not remember what ts_ranges does, probably a copy for tests.

This was written years ago so there must be room for improvements.
It does mostly comparison so compiling it will bring great benefit.

The function gets 2 parameters:
1) ranges list. from1_to1,from2_to2,.....
2) Value to check.

For example: select Range('0-100,101-200,201-300',balance) from account;
will divide the records to negative balance, one of the ranges or more
then 300.

I am keeping the last 2 range lists in memory to avoid parsing of the
range list each time.

As you can see you can use and copy it, but please retain the copyright.

If you make any enhancements please tell me so I can enhance my copy.

USE IT ON YOUR OWN RISK.

CREATE OR REPLACE PACKAGE "RANGE" AS
--
-- Developed by: Yechiel Adar, Mehish Computer services.
--
-- E-mail: _adary_@(protected)>
       adar666@(protected)>

-- All Rights Reserved.
-- You can use, give away and modify this package freely,
-- just retain the credits.
-- Send any modifications and changes back to me so I can enhance my copy.
--
TYPE R_vec_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
current_ptr NUMBER DEFAULT 0;
R_low R_vec_type;
R_high R_vec_type;
no_of_ranges NUMBER DEFAULT 0;
SAVE_PARAM VARCHAR2(300) DEFAULT NULL;
R_low1 R_vec_type;
R_high1 R_vec_type;
no_of_ranges1 NUMBER DEFAULT 0;
SAVE_PARAM1 VARCHAR2(300) DEFAULT NULL;
R_low2 R_vec_type;
R_high2 R_vec_type;
no_of_ranges2 NUMBER DEFAULT 0;
SAVE_PARAM2 VARCHAR2(300) DEFAULT NULL;

FUNCTION RANGES(RANGE_LIST IN VARCHAR2 , FORMULA_V IN NUMBER) RETURN
VARCHAR2 PARALLEL_ENABLE;
FUNCTION ts_RANGES(RANGE_LIST IN VARCHAR2 , FORMULA_V IN NUMBER)
RETURN VARCHAR2 PARALLEL_ENABLE;
PRAGMA RESTRICT_REFERENCES (RANGES, WNDS, RNDS);
END RANGE;
/


CREATE OR REPLACE PACKAGE BODY RANGE IS
-- PRAGMA SERIALLY_REUSABLE;

FUNCTION RANGES(RANGE_LIST IN VARCHAR2 ,FORMULA_V IN NUMBER) RETURN
VARCHAR2
  IS
list_p VARCHAR2(300);
obligo_p  NUMBER;
len NUMBER;
s1 VARCHAR2(300);
BEGIN
IF SAVE_PARAM = RANGE_LIST THEN
CURRENT_PTR := current_ptr;
ELSIF SAVE_PARAM1 = RANGE_LIST THEN
SAVE_PARAM := SAVE_PARAM1;
FOR I IN 1..NO_OF_RANGES1 LOOP
R_LOW(I) := R_LOW1(I);
R_HIGH(I) := R_HIGH1(I);
END LOOP;
NO_OF_RANGES := NO_OF_RANGES1;
ELSIF SAVE_PARAM2 = RANGE_LIST THEN
-- dbms_output.put_line('2 ' || current_ptR);
SAVE_PARAM := SAVE_PARAM2;
FOR I IN 1..NO_OF_RANGES2 LOOP
R_LOW(I) := R_LOW2(I);
R_HIGH(I) := R_HIGH2(I);
END LOOP;
NO_OF_RANGES := NO_OF_RANGES2;
ELSE
-- dbms_output.put_line('1 ' || CUrrent_ptR);
SAVE_PARAM := range_list;
list_p := range_list;
FOR I IN 1..NO_OF_RANGES LOOP
R_LOW(I) := NULL;
R_HIGH(I) := NULL;
END LOOP;
NO_OF_RANGES := 0;
FOR i IN 1..LENGTH(list_p) LOOP
   len     := INSTR(list_p,',');
IF len IS NULL THEN
 EXIT;
END IF;
no_of_ranges := no_of_ranges + 1;
s1     := SUBSTR(list_p,1,len);
list_p   := SUBSTR(list_p,len+1);
   len     := INSTR(s1,'_');
   R_low(i) := SUBSTR(s1,1,len-1);
   R_high(i) := SUBSTR(s1,len+1,LENGTH(s1) - len - 1);
END LOOP;
IF SAVE_PARAM1 = NULL OR CURRENT_PTR = 2 THEN
SAVE_PARAM1 := SAVE_PARAM;
FOR I IN 1..NO_OF_RANGES LOOP
 R_LOW1(I) := R_LOW(I);
 R_HIGH1(I) := R_HIGH(I);
END LOOP;
NO_OF_RANGES1:= NO_OF_RANGES;
CURRENT_PTR := 1;
ELSE
SAVE_PARAM2 := SAVE_PARAM;
FOR I IN 1..NO_OF_RANGES LOOP
 R_LOW2(I) := R_LOW(I);
 R_HIGH2(I) := R_HIGH(I);
END LOOP;
NO_OF_RANGES2 := NO_OF_RANGES;
CURRENT_PTR := 2;
END IF;
END IF;
-- dbms_output.put_line(current_ptr || save_param);
obligo_p:=FORMULA_V;
IF obligo_p < R_low(1) THEN
RETURN ' 0 ) <' ||R_low(1);
END IF;
FOR j IN 1..no_of_ranges LOOP
 IF obligo_p BETWEEN R_LOW(j) AND R_high(j) THEN
 IF j < 10 THEN
     RETURN ' '||j||' ) '||R_low(j)||'-'||R_high(j);
ELSE
     RETURN j||' ) '||R_low(j)||'-'||R_high(j);
END IF;
 END IF;
END LOOP;
IF obligo_p > R_high(no_of_ranges) THEN
  IF no_of_ranges+1 < 9 THEN
  RETURN ' '||no_of_ranges+1||' ) '||R_high(no_of_ranges)||'+';
  ELSE
  RETURN no_of_ranges+1||' ) '||R_high(no_of_ranges)||'+';
END IF;
END IF;
RETURN obligo_p || ' - NOT IN RANGE !!';
END RANGES;

FUNCTION ts_RANGES(RANGE_LIST IN VARCHAR2 ,FORMULA_V IN NUMBER) RETURN
VARCHAR2
  IS
list_p VARCHAR2(300);
obligo_p  NUMBER;
len NUMBER;
s1 VARCHAR2(300);
BEGIN
IF SAVE_PARAM = RANGE_LIST THEN
CURRENT_PTR := current_ptr;
ELSIF SAVE_PARAM1 = RANGE_LIST THEN
SAVE_PARAM := SAVE_PARAM1;
FOR I IN 1..NO_OF_RANGES1 LOOP
R_LOW(I) := R_LOW1(I);
R_HIGH(I) := R_HIGH1(I);
END LOOP;
NO_OF_RANGES := NO_OF_RANGES1;
ELSIF SAVE_PARAM2 = RANGE_LIST THEN
-- dbms_output.put_line('2 ' || current_ptR);
SAVE_PARAM := SAVE_PARAM2;
FOR I IN 1..NO_OF_RANGES2 LOOP
R_LOW(I) := R_LOW2(I);
R_HIGH(I) := R_HIGH2(I);
END LOOP;
NO_OF_RANGES := NO_OF_RANGES2;
ELSE
-- dbms_output.put_line('1 ' || CUrrent_ptR);
SAVE_PARAM := range_list;
list_p := range_list;
FOR I IN 1..NO_OF_RANGES LOOP
R_LOW(I) := NULL;
R_HIGH(I) := NULL;
END LOOP;
NO_OF_RANGES := 0;
FOR i IN 1..LENGTH(list_p) LOOP
   len     := INSTR(list_p,',');
IF len IS NULL THEN
 EXIT;
END IF;
no_of_ranges := no_of_ranges + 1;
s1     := SUBSTR(list_p,1,len);
list_p   := SUBSTR(list_p,len+1);
   len     := INSTR(s1,'_');
   R_low(i) := SUBSTR(s1,1,len-1);
   R_high(i) := SUBSTR(s1,len+1,LENGTH(s1) - len - 1);
END LOOP;
IF SAVE_PARAM1 = NULL OR CURRENT_PTR = 2 THEN
SAVE_PARAM1 := SAVE_PARAM;
FOR I IN 1..NO_OF_RANGES LOOP
 R_LOW1(I) := R_LOW(I);
 R_HIGH1(I) := R_HIGH(I);
END LOOP;
NO_OF_RANGES1:= NO_OF_RANGES;
CURRENT_PTR := 1;
ELSE
SAVE_PARAM2 := SAVE_PARAM;
FOR I IN 1..NO_OF_RANGES LOOP
 R_LOW2(I) := R_LOW(I);
 R_HIGH2(I) := R_HIGH(I);
END LOOP;
NO_OF_RANGES2 := NO_OF_RANGES;
CURRENT_PTR := 2;
END IF;
END IF;
-- dbms_output.put_line(current_ptr || save_param);
obligo_p:=FORMULA_V;
IF obligo_p < R_low(1) THEN
RETURN ' 0 ) <' ||R_low(1);
END IF;
FOR j IN 1..no_of_ranges LOOP
 IF obligo_p BETWEEN R_LOW(j) AND R_high(j) THEN
 IF j < 10 THEN
     RETURN ' '||j||' ) '||R_low(j)||'-'||R_high(j);
ELSE
     RETURN j||' ) '||R_low(j)||'-'||R_high(j);
END IF;
 END IF;
END LOOP;
IF obligo_p > R_high(no_of_ranges) THEN
  IF no_of_ranges+1 < 9 THEN
  RETURN ' '||no_of_ranges+1||' ) '||R_high(no_of_ranges)||'+';
  ELSE
  RETURN no_of_ranges+1||' ) '||R_high(no_of_ranges)||'+';
END IF;
END IF;
RETURN obligo_p || ' - NOT IN RANGE !!';
END ts_RANGES;

END RANGE ;
/


Adar Yechiel
Rechovot, Israel




--
http://www.freelists.org/webpage/oracle-l