SQL & PL/SQL :: Sequence Number Generated

Jun 6, 2011

I am facing issue with my sequence number about sequence number generated..I want it to follow the sequence all the time, i.e. it should take next incremental number to last generated seauence, but it generates random one all the time, i.e

s.nextval() = 4
s.nextval() = 5
s.nextval() = 10
s.nextval() = 543
s.nextval() = 544

My sequence defination is as below

CREATE SEQUENCE s
START WITH 1
MAXVALUE 999999999999999999999999999
MINVALUE 1
NOCYCLE
CACHE 20

View 11 Replies


ADVERTISEMENT

Forms :: Disable Save Button After The Sequence In Generated?

Feb 2, 2013

i have master-detail form in which i am generating sequence number as my bill no. on save button.

i want to disable save button after the sequence in generated.

View 1 Replies View Related

Export/Import/SQL Loader :: Fetching ID Sequence Generated Value

Oct 25, 2012

I Have a case where in the id value generated through sequence from first table should be used as the value for one of the columns in second table.

i.e. for Example.

I am Running sqlloader for Table 1 with a ctl file:

Column : Asset_id : sequence generated value(Ex.500)

Now while running sqlloader for Table 2: The above generated sequence value(500) should be fetched and stored in Asset_id field for this table.

View 1 Replies View Related

Server Utilities :: Assign Numeric Value Generated From Sequence In Control File?

Dec 22, 2003

I have a control file like following:

LOAD DATA
INFILE *
INTO TABLE member
REPLACE
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
(
control_id,
name,
address
)

problem is that control_id value is not in data file and I have to assign to each row the same value generated from sequence or from unix variable.

For example, after I run sqlldr, I have to have records in the table like following:

control_id name address
---------- ---- -------
1847 Charlie 250 yonge st
1847 Peter 5 Brookbanks dr
1847 Ben 123 King st
.
.
.

How do I do that?

View 29 Replies View Related

SQL & PL/SQL :: How To Generate Number (not Sequence Number) In Query

Mar 9, 2011

I have the following select query that works perfectly fine. Returns 25 rows based on the descending order of the price.But, I want add one more expression to this list of columns in this query (apart from customer_id).

the expression should look like Cust-01 for the first customer from the below query all the way to Cust-25 for the last customer.But how can I can generate 01 to 25 in oracle?

select customer_id from
(select customer_id from capitalPLAN
where member_status = 'MEMBER' AND customer_id NOT in ('156','201','1385','2125','3906','165')
order by price desc
)
where rownum <= 25

View 4 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 Number Not Allowed

Mar 2, 2013

I have a following select statement where I am not generating a sequence in any of these WHERE clause, GROUP BY or ORDER BY clause, DISTINCT clause, along with a UNION or INTERSECT or MINUS or sub-query.

I have query like this

"SELECT
AER_ID_TEMP.AER_ID,
AER_ID_TEMP.D__PRNO,
AER_ID_TEMP.D__PCNT,
CAST((select char_value from aris_param_data where param_id = 101)||seq_record_id.nextval AS INT) as RECORD_ID
FROM AER_ID_TEMP"

but still encountering a error "ORA-02287 sequence number not allowed"

View 2 Replies View Related

SQL & PL/SQL :: Sequence Number In A Trigger

Oct 15, 2010

CREATE OR REPLACE TRIGGER audit_emp AFTER
INSERT OR UPDATE OR DELETE ON Employee FOR EACH ROW
declare
my emp_audit%ROWTYPE;
begin
if inserting then my.action := 'I';
[code].......

It works alright but i need to enter a sequenced value to a column audit_id which is a column in the emp_audit table when a change takes place, but when i define the variable it gives be an error as bad bind variable so where can i define this column in this trigger so get a sequenced value every time i d or u occurs .

View 5 Replies View Related

SQL & PL/SQL :: Last Sequence Number Created

Mar 31, 2011

Is there any way to get the last Sequence Number generated regardless the Sequence name? Something like dbinfo('sqlca.sqlerrd1') within Informix?

Imagine I have 2 sequences. Imagine that I run both several times until 20:00 hours. Imagine that at 21:00 hours I would like to know what was the last generated number created by any of the sequences. I don't wann know wich sequence generated the last NEXTVAL, I only want to know the value of the last NEXTVAL used.

Is it possible without mentionating the Sequence name?

View 4 Replies View Related

SQL & PL/SQL :: Sequence Number Generation

Jul 26, 2010

I am using following Procedure/function for inserting DATA into New table:

PROCEDURE NEMPID IS
OFFICER CHAR(1) := '0';
OFFICIAL CHAR(1) := '9';
CURSOR DOJSUP IS

[Code]....

Get/Retrieved data from personal and cat9 files and Inserted Data stored in file NEMP as

EMP_ID NEMP_ID
------- ---------
111 01-8722
132 01-9224
88 01-8318
114 01-8717
106 01-8517

[Code]....

I want also generate a sequence/serial number for same category/designation of personals/employees exist in personal file ?

Like 001, 002 ,003 at the end of NEMPID field.

EMP_ID NEMP_ID
------- ---------
111 01-8722-001
132 01-9224-002
88 01-8318-003
114 01-8717-004

[Code]....

HOW CAN GENERATE SEQUENCE/SERIAL NUMBER OF SAME CATEGORY EMPLOYEES AND INSERT INTO TABLE NEMP?

View 16 Replies View Related

SQL & PL/SQL :: Query To Get Sequence Number In Group

Dec 7, 2010

I have Table Data in format :-

--------------------------
ABC
ABC
ABC
XYZ
XYZ

and i have a requirement where I need the output in this format.

-------------
ABC 1
ABC 2
ABC 3
XYZ 1
XYZ 2

what query to be used for this

View 2 Replies View Related

SQL & PL/SQL :: Same Sequence Number For Repeating Data

Feb 21, 2013

I want to insert same sequence number for repeated data else new sequence.

my data is as below

EMP_NAMEEMP_INV

PrasCTI
PrasCTI
ShariqWitness
KateCTI
ShariqAssisted
ShariqWitness

the first 2 record are same Pras CTI and Pras CTI so i want my sequence generate value once as below

1 Pras CTI
1 Pras CTI
2 Shariq Witness
3 Kate CTI
4 Shariq Assisted
2 Shariq Witness

Shariq and witness are repeating so same sequence number but Kate CTI occured once so nextval of sequence

How can i acivee it

CREATE TABLE EMP_INV
(
EMP_NAME VARCHAR2(100 BYTE),
EMP_INV VARCHAR2(100 BYTE)
)
Insert into EMP_INV
(EMP_NAME, EMP_INV)
Values
('Shariq', 'Witness');

[Code]...

View 12 Replies View Related

SQL & PL/SQL :: Number Sequence At Row Level Like ROWNUM

Jun 1, 2010

I have a result set with three columns as 'Product Category', 'Product' & 'QtySales' and 10 rows, sorted in the order Product Category, Product. This means, a product category will have one or more products under it.

Now i want to add a fourth column to my result set, which should display a incremental number sequence from starting from 1, 2, 3.. for each row. Also when the value of the Product Category (1st column) changes, this sequence should be restarted again from 1.

Col1 Col2 Col3 Col4
PC1 P1 10 1
PC1 P2 20 2
PC2 P3 30 1
PC2 P4 10 2
PC2 P5 15 3
PC3 P6 25 1

View 1 Replies View Related

SQL & PL/SQL :: Rows In Table With Sequence Number

May 17, 2012

I have a table ABC with two columns NAME and SEQUENCE. The valid values of sequence are from 1..50. The table may not have all the Sequence number. For example following is the data in my table..

Quote:NAME SEQUENCE
------------------------
Jonh| 5
Amy| 1
Suresh| 3

I need a SQL to get the data like

Quote:NAME SEQUENCE
------------------------
Amy| 1
| 2
Suresh| 3
| 4
Jonh | 5
|6
|7
..
..
..
|50

Basically I want to have rows for the sequence numbers which are not present i my table. I thought of using the following query to generate a sequence and then join, but does not work.

select rownum from dual connect by rownum <=50

I tried something like

with temp as (select name, sequence from abc where supp_ref_order is not null order by sequence )
select rownum as num, temp.name from dual, temp connect by rownum <=50 where temp.sequence =num

View 12 Replies View Related

PL/SQL :: Generate Unique Number Without Sequence?

Feb 13, 2013

I need generate an unique number without sequence. I am using Oracle 11.2, here is the details. The column idSeq is unique withing one specific idType. I don't want a sequence for each idType.

create table tb_test (idSeq number(5), idType number(5), addr varchar2(256));

insert into tb_test
(select case when idSeq is null then 1 else max(idSeq)+1 end, 3, 'Main street');

I am having ORA-00937 : not a single-group group function error

View 23 Replies View Related

PL/SQL :: ORA-02287 / Sequence Number Not Allowed Here

Dec 5, 2012

create sequence seq minvalue 1 maxvalue 99999 start with 1 increment by 1;
create table t ( id number, b date);
insert into t values (seq.nextval,sysdate);
update t set b=sysdate-1 where id=seq.currval;
update t set b=sysdate-1 where id=seq.currval
*
ERROR at line 1:
ORA-02287: sequence number not allowed here

how can i update as above ..

View 7 Replies View Related

Duplicate Sequence Number For Identical Rows

Jul 5, 2010

I have a table:

Name
_____
Smith Street
Smith Street
John Street
Ed Street
Ed Street
Ed Street

and need to assign sequence numbers only when the record (Name) changes, e.g. :

Name Seq
_____ ____
Smith Street 1
Smith Street 1
John Street 2
Ed Street 3
Ed Street 3
Ed Street 3

I have experimented with row_number partition but then i just get the sequence returning to 1 when the name value changes.

If I grouped the records by Name I would like to have unique, sequential numbers: 1, 2, 3 but where there is the same name I would like the sequence to stop and the number to replicate?

View 9 Replies View Related

Forms :: Sequence Number To Be Displayed In Form

Mar 27, 2012

In my form I have a submit button, when i click on it a sequence number to be generated and displayed in a text box.

I have created a sequence and tried inserting into table, it works fine however it's not getting displayed in form.

Note: Tried giving sequence.sequence_name.nextval in initial value of the text box, didn't work showed FRM-11324 error.

--When button pressed--

DECLARE

v_seq number(20);

BEGIN

select seq_name.nextval
into v_seq
from dual;

[Code]...

View 15 Replies View Related

Forms :: Generating Sequence - Get Serial Number?

Dec 21, 2011

i want to get serial numbers in oracle forms. i have one data block with 10 rows and a non database display item with corresponding length as that of the original data block .

when ever i am executing the form i want to see the serial number generated in the display item and when i inserted a new item or row it should show the corresponding rownum.

eg:

empno name serialno
5 tom 1
4 tinil 2
6 sofy 3
7 john 4
1 albert 5
2 michel 6

i want to get the above answer using forms.

View 7 Replies View Related

SQL & PL/SQL :: Reference Cursor To Generate Sequence Number

Apr 1, 2013

I want the query to generate the sequenctial number from 1 for the new financial year in some query.

View 6 Replies View Related

SQL & PL/SQL :: How To Group Query Sequence Number Wise

Oct 3, 2010

I ve a data in table as follows

code status seqnce Length
B1 AVL 1 10
B2 AVL 2 10
B3 ASG 3 10
B4 AVL 4 10
B5 AVL 5 10
B6 AVL 6 10
B4 ASG 7 10
B4 ASG 8 10

I need to group the AVL status codes with the sum of lengths Ex) B1 to B2 - 20 B4 to B6 - 30 like that.

View 5 Replies View Related

Server Utilities :: SQLLDR Sequence Number

Jun 13, 2013

I have a CSV file with 100 records and one of the column as FILE_ID. I want to load one unique number for all 100 recs not for every records.

suppose my sequence returns 3 as next val i want to load 3 for all 100 records. How to implement this in control file or sh file . I am using shell script to call sqlldr.

View 2 Replies View Related

SQL & PL/SQL :: Creating Loop - Sequence Should Add A Number In The End Of Variables

Jun 12, 2013

SELECT
DECODE(BREINV1.NAMEKEY, NULL,'0','1') "BRE_INV1",
DECODE(BREINV1.NAMEKEY, NULL,' ',BREINV1.SEQUENCE) "BRE_NUMINV1",
DECODE(BREINV1.NAMEKEY, NULL, ' ', DECODE(BREINV1.SEQUENCE,NULL,NULL,

[Code]....

My problem, I have the query which works correctly. However as you can see there is always a sequence number in the end of variables and I am not sure the total number of cases, so I have to create a loop for the same query.

I am doing the same things on BREINV1 BREINV2 BREINV3 BREINV4... AND BRENUMINV1 BRENUMINV2... so the sequence should add a number in the end of variables...

View 39 Replies View Related

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 Auto Increment Number - Create Sequence And Trigger

Nov 26, 2012

I am switching database from access to oracle 11g. I have create all the required tables, but I am stuck at one point. The previous person who created access database had auto increment with SG0101, SG0102,........ In oracle I know we can auto increment primary keys but only with the numbers not with characters.

So I have customerid which is a primary key and it automatically increments the number, but I have one more column with memberid where I am inserting all the ids that start with SG0101 bla bla.....

I already have 800 member ID's that start with SG, but that value doesnt automatically increment because I dont have any sequence or trigger to do that.

So how do I create a sequence and trigger that will automatically start value with SG and keeps auto incrementing?

View 12 Replies View Related

Reports & Discoverer :: Sequence Number In Oracle 10gR1

Aug 26, 2010

My requirement is I have to get a sequence no generated for 2 set of groups. Here is an example to better resolve it.

Gr1
Seq Data
--------------
1 Data1
2 Data2
3 Data3

GR
Seq Data
--------------
4 Data1
5 Data2

View 1 Replies View Related

Server Administration :: What Is Control File Sequence Number

Jul 17, 2012

SQL> select checkpoint_change#,controlfile_change# from v$database;

CHECKPOINT_CHANGE# CONTROLFILE_CHANGE#
------------------ -------------------
203454 204955

what's the difference between checkpoint_change# and controlfile_change#.
what's the checkpoint_change# use for ? does it use for recover ?
what's the controlfile_change# use for ?
when the controlfile_change# will be increase ?

SQL> select controlfile_sequence# from v$database;

CONTROLFILE_SEQUENCE#
---------------------
293

Qs.) what is controlfile_sequence# ?

View 6 Replies View Related

Reports & Discoverer :: Showing The Number Sequence Of Displayed Data

Oct 21, 2010

I have query in that query i should display the number of row. I have tried to select the row num but when i use it the data will be duplicated. I have found something that related to use temporary table, or subquery but all of them will not work because the query depends on many tables and if i use temporary table filling the table and then reading from it will take too long time.

View 11 Replies View Related

Reports & Discoverer :: Getting Runtime Sequence Number At Report Level?

Jan 21, 2011

how do i get runtime sequence number at report level.

View 1 Replies View Related

Server Administration :: Archive Log File Sequence Number Reduced

Mar 28, 2012

We are facing a different issue in our database. From yesterday night, the archive log generated with 5 digit. But it supposed to be 6 digit. Hence we are not able to apply the logs in DR Location.

View 4 Replies View Related







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