SQL & PL/SQL :: How To Create A Sequence
Sep 11, 2010Create a sequence E_SQ which start with the (current max empno + 1) and increment by 1.
View 5 Replies
ADVERTISEMENT
SQL & PL/SQL :: Create A Sequence In DML Trigger?
Apr 12, 2013i would like to create a sequence inside a dml trigger. Is it possible? I created a trigger and it is compiled sucessfully.
create or replace trigger tri_update_test
after delete on test
declare
pragma autonomous_transaction;
[code]...
trigger created sucessfully.And i try to delete data from the test
delete from test where id=5;
Output:
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SCOTT.TRI_UPDATE_TEST", line 4
ORA-04088: error during execution of trigger 'SCOTT.TRI_UPDATE_TEST'
Create Sequence Using Subselect / Value From Select
Jul 8, 2008CREATE sequence customer_id start with (select max(customer_id) from customer) increment by 1
i am trying to do that, can it be done or should i just find the max id myself and replace that select with a no?
Server Utilities :: How To Create Sequence
Jul 16, 2011Every time sequence should be start from 1.can i know how to create sequence? for ex:-once i have uploaded 100 records this time sequence is generating from 1 to 100.next time i will upload again 100 records this time sequence is generating(Starting) from 200 to 300.but it should be generate from 1. how? i have created like this
CREATE SEQUENCE XX_SEQUENCE
MINVALUE 1
MAXVALUE 99999999999
START WITH 1
INCREMENT BY 1
CYCLE
NOCACHE;
Forms :: How To Create Sequence In Form
Jan 13, 2013I know how to create sequence in the database.But I want to create this sequence from the form by giving the name of the sequence from a filed in the form.
View 1 Replies View RelatedPossible To Create Sequence Inside DML Trigger
Apr 12, 2013 i would like to create a sequence inside a dml trigger. Is it possible? I created a trigger and it is compiled sucessfully.
create or replace trigger tri_update_test
after delete on test
declare
pragma autonomous_transaction;
begin
execute immediate 'create sequence t_unqid
start with 1
increment by 1
nocache '
end ;trigger created successfully. And i try to delete data from the test delete from test where id=5;
Output:
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SCOTT.TRI_UPDATE_TEST", line 4
ORA-04088: error during execution of trigger 'SCOTT.TRI_UPDATE_TEST'
PL/SQL :: Unable To Create Logic Sequence?
Oct 23, 2012create table t (a varchar2(20),b number(8));
insert into t values ('aa',4);
insert into t values ('ba',6);
insert into t values ('ca',7);
insert into t values ('da',8);
in place of 8 there can be any number between 1 to 100
if in place of 8 number is <10
insert into t values('ea',10); ---- this i need dynamic insert
if in place of the number between 11-19 then
insert into t values('ea',20); ---- this i need dynamic insert
and so on
i tried as below
select case when max(b) <10 then 10 when max(b) between 10 and 20 then 20 end from t;
but i cant write case for again and again upto nth
PL/SQL :: Create Sequence Using EXECUTE IMMEDIATE Statement?
Dec 14, 2012Is it possible to create sequence using EXECUTE IMMEDIATE statement. The sequence name will be the bind variable.
DECLARE
TEMP VARCHAR2(20);
BEGIN
TEMP := :P2_INFO;
EXECUTE IMMEDIATE 'CREATE SEQUENCE' TEMP;
END;
This creates a sequence named TEMP, but i want the name :P2_INFO entered by user.
SQL & PL/SQL :: How To Auto Increment Number - Create Sequence And Trigger
Nov 26, 2012I 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?
Create A Sequence For Primary Key To Simply Auto-increment By Default Of 1
Mar 14, 2005I'm trying to create a sequence for a primary key to simply auto-increment by the default of 1. I have a sql script written to generate mt tables, and I'm not sure how to modify the script to include the sequence. I also just want the sequence for a specific column, ie, PK, not the PK in all tables.
Here's a snippet from my script:
create table image
(
image_id int NOT NULL,
source_id int NOT NULL,
CONSTRAINT image_id_pk PRIMARY KEY (image_id),
CONSTRAINT fk_source_id FOREIGN KEY (source_id) REFERENCES source(source_id)
);
Would I add the create sequence statement right after the create table, and if so, how do I apply the sequence to only 1 table and a single column?
Get A Query Of Each Sequence?
Jul 24, 2010How do I get a query of each sequence and who has the permissions to it?
View 1 Replies View RelatedUsing Sequence In A Trigger
Jun 1, 2009I 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.
Trigger Corresponding To A Sequence
Apr 16, 2007I 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
How To Set Sequence Cache
Oct 10, 2012Are there any recommendations or good practices to set sequence CACHE parameter (for example one caching per hour, day etc)?
View 4 Replies View RelatedSQL & PL/SQL :: Sequence Skips First Value?
Jul 23, 2010For 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.
SQL & PL/SQL :: Alter Sequence With No Max Value?
May 4, 2010is 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.
SQL & PL/SQL :: Sequence Can Be Used Across Databases?
Mar 19, 2012As 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 RelatedSQL Plus Viewing A Sequence
Nov 1, 2007After 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 RelatedSQL & PL/SQL :: Sequence Next Value Change?
Jul 25, 2011We Require to Change the Sequence Next Value.
If it possible to Update the Sequence Next Value in Oracle.
SQL & PL/SQL :: Sequence In A Trigger
Dec 27, 2010The 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]....
SQL & PL/SQL :: Sequence Identification
May 29, 2012I 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 RelatedSequence Number In 11g RAC
Sep 17, 2012In 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.
SQL & PL/SQL :: Sequence - Getting Error?
May 13, 2011i 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
PL/SQL :: New Enhancement In 11G Sequence
Oct 22, 2012I 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 .
Create Procedure To Create User In Oracle
Jan 19, 2012I need to create PROCEDURE to create user in oracle
CREATE OR REPLACE PROCEDURE "CREATE_USER_ORACLE8"
(
USER_ID in VARCHAR2,
PASSWORD in VARCHAR2,
ROLES in VARCHAR2,
nReturnCode OUT NUMBER
)
BEGIN
[code].......
Compilation errors for PROCEDURE NOG.CREATE_USER_ORACLE8
Error: PLS-00103: Encountered the symbol "BEGIN" when expecting one of the following:
; is with authid deterministic parallel_enable as
Line: 9
Text: BEGIN
i want that the customer execute PROCEDURE (user_id,password,PROCEDURE )
Inserting Values From A Sequence?
Jun 10, 2011I 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'
SQL & PL/SQL :: Sequence Increments When It Is Not Being Called
Dec 1, 2012I 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] ......
SQL & PL/SQL :: Using Sequence In CASE Statement
Oct 11, 2011Can we use the sequence.nextval,sequence.currval inside case block.
CREATE TABLE EQUALITY_TEST(NUM1 NUMBER, NUM2 NUMBER, SEQ NUMBER);
INSERT INTO EQUALITY_TEST VALUES ( 2 ,0 , NULL);
INSERT INTO EQUALITY_TEST VALUES ( 2 ,2 , NULL);
INSERT INTO EQUALITY_TEST VALUES ( 2 ,2 , NULL);
INSERT INTO EQUALITY_TEST VALUES ( 12 ,2 , NULL);
INSERT INTO EQUALITY_TEST VALUES ( 12 ,12 , NULL);
INSERT INTO EQUALITY_TEST VALUES ( 12 ,12 , NULL);
CREATE SEQUENCE SEQ_TEMP START WITH 100 INCREMENT BY 1;
Now i need to update SEQ column with SEQ_TEMP sequence. When NUM1,NUM2 values are unequal sequence should be incremented otherwise need to use the same sequence number(CURRVAL)
I have tried like this
UPDATE EQUALITY_TEST
SET SEQ=
CASE WHEN NUM1=NUM2
THEN SEQ_TEMP.NEXTVAL
ELSE
SEQ_TEMP.CURRVAL
END ;
SELECT * FROM EQUALITY_TEST;
Output
NUM1NUM2SEQ
120100
222101
322102
4122103
51212104
61212105
But Required Output
NUM1NUM2SEQ
120100
222100
322100
4122101
51212101
61212101
SQL & PL/SQL :: Use Sequence In Prepared Statement?
Jun 30, 2010I'm having some difficulty getting a simple SQL statement to work. It uses bind variables, but has a problem when I introduce a sequence:
INSERT INTO AE_MASTER.STAGE_USAGE (STAGE_USAGE_ID, ANIMAL_USAGE_ID_FK) VALUES (AE_MASTER.AE_GENERIC_SEQ.NEXTVAL, :dummy)
==> ORA-00942: table or view does not exist
But if I remove the sequence, it works:
INSERT INTO AE_MASTER.STAGE_USAGE (STAGE_USAGE_ID, ANIMAL_USAGE_ID_FK) VALUES (1, :dummy)
==> Success
So you might think there is a problem with the sequence, but if I use a name that doesn't exist, it tells me so:
INSERT INTO AE_MASTER.STAGE_USAGE (STAGE_USAGE_ID, ANIMAL_USAGE_ID_FK) VALUES (xxxAE_MASTER.AE_GENERIC_SEQ.NEXTVAL, :dummy)
==> ORA-02289: sequence does not exist
So I'm a bit stumped as to why it's not working! Is it not possible to use sequences with bind variables?
In the past I have gotten around this by using a SELECT statement to get the sequence number then following it with the INSERT statement, but this time I'd really like to get it all done in the same statement if possible.
SQL & PL/SQL :: Sequence Number Not Allowed
Mar 2, 2013I 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"