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


ADVERTISEMENT

SQL & PL/SQL :: Variables In Loop Procedure

Feb 15, 2010

PROCEDURE CALCULATE_CASH_REBAL( P_Account_id IN VARCHAR2,
P_Txn_Ccy IN VARCHAR2,
P_Allocation IN VARCHAR2,
l_lty_id IN VARCHAR2 ) IS
l_balance_fmt NUMBER := 0;
BEGIN
[code]....

it should be updating l_balance_fmt field for all the records in the loop. my question is when it loops to the next cash txn record, will it take the previous value of l_balance_fmt ? it should start again and take 0 as the balance_fmt and add to that..

View 3 Replies View Related

PL/SQL Loop Types - Variables Incrementing Together

Nov 8, 2012

set server output on;

DECLARE
num number(1) :=1;
num2 number(1) := 1;
BEGIN
WHILE num < 5 LOOP
[code]...........

the output for this loop is:
1+1=2
2+2=4
3+3=6
4+4=8

this is what I wanted. Both variables are incrementing together. However, when I try to use a FOR loop to do the same thing:

set serveroutput on;

DECLARE
num number(1) :=1;
num2 number(1) := 1;
BEGIN
FOR i in 1..4 LOOP
[code].......

I get the result:

1+1=2
1+2=3
1+3=4
1+4=5
2+5=7
2+6=8
2+7=9
2+8=10
3+9=12
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at line 8

I understand the output means the inner loop is running its course before the outer loop is started and then continuing on to exceed its limit, but I don't understand why this is or how to make it so I get the same results as the WHILE loops.

View 4 Replies View Related

Loop - Two Variables (username / Password) Not Clearing Out?

Apr 15, 2008

The idea of the program below is to create a sql "module" (just a .sql file) that will loop and ask for a user id until the user enters the correct one. Here is the code I have thusfar:

set serveroutput on;
accept username prompt 'Enter user name here: ';
accept password prompt 'Enter password here: ';

declare
usernameCONFIRM VARCHAR(30);
passwordCONFIRM VARCHAR(30);
begin
select USERID into usernameCONFIRM from GAMEUSER where USERID = '&username';

[code]...

The main problem I'm seeing is that the two variables I ask for (username and password) aren't clearing out.

View 12 Replies View Related

SQL & PL/SQL :: Creating Global Variables In Sqlplus On 11g

May 4, 2011

I have taken some procedures code from SVN repository.These procedures are split into different schema. There are some client and common parameters used in the from clause of table in these sp.for example

select *
from client.emp;

during the compling procedure, we replace the client value into some schema name.

select *
from scott.emp;

The client and common parameter should be different for every schema.

Is any possible to create global varaible for client and common parameter and subsitute value in it.i.e without editing client and common parameter?

View 8 Replies View Related

SQL & PL/SQL :: Usage Of Loop - Creating A Procedure With 4 Input Parameters

Jun 6, 2012

I have a Table with 4 columns and I am creating a procedure with 4 input parameters and inserting into the table as 4 parameters and how to use loop to insert multiple records.The data to be taken from excel.please suggest how to use the loop to insert multiple records.

create or replace procedure PRC_add_data(
P_Emp_No varchar2,
P_Member_Name varchar2,
P_IDvarchar2,
P_UHID varchar2
)
is
BEGIN
INSERT INTO UploadData (Emp_No,Member_Name,ID,UHID) values (P_Emp_No,P_Member_Name,P_ID,P_UHID)
END;
/

View 6 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

SQL & PL/SQL :: How Many Maximum Number Of Bind Variables

Mar 10, 2010

How many maximum number of bind variables,can we use in Execute Immediate.

View 2 Replies View Related

SQL & PL/SQL :: Creating Sequence On Duplicate Data Through Query

Nov 18, 2011

I have a table with three columns mentioned below :

Col1 Col2 Col3
121
221
331
431
531
622
722

Col1 is the primary key.I need a sql query which will show the data set mentioned below :

Col1 Col2 Col3 Seq
121 1
221 1
331 2
431 2
531 2
622 3
722 3

I need all the 7 records to be displayed.

View 16 Replies View Related

SQL & PL/SQL :: Creating Sequence - Select Returns Integer

Oct 18, 2010

im creating sequences for an already created db and need to set MINVALUE to sequences.

create sequence tb_1
minvalue X
start with X;

so, X == select Max(id_tb1) from tb_1.

I need that select returns an integer.

How can I do this??

View 11 Replies View Related

Client Tools :: Creating Sequence Value After Data Import?

Feb 23, 2011

I have an excel sheet as follows: I have to import the data in the excel sheet to oracle database table through TOAD.

name1 name2 name3
ABS SDFG FHTR
DFR GHJK HJK
... ... ...

Now, I need an id column along with these three that would have id numbers like that of a sequence. That means, suppose I have 1000 records in the excel sheet, then the table should have as many numbers automatically after data import.

View 18 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 :: 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 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 :: 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







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