?
Solved

Trigger/Function creation in Oracle regarding

Posted on 2008-10-02
6
Medium Priority
?
510 Views
Last Modified: 2013-12-18
COLUMN1        COLUMN2
8983888          2
8983888             1
8983888             5

Need to sort this first


8983888            1
8983888         2
8983888            5


then I need to sequence COLUMN2


8983888           1
8983888           2
8983888           3


How to do this using a trigger(if possible)?

I am asking about a trigger because they will be inserted(using SQL Loader) and as soon as they are inserted, the tables should be structured according to the above requirement.

If not possible how to do that using a function? ( or ) a sql query?

Thanks in anticipation.
0
Comment
Question by:srikanthradix
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 9

Expert Comment

by:jamesgu
ID: 22630777
so the sequence of a new record is always incremented by 1?

you may use a sequence

CREATE SEQUENCE your_sequence
INCREMENT BY 1
START WITH 1

CREATE OR REPLACE TRIGGER tri_your_table
BEFORE INSERT ON your_table
FOR EACH ROW
BEGIN
      SELECT your_sequence.NEXTVAL
      INTO :new.sequence_column
      FROM dual;
END;
0
 

Author Comment

by:srikanthradix
ID: 22631209
Suppose the data is like this:

COLUMN1        COLUMN2
8983888             2
8983888             1
8983888             5
8983889             4
8983889             1
8983889             5

The Result should be

COLUMN1        COLUMN2
8983888             1
8983888             2
8983888             3
8983889             1
8983889             2
8983889             3

Sorry, I am not being clear.
0
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 750 total points
ID: 22631411
first load the data and then also you can run update command to update it as per your needs :

can you try :

update your_table x
set ( col1, col2 ) =
(  
select y.col1, y.val  from  ( select col1, col2, row_number() over (partition by col1 order by col2 ) val from your_table ) y
where x.col1 = y.col1
and x.col2 = y.col2
  );


0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 27

Assisted Solution

by:sujith80
sujith80 earned 300 total points
ID: 22631417
A trigger will not be able to do that.
Use the following query.
select column1, row_number() over(partition by column1 order by column2) column2
from <your table>;

Open in new window

0
 
LVL 15

Assisted Solution

by:Shaju Kumbalath
Shaju Kumbalath earned 450 total points
ID: 22633048
Follow the steps and try loading
1. Create a table to hold the seq

CREATE TABLE SRL.SEQ_PRM
(
PARAM_NAME VARCHAR2(30 BYTE),
COLUMN_ID NUMBER(8)
);
2. Create a procedure to populate this
 
CREATE OR REPLACE PROCEDURE p_getnewid (
param_nm IN VARCHAR2,
new_id OUT NUMBER
)
IS
begin
BEGIN
SELECT (NVL (column_id, 0) + 1)
INTO new_id
FROM seq_prm
WHERE UPPER (param_name) = UPPER (param_nm)
FOR UPDATE OF column_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
INSERT INTO SEQ_PRM (PARAM_NAME)
VALUES (PARAM_NM);
new_id := 1;
END;
UPDATE seq_prm
SET column_id = new_id
WHERE UPPER (param_name) = UPPER (param_nm);

END;
/
 
3.  Create trigger on ur original table

create or replace trigger x BEFORE insert on table_x
for each row
begin

P_GETNEWID(:NEW.col1,:NEW.col2);

end;
 
Now try to load data
0
 

Author Closing Comment

by:srikanthradix
ID: 31502567
Thanks for solution.
0

Featured Post

How To Reduce Deployment Times With Pre-Baked AMIs

Even if we can't include all the files in the base image, we can sometimes include some of the larger files that we would otherwise have to download, and we can also sometimes remove the most time-consuming steps. This can help a lot with reducing deployment times.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question