SQL & PL/SQL :: Using Sequence.NEXTVAL From DUAL Versus In INSERT Statement?

Apr 8, 2013

I am trying to understand the difference between using sequence.NEXTVAL from DUAL as against using it direclty in an INSERT statment.

--Sequence Creation
CREATE SEQUENCE SEQ_ID START WITH 1 MINVALUE 1 NOCYCLE CACHE 500 NOORDER;
--Table1 Creation
Create table TABLEA (COL1 number, COL2 varchar2(10),
constraint COL1_PL primary key (COL1));
--Table2 Creation
Create table TABLEB(COL3 number);
alter table TABLEB add constraint COL1_FK foreign key(COL3) references TABLEA(COL1);

-- Option1 - Using sequence.NEXTVAL from DUAL

DECLARE
v_seq_num NUMBER;
BEGIN
SELECT SEQ_ID.NEXTVAL INTO v_seq_num FROM DUAL;
INSERT INTO TABLEA (COL1, COL2) VALUES (v_seq_num, 'test');
INSERT INTO TABLEB (COL3) VALUES (v_seq_num);
END;

-- Option2 - Using sequence.NEXTVAL in INSERT USING RETURNING INTO clause

DECLARE
v_seq_num NUMBER;
BEGIN
INSERT INTO TABLEA (COL1, COL2) VALUES (SEQ_ID.NEXTVAL, 'test') RETURNING COL1 INTO v_seq_num;
INSERT INTO TABLEB (COL3) VALUES (v_seq_num);
END;

View 9 Replies


ADVERTISEMENT

SQL & PL/SQL :: Select From Dual Versus Equals Operator?

Mar 14, 2011

I have a package function which is wrapped and I cannot see the code.The package function raises an user-defined exception when :

SELECT ABC.*
FROM ABC
WHERE ABC.A = PACK.FUNC(ABC.B,ABC.C)

But it does not raise any exception and the query works absolutely fine generating desired results when :

SELECT ABC.*
FROM ABC
WHERE ABC.A = (SELECT PACK.FUNC(ABC.B,ABC.C) FROM DUAL)

View 6 Replies View Related

Sequence Nextval Storage Information?

Dec 23, 2012

Sequence nextval is a pseodocolumn, but where the value of nextval is stored and why before we use CURRVAL for a sequence in our session, we must first initialize the sequence with NEXTVAL?

View 5 Replies View Related

SQL & PL/SQL :: Set Sequence Nextval As Default To Primary Key Row

Jul 15, 2012

I'm using Oracle 11g and I'm trying to set the sequence nextval as default to Primary Key row, when I create the table, Oracle throws an exception that columns are not allowed. Here is an example.

CREATE TABLE EMP (
EMP_ID VARCHAR2(50) PRIMARY KEY DEFAULT MYSECUENCE.NEXTVAL)

View 10 Replies View Related

TimesTen In-Memory :: Execute Statement SELECT CURRENT_DATE FROM DUAL On 11.2.2?

Sep 24, 2013

We are trying to execute a statement SELECT CURRENT_DATE FROM DUAL on Timesten 11.2.2  . It throws error unknown referenced column error. Command> select current_date from dual; 2211:

Referenced column CURRENT_DATE not foundThe command failed. But the following doc shows the support. 

TimesTen PL/SQL Support: Reference Summary CURRENT_DATE function

Returns the current date in the session time zone. YIn TimesTen this returns the current date in UTC (universal time). TimesTen does not support local time zones.

View 4 Replies View Related

SQL & PL/SQL :: Insert Into Statement Doesn't Insert All Rows Return By Select Statement?

Jan 12, 2011

If i inserted the values in table it gets inserting very few rows only.I dont know y it is?

View 15 Replies View Related

Server Utilities :: Insert Data Without Writing Insert Statement In Oracle?

May 15, 2010

how to insert data in oracle table without writing insert statement in oracle 9i or above. i am not going to write insert all, merge, sqlloder and import data.

View 2 Replies View Related

SQL & PL/SQL :: How To Insert Single Quote In Insert Statement

Feb 24, 2012

