SQL & PL/SQL :: Alphanumeric Sequence Generator

Apr 12, 2010

Is there a function or a process to generate and maintain an alphanumeric sequence?

I need to be able to sequentially generate both a 3 and 5 byte case sensitive alphanumeric primary key that uses A-Za-z0-9 characters intermittently of which the 5 byte MAY be a subset of the 3 byte. e.g. the key can be 00000 or a3BD7.

so I need to be able to generate a new 5 char using 3 char base and if that value is not available, generate a unique 5 char value and allocate the substr(1,3) of that value backwards.

Ex1: unique customer location comes in - not a preexisting customer name (same name,different location address doesn't exist) --> select nextval.[5char] into v_long_c to insert into customer_loc table, and substr (v_long_c, 1, 3) for customer name table.

EX 2: unique customer location comes in. customer name exists in customer table with 3char val A39, but that location doesn't exist in customer_location table (5chars are location specific).

I need to take the A39 and generate new 5char for that unique location using that prefix (A39) if possible.

However, a number of a39-- already exist, though not all assigned to the same customer name (we're trying to keep them grouped together but that might not be logically feasible)

How do I select next a39||[2char] for that unique location - and if that value is not available (all 62*62 possibilities have been used for A39--), select nextval.[5charseq] into long_key.

I suspect someone out there knows the functions I can use to create this or has written a package to do just this; I suspect with enough time I could do it, but I don't currently have the time or knowledge to develop it within these deadlines. I thought it would be easier.

My short-sighted solution was to create a static table of all iterations of A-z0-9 5 char values, select one, mark it used and move on. Unfortunately 62^5 is a substantial number (913+million records) and that table took a LOT of space, causing my development server to groan and crack miserably. indexing on it takes a lot of space too (and trying to build multiple indexes exceeded database size). But without an index on the 3char field, selecting an available 5 code from it based on the customer_3char prefix took five minutes - much longer than the fraction of a second I need.

View 5 Replies


ADVERTISEMENT

SQL & PL/SQL :: Oracle Primary Key Error On Sequence Number Generator

Mar 11, 2011

I have a table called table 1 and I am trying to insert a few values in this table and i am constantly getting bogged down by a primary key error. Strange thing is this primary key called "ID" on the table is a system generated sequence value number. The error is

"ORA-00001: unique constraint (Schema1.LICN_PK) violated"

Table structure:
*****************************************
CREATE TABLE schema1.table1
(
ID NUMBER(12),
LITM_ID NUMBER(12),
PROG_PROGRAM_CD VARCHAR2(2 BYTE),
SCHED_NBR VARCHAR2(3 BYTE),
SCHD_VERSION_YR NUMBER(4),
SCHD_VERSION_NBR NUMBER(3),
[code]........

****************Insert statement*************
insert into table1
(id,
litm_id,
prog_program_cd,
sched_nbr,
schd_version_yr,
[code].......

why the error is coming up and any way to resolve it. I checked the max sequence number value and kept running it for a while and then tried to run my insert statement but no luck yet.

View 5 Replies View Related

SQL & PL/SQL :: How To Use Row Generator

Apr 16, 2013

I have a specific problem like below of allocating proper pallets to received quantity. row generator query feature.

CREATE TABLE PALLETMASTER
(
PALLETTYPE VARCHAR2(1 BYTE),
CAPACITY NUMBER(2)
);

Insert into PALLETMASTER (PALLETTYPE, CAPACITY) Values ('A', 18);
Insert into PALLETMASTER (PALLETTYPE, CAPACITY) Values ('B', 16);
Insert into PALLETMASTER (PALLETTYPE, CAPACITY) Values ('C', 15);
Insert into PALLETMASTER (PALLETTYPE, CAPACITY) Values ('D', 14);
[code]...

View 12 Replies View Related

SQL & PL/SQL :: A Row Generator?

Oct 25, 2011

In my table two colums are there say Col1 and Col2. Data is:

Col1 Col2
----- -----
15 20

So i want to get the result in some Column as:
ROWNUM Col
------- -----
1 15
2 16
3 17
4 18
5 19
6 20

View 1 Replies View Related

SQL & PL/SQL :: Order By Alphanumeric?

Aug 1, 2012

create table x(
sno varchar2(5)
);

insert into x values('A-1');
insert into x values('B-1');
insert into x values('B-2');
insert into x values('B-3');
insert into x values('1');
insert into x values('A-2');
insert into x values('2');
insert into x values('3');
insert into x values('A-4');
insert into x values('B-4');
insert into x values('C-4');
insert into x values('D-4');

SQL>select * from x;

SNO
-----
A-1
B-1
1
A-2
2
3
A-4
B-4
C-4
D-4
B-2
B-3

How can I select it ike this

1
A-1
A-2
A-4
B-1
2
B-2
3
B-3
B-4
C-4
D-4

View 15 Replies View Related

SQL & PL/SQL :: Check For Alphanumeric Values

Sep 8, 2011

i need a function which checks if v_rand carrying a value is alphanumeric if nt this value of 6 alpha numeric characters must be generated again... here is the actual fn.

Function alphanumeric
Return varchar2
is
v_rand varchar2(10) := 0;
Begin
[code]......

View 4 Replies View Related

SQL & PL/SQL :: Sort Alphanumeric Data

Nov 20, 2008

I have an urgent request which is pending with the following problem.

Problem :

I have a table which contains data of various datatypes like alphanumeric,varchar and number.

Now my query is " how to sort the data of the table using alphanumeric field"

How to select the data in a required(MyRequirement) sort order.

Data
============
12.4PI1
12.4
12.2
12.4T
12.3PI1

[Code]....

afterSorting(which I am getting Now)
============
12.2
12.3PI1
12.4
12.4PI1
12.4PI10
12.4PI11

[Code]...

MyRequirement
===============
12.2
12.3PI1
12.4
12.4PI1
12.4PI2
12.4PI3
12.4PI10

[Code]..

Means it has to sort the data order by lefthand side of PI and also righthand side of PI.

Pls check the attachment if you are not getting the above data in correct order.

View 22 Replies View Related

SQL & PL/SQL :: Alphanumeric Sorting Column Base

Jun 18, 2010

I am trying to sort columns base on cluster and alpha numeric field.

Column1 Columns2 column3
1 2-CA6R-234 9
1 2-CA6R-231 8
1 ARCT-0037000000ewegZ 10
2 2-QIZFF7 1
3 2-PIZFF6 6
3 ARCT-0037000000ewipk 9
3 2-QIZTF7 1

Wanted to sort in a way that column1 will be same order and the second column will order first with ARCT-XXXXX and then reset of the column2. It should look like this

Column1 Columns2 column3
1 ARCT-0037000000ewegZ 10
1 2-CA6R-231 8
1 2-CA6R-234 9
2 2-QIZFF7 1
3 ARCT-0037000000ewipk 9
3 2-PIZFF6 6
3 2-QIZTF7 1

View 9 Replies View Related

SQL & PL/SQL :: Select Only Rows Which Has Number And Not Alphanumeric Value?

Oct 16, 2012

I have a column COL1 in table TAB1 which is varchar2. I want select only rows which has number and not alphanumeric value? I don't want to use regexp for this since

View 11 Replies View Related

SQL & PL/SQL :: Last Numeric Character In Alphanumeric String

Feb 8, 2011

How can I find the last numeric value in the alphanumeric string?

Example:

LS14160220SPAD show me 0
MN23160224N show me 4
SP34524442 show me 2

View 9 Replies View Related

SQL & PL/SQL :: How To Validate Given String For Alphanumeric Character

Feb 14, 2011

To built the Pl/SQL function or SQL query to validate the given string for alphanumeric character.

I would pass the string of size 10 character, we need to validate first 5 character as alphabet, next 4 character as numeric and last 1 character as alphabet.

I will pass the each row value to the function, it need to return "T" or "F" based on the condition,first 5 character as alphabet, next 4 character as numeric and last 1 character as alphabet.

Here is the DML and DDL.

[code]

create table abc ( classid varchar(10));

insert into abc values ("abcde1234f");
insert into abc values ("abcde12345");
insert into abc values ("ab1de1234f");
insert into abc values ("abcde1234f");
insert into abc values ("abcd21234f");

[code]

Output:

Input : abcde1234f Output : T
Input : abcde12345 Output : F
Input : ab1de1234f Output : F
Input : abcde1234f Output : T
Input : abcd21234f Output : F

View 6 Replies View Related

SQL & PL/SQL :: Exception Query - Alphanumeric Input?

Dec 27, 2012

I'm a newbie to PL/SQL. I had a quick query about trapping exceptions.

I have a sample table called my_emp, which contains last name, salary, etc. I have written the following code that takes in an employee salary and if the salary exists it displays the last name and corresponding salary. If two or more rows are returned, the exception handles it. Likewise if there are no records with that salary, the exception takes care of it.

I was trying to input an alphanumeric input, such as 1bbb as the salary and of course ORA-06502 error pops up in the sql command line. I now want to trap this using an exception but whatever I try I still get the ORA-06502 in the calling environment rather than getting the 'Not a number' or 'Some other error occured' message. why the WHEN VALUE_ERROR or the WHEN OTHERS exceptions are not trapping the error?

DECLARE
v_sal NUMBER (12) := '&Enter_salary';
v_last_name VARCHAR2(10);
BEGIN
SELECT last_name

[code]...

View 3 Replies View Related

Forms :: How To Validate Alphanumeric And Symbol In Textbox

Nov 12, 2012

I have created a form that have a button to check the character in the textbox is alphanumeric and symbol. Here attached the code, that i tried from other post.

View 5 Replies View Related

Get A Query Of Each Sequence?

Jul 24, 2010

How do I get a query of each sequence and who has the permissions to it?

View 1 Replies View Related

Using Sequence In A Trigger

Jun 1, 2009

I have created a trigger for after insert which updates a table when there is a row inserted in that table. The update is on a column which stores the application description along with the sequence number. Now my requirement is that sequence number should be unique only with in an application but not with in the table.Say the row entry can be as follows:

App_Desc Request_ID
-----------------------
DEV 100 1
DEV 101 2
STG 100 3
STG 101 4

Here Request_ID is unique But the sequqnce thats created for DEV (100,101) should take an entry of 102 for the next entry for DEV and same applies for STG. So I have to use the same sequqnce for all the application.

View 1 Replies View Related

Trigger Corresponding To A Sequence

Apr 16, 2007

I have 3 tables whose structure is as follows:

1) Invoice
Invoice_no number(8),
Debtor varchar2(35),
Invoice Date Date,
Print Date Date,
Currency varchar2(3),
Total Amount Ex Number(11,2),
Total Amount Vat Number(11,2),
Total Amount Number(11,2),
Status Varchar2(15),

2) Invoice Line

Invoice_no number(8),
Invoice_Line_No number(8),
Description_1 varchar2(35),
Description_2 varchar2(35),
Line_Quantity Number(11,2),
Currency varchar2(3),
Line_Amount Number(11,2),
Vat Code number(1),
Status Varchar2(15),

3) vat code
vat code number(1),
Description varchar2(35),
Vat Percentage number(11,2)

Here invoice no and invoice line no are automatically generated fields?

How to generate invoice line no corresponding to invoice no and how to satisfy the following conditions:

1) total vat amount in invoice table should be automatically filled . total vat amount=(line_quantity* line_amount*vat percentage)/100

