Home » SQL & PL/SQL » SQL & PL/SQL » how to chnage a customer_id (TOAD)
how to chnage a customer_id [message #382503] Thu, 22 January 2009 15:10 Go to next message
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 #382504 is a reply to message #382503] Thu, 22 January 2009 15:15 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
ou have table & provided us no DDL for it.
You have data & provided us no DML for test data.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you.

You have not shown us via CUT & PASTE what you actually did or how Oracle responded.

You have not provided any expected output.
Re: how to chnage a customer_id [message #382507 is a reply to message #382504] Thu, 22 January 2009 15:24 Go to previous messageGo to next message
ORACLE_HELP1
Messages: 8
Registered: January 2009
Junior Member
i checking for the code.i didnot do any.

consider table A has cust_id column and we need to change the data in it to a new cust_id
Re: how to chnage a customer_id [message #382509 is a reply to message #382503] Thu, 22 January 2009 15:27 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>i didnot do any.
Then we can & should do the same.
Re: how to chnage a customer_id [message #382512 is a reply to message #382503] Thu, 22 January 2009 15:32 Go to previous messageGo to next message
ORACLE_HELP1
Messages: 8
Registered: January 2009
Junior Member
Thank u so much if u can help me in doing this.
Re: how to chnage a customer_id [message #382514 is a reply to message #382503] Thu, 22 January 2009 15:33 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
UPDATE .......

now fill in the dots.

Re: how to chnage a customer_id [message #382515 is a reply to message #382503] Thu, 22 January 2009 15:41 Go to previous messageGo to next message
ORACLE_HELP1
Messages: 8
Registered: January 2009
Junior Member
iam not updating any.i have to generate a new_cust_id keeping the old_cust_id too in the DB.
Re: how to chnage a customer_id [message #382517 is a reply to message #382503] Thu, 22 January 2009 15:45 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>The new cust_id that i get should be replaced everywhere the DB
This sure seems to me to require an UPDATE; since UPDATE is what does replacement.

You have table & provided us no DDL for it.
You have data & provided us no DML for test data.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you.

You have not shown us via CUT & PASTE what you actually did or how Oracle responded.

You have not provided any expected output.
Re: how to chnage a customer_id [message #382518 is a reply to message #382503] Thu, 22 January 2009 15:47 Go to previous messageGo to next message
ORACLE_HELP1
Messages: 8
Registered: January 2009
Junior Member
ok.lets first write the code for changing the customer_id to a new one.
Re: how to chnage a customer_id [message #382519 is a reply to message #382503] Thu, 22 January 2009 15:49 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>ok.lets first write the code for changing the customer_id to a new one.
OK, I agree. Please do so.
Re: how to chnage a customer_id [message #382520 is a reply to message #382503] Thu, 22 January 2009 15:54 Go to previous messageGo to next message
ORACLE_HELP1
Messages: 8
Registered: January 2009
Junior Member
Sad Iam asking u to help me out
Re: how to chnage a customer_id [message #382521 is a reply to message #382503] Thu, 22 January 2009 15:55 Go to previous messageGo to next message
ORACLE_HELP1
Messages: 8
Registered: January 2009
Junior Member
iam asking u to help me out
Re: how to chnage a customer_id [message #382523 is a reply to message #382503] Thu, 22 January 2009 16:00 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
Forgive this pop quiz.

Do you know how to spell SQL?

You have table & provided us no DDL for it.
You have data & provided us no DML for test data.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you.

You have not shown us via CUT & PASTE what you actually did or how Oracle responded.

You have not provided any expected output.

Re: how to chnage a customer_id [message #382524 is a reply to message #382515] Thu, 22 January 2009 16:02 Go to previous messageGo to next message
Littlefoot
Messages: 20888
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 as
SQL> 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 Go to previous messageGo to next message
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 #382559 is a reply to message #382526] Fri, 23 January 2009 00:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
DBMS_CRYPTO

Please read OraFAQ Forum Guide and don't use IM speak.

Regards
Michel

[Updated on: Fri, 23 January 2009 00:41]

Report message to a moderator

Re: how to chnage a customer_id [message #382576 is a reply to message #382526] Fri, 23 January 2009 01:18 Go to previous message
Littlefoot
Messages: 20888
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>
Though, at it is undocumented, and if you choose to use it, perhaps you should create your own REVERSE function because there's no guarantee that it will exist in future Oracle versions.
Previous Topic: INVALID DATATYPE for Collection
Next Topic: new and old in triggers
Goto Forum:
  


Current Time: Fri Dec 02 20:32:44 CST 2016

Total time taken to generate the page: 0.15925 seconds