| how to chnage a customer_id [message #382503] |
Thu, 22 January 2009 15:10  |
ORACLE_HELP1
Messages: 8
Registered: January 2009
|
Junior Member |
|
|
iam trying to change a cust_id in a table.The new cust_id that i get should be replaced everywhere the DB and i am giving the samles of my data for cust_id
cust_id..2345
cust_id...45678
cust_id...467890
The new number can be generated using any function.But not the random package from DBMS_RANDOM be used.
i need a code for changing the cust_id to a new_cust_id.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: how to chnage a customer_id [message #382524 is a reply to message #382515] |
Thu, 22 January 2009 16:02  |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
| ORACLE_HELP1 | i have to generate a new_cust_id keeping the old_cust_id too in the DB.
|
Let's imagine that there's a table which contains column named "cust_id", such asSQL> create table test
2 (cust_id number,
3 cust_name varchar2(20)
4 );
Table created.
SQL> insert all
2 into test (cust_id, cust_name) values (123, 'Little')
3 into test (cust_id, cust_name) values (456, 'Foot')
4 select * from dual;
2 rows created.
SQL>
As you'd like to create a new "cust_id" and keep the old one (in the same table?), it appears that you'll have to add a new column into the table:SQL> alter table test add new_cust_id number;
Table altered.
SQL> select * from test;
CUST_ID CUST_NAME NEW_CUST_ID
---------- -------------------- -----------
123 Little
456 Foot
SQL>
| ORACLE_HELP1 | The new number can be generated using any function.But not the random package from DBMS_RANDOM be used.
|
"Any function" is, well, a little bit vague; what does "any" mean? Oracle function? User-created one?
| Quote: | iam not updating any.
|
Oh, yes, you are! Otherwise, there's no way to fill "new_cust_id" in the same record where the original "cust_id" exists.
OK, here's a user-created function (a dummy one - I'll add existing "cust_id" to current minutes and seconds. Records are being updated and - finally - we'll see the result:SQL> create or replace function fun_test (par_id in number)
2 return number is
3 begin
4 return to_number(to_char(sysdate, 'miss')) + par_id;
5 end;
6 /
Function created.
SQL> update test set
2 new_cust_id = fun_test (cust_id);
2 rows updated.
SQL> select * from test;
CUST_ID CUST_NAME NEW_CUST_ID
---------- -------------------- -----------
123 Little 5879
456 Foot 6212
SQL>
So, in a few words:
- alter the table
- create a function
- update records
Though, I might have misunderstood the question. If so, could you explain the problem using more details than you did so far?
|
|
|
|
| Re: how to chnage a customer_id [message #382526 is a reply to message #382503] |
Thu, 22 January 2009 16:31 |
ORACLE_HELP1
Messages: 8
Registered: January 2009
|
Junior Member |
|
|
|
Thanks for the code.I need the new cust_id of the same lenght as the old_cust_id.and if u use the sys minutes and seconds to add this might vary at the time i run the code everytime.i need a function it might be oracle defined or user defined.But i guess we have to use the data encryption method to change the customer_id.If u can show me an example using DATA_ENCRYPTION package that would be great.
|
|
|
|
|
|
| Re: how to chnage a customer_id [message #382576 is a reply to message #382526] |
Fri, 23 January 2009 01:18 |
|
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
| Quote: | I need the new cust_id of the same lenght as the old_cust_id.
|
Why didn't you say so?
| Quote: | if u use the sys minutes and seconds to add this might vary at the time i run the code everytime.
|
You didn't provide any specification; minutes + seconds are here just as an example as you - once again - didn't specify how the new ID should look like.
Besides, how is anyone supposed to know that you will run that code more than once? Do you frequently modify ID values? How does that reflect to foreign key constraints and other possible implications?
| Quote: | i need a function it might be oracle defined or user defined. But i guess we have to use the data encryption method to change the customer_id.
|
Why should ID be encrypted? A sequence might be a good choice for an ID if this number (or character or whatever it is) doesn't tell you any other information (such as ID = M1990BE -> Male born in 1990 Blue Eyes).
Reading what you've said, it appears that the (undocumented) Oracle function REVERSE might do the job - it will produce an ID having the same length as the original one, won't change if run more than once, will "encrypt" data (true, reversing something isn't very sophisticated way of encrypting, but ...).SQL> update test set new_cust_id = to_number(reverse(to_char(cust_id)));
2 rows updated.
SQL> select * from test;
CUST_ID CUST_NAME NEW_CUST_ID
---------- -------------------- -----------
123 Little 321
456 Foot 654
SQL> |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
Iam asking u to help me out