2) invoice cannot be printed, changed when status is 'closed'.

3) invoice lines cannot be added,changed or removed when status of invoice is closed

4) when any of invoice line no is changed,added, removed or invoice is changed the status of invoice should be changed to 'new'.

5) invoice date of a new invoice cannot be of the past

View 1 Replies View Related

How To Set Sequence Cache

Oct 10, 2012

Are there any recommendations or good practices to set sequence CACHE parameter (for example one caching per hour, day etc)?

View 4 Replies View Related

SQL & PL/SQL :: Sequence Skips First Value?

Jul 23, 2010

For some reason Oracle Sequence is skipping the first value. Here is an example of script.

drop sequence tseq;
drop table xyz;
create table xyz
(sno integer);
create sequence tseq
start with 1;
insert into xyz values (tseq.nextval);
select * from xyz;

Now the runtime of the script in SQL Developer:

drop sequence tseq succeeded.
drop table xyz succeeded.
create table succeeded.
create sequence succeeded.
1 rows inserted
SNO
----------------------
2

Why is it skipping the starting value 1. Is there something new in 11g that is causing it to skip the first value. I have many scripts and they all are having errors due to this issue.

View 39 Replies View Related

SQL & PL/SQL :: How To Create A Sequence

Sep 11, 2010

Create a sequence E_SQ which start with the (current max empno + 1) and increment by 1.