INSERT INTO LKP_ASSET_LOCATION (LOCATION) VALUES ('AMERICA'S CUP VILLAGE')

View 2 Replies View Related

SQL & PL/SQL :: Using Sequence In CASE Statement

Oct 11, 2011

Can 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

View 4 Replies View Related

SQL & PL/SQL :: Use Sequence In Prepared Statement?

Jun 30, 2010

I'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.

View 12 Replies View Related

PL/SQL :: Create Sequence Using EXECUTE IMMEDIATE Statement?

Dec 14, 2012

Is 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.

View 10 Replies View Related

SQL & PL/SQL :: Insert Into Table Without Using Sequence ID

Apr 9, 2012

Is it possible to insert new id without sequence id.

For e.g. :

1 , 'BOLT'
2 , 'CHAIN'

i need to insert 3rd and 4th row as

3 , 'screw'
4 , 'driver'

without using sequence (autonumber gen).

View 11 Replies View Related

Update Statement Versus Cursor Based Update

Sep 7, 2010

I have to update 20 and 60 million records of a table. The update statement are

1> 20 million recs
update mycustomer set update_time=add_months(sysdate,240) where seq_num = 1;

commit;

2> 60 million recs
update mycustomer set update_time=sysdate-seq_num where seq_num <> 1;

commit;

Q1> Is there any way to improve performance
Q2> Will parallel dml improve performance
Q2> Would a pl/sql cursor make any difference in speed.

View 1 Replies View Related

PL/SQL :: How To Insert Sequence Numbers In A Table Column

Aug 23, 2012

I am having the emp table :

empno enmae job
1111 ramu S.E
7658 VENU S.S.E
8280 GETA TRAINEE

alter table emp add sno number

for that sno i have to generate 1,2,3,...

View 4 Replies View Related

SQL & PL/SQL :: INSERT Trigger Will Generate Sequence ID From AA001 To AA999 Value

Feb 25, 2013

I am using Oracle 10G version. I need a code base for new Sequence Trigger.

Requirement : As per the request, before INSERT trigger will generate the sequence ID from AA001 to AA999 value. But once the sequence is reached to AA999, the next sequence value will be generated normal (start from AB001 etc..).

View 6 Replies View Related

Using Nextval Outside Of PL/SQL?

Mar 15, 2011

I used the following code to create a sequence

create sequence mg_sites_seq
start with 1
increment by 1
maxvalue 10000
cycle
cache 5;

then in my values clause of an insert without a distinct I had mg_sites_seq.nextval as one of my values but I'm getting an error that says it's not allowed there.I also tried to use it as a default value on the create table description (my preference) and got the same message. When I did the create table I commented out the field in the insert and value clause.

View 1 Replies View Related

SQL & PL/SQL :: Using Sequence To Insert In Child Table Group By A Counter Column

Oct 1, 2011

I found nothing in SQL (all in PL/SQL).I have a table:

create table Parent (pk_id number primary key); --which is filled using sequence seq_Parent.

And I have a child table:

create table Child (rRef number, fk_parent number primary key (rRef, fk_parent);

that I need to insert into Child using seq_parent but I want to insert the same sequence for each group of rRef. I dont know how to do that using SQL not PL/SQL.

View 7 Replies View Related

SQL & PL/SQL :: How To Get Left Padded Sequence Values In Variable To Insert It In Table For ID Creation

Feb 22, 2010

I have a stored proc SP_INSERT_TRAINEES.Here sTraineeNo is provided as input which has count of trainee needs to be inserted in table aaa_foc.user_profile. The sequence is used to generate ids as :

CREATE SEQUENCE AAA_FOC.TRAINEE_ID INCREMENT BY 1 MINVALUE 0 MAXVALUE 999 NOCACHE NOCYCLE NOORDER

I want the values for ids to be inserted as 001,002,003,.......010,011,................099,100,101,............999.So that the values in the table would be like TRAINEE001,002.......

I have tried to use LPAD to it but the values are getting insertes as TRAINEE1,2,3...........

The code is given below:

CREATE OR REPLACE PROCEDURE AAA_FOC9.SP_INSERT_TRAINEES
(sTraineeNo IN NUMBER,
nReturned_O OUT NUMBER)
IS
ln_insert_cnt PLS_INTEGER :=0;
nSequence NUMBER:=0;
[code]......

View 2 Replies View Related

PL/SQL :: Currval Acting Like Nextval

Mar 16, 2013

I’m trying to avoid a cursor solution and I can’t accomplish this using any group by or aggregate/analytical functions. This approach is basically trying to apply a group by while forcing the group by to operate on the data in a specific order(not the group by order). why is currval giving me the nextval we should get somewhere?

Query:

INSERT INTO WAREHOUSE_DEV.STG_COV_SUBPROGRAM_FUN_CS1 (
CASENUMBER, PREV_CASENUMBER, CLIENT_NUMBER,
PREV_CLIENT_NUMBER, PROGRAMNAME, PREV_PROGRAMNAME,
SUBPROGRAMNAME, PREV_SUBPROGRAMNAME, COVERAGE_YEARMONTH,
CLIENT_COVERAGE, PREV_CLIENT_COVERAGE, CS_COUNT)
select CASENUMBER, PREV_CASENUMBER, CLIENT_NUMBER,

[code]...

The key is that when you have cs_count of 1… get nexval, when you have value of -1 get currval, zero is zero… The first row would get nexval…. So 1 the next seven rows should be 1, then next row 0, the next row 2 and the last 5 rows will also be 2.

Query with currval and nextval:

Query:
INSERT INTO WAREHOUSE_DEV.STG_COV_SUBPROGRAM_FUN_CS1 (
CASENUMBER, PREV_CASENUMBER, CLIENT_NUMBER,
PREV_CLIENT_NUMBER, PROGRAMNAME, PREV_PROGRAMNAME,
SUBPROGRAMNAME, PREV_SUBPROGRAMNAME, COVERAGE_YEARMONTH,
CLIENT_COVERAGE, PREV_CLIENT_COVERAGE, CS_COUNT)

[code]...

View 13 Replies View Related

SQL & PL/SQL :: Insert Statement?

May 13, 2011

I have one query i have create one table T1 in this table i have this three colm (EMPNO,ENAME,HIREDATE) and i have to insert raw from this two table T2 and T3 in T2 table colms are (EMPNO,SAL) and T3 table colm are (EMPNO,MGRID) so which query i have to run

View 4 Replies View Related

SQL & PL/SQL :: Null In Insert Statement?

Jul 5, 2010

I wanted to print 'null' when the column value is null. Actually, i am doing something like this

select empno||','||''''||ename||'''''||','||comm||','||sal from emp

It gives the following output for example
7369,'pointers',,200
If I use the above values to form a insert statement it throws
an error. As 'comm' value is not there.

I wish to get something like
7369,'pointers','',200
or
7369,'pointers',null,200
from the above select query

note I dint copy paste the query exactly from my sql*plus session as I am away from my oracle machine

View 14 Replies View Related

SQL & PL/SQL :: Using Subquery In Insert Statement

Jun 5, 2010

I read in a book that you can't use subquery in an insert statement . E.g:

1)insert into dates (date_col) values (select sysdate fom dual) but when i tried using subquery like this:

2)insert into regions values ((select max(region_id)+1 from regions), 'Oce');

