Home » SQL & PL/SQL » SQL & PL/SQL » Need help on changing all number in once
Need help on changing all number in once [message #402944] Wed, 13 May 2009 04:34 Go to next message
carillpower
Messages: 48
Registered: April 2009
Location: malaysia
Member

Hi to all guru's...

i have a doubt here...i kinda need to scramble a number in a column (num_seq) like this example (12345 --> 23456) in each table...so i decide that i can be done using procedure which it only need us to insert the table name, then system will update the certain parameter (num_seq) in the table...

Its kinda like this ::

create or replace procedure num_seq_scramble (table_name varchar2) is


sqlerr_num NUMBER(9);
sqlerr_msg VARCHAR2(100);
err_rec VARCHAR2(40);
tbl_name := 'table_name';

cursor table_cur is
select *
from tbl_name;

tbl_cur table_cur%rowtype;

begin

open table_cur
loop
fetch table_cur into tbl_cur
EXIT WHEN(table_cur%NOTFOUND) OR (table_cur%NOTFOUND IS NULL);

alter table tbl_name set tbl_cur.num_seq = (tbl_cur.num_seq + 11111);

dbms_output.put_line ('Number from table : '||tbl_name || ' already altered');


end loop;
close table_cur;


EXCEPTION
WHEN OTHERS THEN
sqlerr_num := SQLCODE;
sqlerr_msg :=
TO_CHAR(sqlerr_num, 'S999999999')
|| '-'
|| SUBSTR(SQLERRM, 1, 89);

INSERT INTO chu_errors
VALUES (err_rec, sqlerr_msg, p_proc_id, SYSDATE);

COMMIT;

END num_seq_scramble;
/


Could anyone of u check the syntaxs of it whether its roght or wrong...do appreciate the effort to teach sumthing Laughing
Re: Need help on changing all number in once [message #402950 is a reply to message #402944] Wed, 13 May 2009 04:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
...i kinda need to scramble a number in a column

dbms_random

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel

[Updated on: Wed, 13 May 2009 04:49]

Report message to a moderator

Re: Need help on changing all number in once [message #402976 is a reply to message #402944] Wed, 13 May 2009 05:44 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well lets list the points that leap out.

1) the DML command to change the data in a row is UPDATE, not ALTER
2) you don't specify which row to update so even if you did have an update statement, your code would update all the rows in the table for each record returned by the cursor
3) your WHEN OTHERS doesn't re-raise the exception
4) you can't refer to table names dynamically in the way you're trying to
5) you've chosen the slowest possible way of updating the data

I'd throw it all away, and replace it with something like this:
create or replace procedure num_seq_scramble (table_name varchar2) is
BEGIN
  execute immediate 'UPDATE '||table_name||' SET num_seq = num_seq + 11111';
END;
/
Re: Need help on changing all number in once [message #402986 is a reply to message #402976] Wed, 13 May 2009 06:04 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
JRowbottom wrote on Wed, 13 May 2009 11:44

3) your WHEN OTHERS doesn't re-raise the exception



While this is really bad I think the fact that it's currently doing a commit is even worse.

@carillpower

If you really want to log errors (and I generally wouldn't bother) put the logging code in a seperate procedure that's an autonomous_transaction and call that from the exception handler.
And as JRowbottom says re-raise the error afterwards.
Re: Need help on changing all number in once [message #402989 is a reply to message #402986] Wed, 13 May 2009 06:07 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
I think the fact that it's currently doing a commit is even worse.


Yes - I missed that one.
Re: Need help on changing all number in once [message #403374 is a reply to message #402944] Fri, 15 May 2009 04:00 Go to previous messageGo to next message
carillpower
Messages: 48
Registered: April 2009
Location: malaysia
Member

Thanks for all gurus for the reply...really appreciate that...now i have did some changes...there are to data that need to update...the sequence number which it will update to be the employee number which from another column and name where it will update like this (allen --> axxxx)

here's are the code that i've already made... please feel free to share your thought and skills Razz
CREATE OR REPLACE PROCEDURE member_number_scramble (table_name varchar2) is

  CURSOR tbl_name
  IS SELECT member_num, emp_id, first_name FROM table_name;

  tbl_cur tbl_name%ROWTYPE; 

BEGIN

OPEN tbl_name;

FETCH tbl_name INTO tbl_cur;
LOOP
  execute immediate 'UPDATE '||table_name||' SET tbl_cur.member_num = tbl_cur.emp_id ,
  tbl_cur.first_name = to_char((substr(tbl_cur.first_name,2)),'x')';

END LOOP;

CLOSE tbl_name;
END;
/

[EDITED by LF: applied [code] tags]

[Updated on: Fri, 15 May 2009 04:03] by Moderator

Report message to a moderator

Re: Need help on changing all number in once [message #403411 is a reply to message #403374] Fri, 15 May 2009 07:39 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Read up on cursor loops. Explicitly opening-looping-fetching-closing is soooo eeh.. 1980's??


Use
FOR <rec> IN <cursor> LOOP
...
END LOOP

instead
Re: Need help on changing all number in once [message #403414 is a reply to message #403411] Fri, 15 May 2009 07:52 Go to previous message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Frank wrote on Fri, 15 May 2009 14:39
Read up on cursor loops. Explicitly opening-looping-fetching-closing is soooo eeh.. 1980's??


Use
FOR <rec> IN <cursor> LOOP
...
END LOOP

instead


Definitely liked the eighties Razz
And history repeats itself, so Razz

But all in all your suggestion makes sense!
Previous Topic: CONNECT BY Issue
Next Topic: Subtract Positive values to negative values
Goto Forum:
  


Current Time: Mon Dec 05 06:46:38 CST 2016

Total time taken to generate the page: 0.06440 seconds