View 5 Replies View Related

SQL & PL/SQL :: Alter Sequence With No Max Value?

May 4, 2010

is there any way to alter only max value in sequence without specifying the max value.

i know we can alter it like :

-- Alter sequence
alter sequence TEST_SEQ
maxvalue 99999999;

can we alter it without providing the max value and let oracle choose default value for the same, same as we can do it when creating a new sequence.

View 5 Replies View Related

SQL & PL/SQL :: Sequence Can Be Used Across Databases?

Mar 19, 2012

As we can give grant on sequence and can use in different schema of same database, can same sequence be used across databases?

View 2 Replies View Related

SQL Plus Viewing A Sequence

Nov 1, 2007

After you've created a sequence in sql plus is there anyway of viewing the contents of the sequence and what it does?

View 1 Replies View Related

SQL & PL/SQL :: Sequence Next Value Change?

Jul 25, 2011

We Require to Change the Sequence Next Value.

If it possible to Update the Sequence Next Value in Oracle.

View 3 Replies View Related

SQL & PL/SQL :: Sequence In A Trigger

Dec 27, 2010

The below trigger is running fine without any error ,but i wasn't able to perform the exact logic by the below trigger.IF, inserting multiple row then the SIVNUM field gets the same sequence number for each row , without incrementing the value for the next row.

