Home » SQL & PL/SQL » SQL & PL/SQL » How to code this?
How to code this? [message #221576] Tue, 27 February 2007 09:14 Go to next message
syang
Messages: 30
Registered: February 2007
Member
Hi All,

I have to change some of the datatype on certain columns, for example:

ALTER TABLE CBSSTST.accreditation_status MODIFY(LAST_CHG_USER_ID VARCHAR2(80 BYTE));

ALTER TABLE CBSSTST.accrediting_organization MODIFY(LAST_CHG_USER_ID VARCHAR2(80 BYTE));

...

This change will have to touch 60 tables. Is there an easy way to accomplish this? Maybe, PL/SQL can help?

I am new to Oracle stored procedures, can someone enlighteh me as how to start coding this with PL/SQL?

Thanks in advance for your help!
Re: How to code this? [message #221581 is a reply to message #221576] Tue, 27 February 2007 09:35 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Best thing is to generate it from the Oracle meta data. You could either do this in PL/SQL or use a SQL*Plus script:

set echo off
set feedback off
set pagesize 0
set heading off
set trimspool on

spool run_all.sql
select 'ALTER TABLE CBSSTST.'||table_name||' MODIFY(LAST_CHG_USER_ID VARCHAR2(80 BYTE));'
from all_tab_columns
where owner = 'CBSSTST'
and column_name = 'LAST_CHG_USER_ID'
/
spool off

start run_all.sql
Re: How to code this? [message #221586 is a reply to message #221581] Tue, 27 February 2007 09:42 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you've already got data in those columns, then your changes will fail if you are:
1) Changing the datatype.
2) Decreasing the precision of the varchar2s
Re: How to code this? [message #221593 is a reply to message #221576] Tue, 27 February 2007 10:09 Go to previous messageGo to next message
syang
Messages: 30
Registered: February 2007
Member
I have run the script that Cthulhu (thank you!) wrote and the datatype has been changed successfully to VARCHAR2(80) for all the last_chg_user_id columns on 60 tables. Guess what we have data in those tables when I ran Cthulhu's script. I am running Oracle 10g.

It looks that Oracle 10g allows this type of datatype change even we have data populated in the table.

SQL> select count(*) from DMEPOS_CBA_ZIPCODE;

COUNT(*)
----------
377

SQL> desc cbsstst.DMEPOS_CBA_ZIPCODE;
Name Null? Type
----------------- -------- ------------
ZIP_CD NOT NULL CHAR(18)
LAST_CHG_USER_ID VARCHAR2(80)
LAST_CHG_DT DATE
CNTY_NAME VARCHAR2(20)

Thank you all for your input!



Re: How to code this? [message #221595 is a reply to message #221576] Tue, 27 February 2007 10:12 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Having data in the table would not cause a problem if the data type was already VARCHAR2 and you were increasing its size. That's been true in all versions of Oracle.
Re: How to code this? [message #221701 is a reply to message #221595] Wed, 28 February 2007 02:11 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quite true. That's why I left that one off the list of problems. I put my warning up because the OP said he had to change the datatype on the columns, rather than the precision.
Just sloppy wording I guess.
Previous Topic: merging multiple rows
Next Topic: Error in Query
Goto Forum:
  


Current Time: Tue Dec 06 16:26:18 CST 2016

Total time taken to generate the page: 0.13716 seconds