This query worked but 1st query didnt.From my assumptions if we try inserting values in table with the subqueries for a particular column as in 1st query , it will throw error but not while inserting values in all columns as in 2nd query.

View 5 Replies View Related

Insert Statement Causing ORA-01858?

Dec 4, 2012

i'm executing a load test using the below statement:

INSERT INTO BPMBI.PPROCINSTANCE SELECT * FROM BPMBI.PPROCINSTANCE_BKP WHERE ROWNUM < 501;
COMMIT;
EXIT;

I've been using the same for a few days without any error while now wehn i try to use the same i get below error:

SQL> insert into pprocinstance select * from pprocinstance_bkp where rownum <= 500;
insert into pprocinstance select * from pprocinstance_bkp where rownum <= 500
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected.

I've been trying to check what the issue is and when i execute the rownum < 140 works fine while rownum < 150 is showing the same error.

View 6 Replies View Related

Concurrency Wait On Insert Statement?

Feb 19, 2013

I am running Oracle RAC 2 nodes 11g R2 on AIX 7.1

I have a table with unique index, and the application is doing inserts/updates into this table.Suddenly and for about half a minute I faced a high concurrency waits on all the processes running these inserts for one node. I saw this high concurrency wait in the top activity screen of the OEM only on one of the nodes. knowing that the processes doing these inserts are running on both nodes.

All what I have that in this half minute I see high concurrency wait in OEM top activity screen related to this insert statement and when I clicked on the insert I found high "enq: TX - index contention". Again this was only on one node.After this half minute everything went back to normal.What could be the reason and how can I investigate it ?

View 3 Replies View Related

PL/SQL :: Using Sqlerrm Directly On Insert Statement?

Oct 3, 2013

Given below a block of code, this code compiled successfully on one DB but returns error on other.DB version is same. I know sqlerrm can not be used directly but how it compiled successfully on one DB. 

declare
l_procedure_name CONSTANT VARCHAR2(100) := 'copy_device_status_tables';
l_procedure_id CONSTANT INTEGER := 301;

[Code].....

Error report:

ORA-06550: line 12, column 75:
PL/SQL: ORA-00984: column not allowed here
ORA-06550: line 11, column 5:
PL/SQL: SQL Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.

View 5 Replies View Related

SQL & PL/SQL :: Insert Statement Which Will Commit Transaction By Itself

May 22, 2010

What is the syntax to write an insert statement which will commit the transaction by it self,not using an exclusive commit statement after wards.

View 2 Replies View Related

PL/SQL :: Exception Handling In Insert Statement

Jul 17, 2012

Here's sample code :

declare
i number;
l_rec number;
cursor c1 is select i from t1;
begin
[code]....

After executing Data must be inserted into t2..If any error is there it should insert into t2 with status 'E' and move on T2

i fflag
---------
11 E
111 Y
1111 Y

The code above is failing after inserting 11 E into t2 table

View 5 Replies View Related

Insert Into Statement - Getting ORACLE Error?

Jan 22, 2012

I am creating the following two tables...no issues here:
CODECREATE TABLE COURSE_SECTION
  (
  Csecid NUMBER(8) CONSTRAINT COURSE_SELECTION_NUMBER_pk PRIMARY Key,
  Cid NUMBER(6) NOT NULL CONSTRAINTS COURSE_SELECTION_Cid_fk REFERENCES COURSE,
  Termid NUMBER(5) NOT NULL CONSTRAINTS COURSE_SELECTION_Termid_fk REFERENCES TERM,
 
[code]...

The issue I am having is actually inserting data into the table:
CODEINSERT INTO ENROLLMENT
  VALUES (100, 1000, 'A' );
INSERT INTO ENROLLMENT
  VALUES (100, 1003, 'A' );

[code]...

But I get an ORACLE error of

ORA-02291- integrity constraint (User1.ENROLLMENT_CSECID_FK) violated - parent key not foundHow can the parent key not be found when I have it declared/created in the above statement?

View 3 Replies View Related

Using Of ROWNUM In Insert Statement Gives Error In Oracle 11g

May 24, 2011

I have a query regarding the use of rownum inside the insert statement.

For example, I have a sample table as: sample1(aa date, bb number);

Insert
INTO sample1
VALUES (SYSDATE, ROWNUM);

this statement is working fine in Oracle 9i but gives error in Oracle 11.2.0.1. The error is ORA-976 ,

Why this error coming in Oracle 11g and how to resolve it?

Our Environment: UNIX AIX 5.3, Oracle 11.2.0.1 database

View 1 Replies View Related

Forms :: How To Insert And Update With Single Statement

Mar 5, 2012

I would like to insert a value if that value is not existing in the table (example for a column which contains date only new dates should be inserted and if the date already exists in the column then it needs to get updated )

example of scenario...

date s1 s2 s3

in the above if the date is new..it should get inserted with the appropriate slot no.(s1,s2,s3) if the date already exists it needs to update the no.in slot no.

View 8 Replies View Related







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