Only the sequence value increases on the next set of row ow's inserted

Example (inserting 2 rows ) based on the below trigger:

item , refwo , sivnum
1234 , 55555 , 00001
6352 , 77777 , 00002

i want to achieve :

item , refwo , sivnum
1234 , 55555 , 00001
6352 , 77777 , 00001

CREATE OR REPLACE TRIGGER siv_mat_insert
BEFORE INSERT
ON MATUSETRANS
REFERENCING OLD AS old NEW AS new
for each row
[code]....

View 6 Replies View Related

SQL & PL/SQL :: Sequence Identification

May 29, 2012

I have round about 100 sequences in a schema. How can i identify that which sequences are not used for a long time. Or How can i identify the last date when that sequence is used.

View 5 Replies View Related

Sequence Number In 11g RAC

Sep 17, 2012

In our production, we have two nodes in the cluster. We use the sequence for one of the main table for primary key. Our application is expecting sequence number increments along with created date time stamp. Right now sequences are cached for each node and it creates problem for the application. We would not like to use NOCACHE option because it causes performance issue.

This is the current scenario -

Transaction #1 on Node 1 - Seq ID 1 - Time Stamp 12:01
Transaction #2 on Node 2 - Seq ID 51 - Time Stamp 12:02
Transaction #3 on Node 1 - Seq ID 2 - Time Stamp 12:03

When I try to query based on the time stamp, primary should also go up. To be very clear on what I would like to have, please consider the following example.Without using NOCACHE option, I need to have the data in the following order.

Transaction #1 on Node 1 - Seq ID 1 - Time Stamp 12:01
Transaction #2 on Node 2 - Seq ID 2 - Time Stamp 12:02
Transaction #3 on Node 1 - Seq ID 3 - Time Stamp 12:03

In other words, sequence number should always increment along with the time.

View 2 Replies View Related

SQL & PL/SQL :: Sequence - Getting Error?

May 13, 2011

i have create one sequece using this syntax

CREATE SEQUENCE seq_emp_id START WITH 100;

after that when i create a table using this syntax

CREATE TABLE employee
(
emp_id integer default NEXTVAL ('seq_emp_id'),
name text
);

i got an error like this
*
ERROR at line 2:
ORA-04044: procedure, function, package, or type is not allowed here

so how can i resolve my this error

View 3 Replies View Related

PL/SQL :: New Enhancement In 11G Sequence

Oct 22, 2012

I have Created sequence in oracle 10G and its working fine. its showing THE NUMBER IS 2

SQL> DECLARE NUM NUMBER;
  2  BEGIN
  3  select SERIAL_NO.NEXTVAL into num from dual;
  4  DBMS_OUTPUT.PUT_LINE('THE NUMBER IS ' ||NUM);
  5  END;
  6  /
THE NUMBER IS 2

PL/SQL procedure successfully completed.When i execute this code in oracle 11G then it does not show output.

SQL> DECLARE NUM NUMBER;
  2  BEGIN
  3  NUM :=SERIAL_NO.NEXTVAL;
  4  DBMS_OUTPUT.PUT_LINE('THE NUMBER IS ' ||NUM);
  5  END;
  6  /

PL/SQL procedure successfully completed. SQL> Why this is not showing output as THE NUMBER IS .

View 1 Replies View Related

Inserting Values From A Sequence?

Jun 10, 2011

I created a Table with a single column varchar2.. in which I wanted to insert value like 'BBBBAB1'... till 'BBBBAB100'

Created a sequence starting with 1...

and inserted single row, and multiple rows using loop also; by using below code -

insert into Trans SELECT CONCAT('BBBBAB', Trans1.NEXTVAL) from dual;

but whenever I see the values they are not as required ... 'BBBBAB1' but one character 'B' is missing, and the values populating are 'BBBAB1'.. 'BBBAB100'

View 1 Replies View Related

SQL & PL/SQL :: Sequence Increments When It Is Not Being Called

Dec 1, 2012

I have a table called "Subjects" which lists subjects to match with notations in another table I have created a simple sequence (CREATE sequence subjectid) to created the subject id for the table. But I notice that if there is a skip in the date, the sequence increments automatically when I am not even using it. It even appears to be incrementing even when I am not doing any database activity.

This is not an issue of data integrity, because the values in the subject_id column do not need to be sequential, they just need to be unique. But it really has me curious. I created another table called "keep_track" to keep track of what is happening:

16-NOV-12 2952
16-NOV-12 2953
16-NOV-12 2954
16-NOV-12 2955
16-NOV-12 2956
16-NOV-12 2957
16-NOV-12 2958
16-NOV-12 2959
16-NOV-12 2960
16-NOV-12 2961
16-NOV-12 2962

[Code] ......

View 10 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved