Re: converting char to varchar

Here's one solution:

DEFINE OWNER_NAME=<schema_name>

SET HEAD OFF
COLUMN LINE2 NEWLINE SET LINESIZE 132
SET PAGESIZE 9999 SPOOL TOVARCHAR2A.SFS SELECT
  'ALTER TABLE '||TABLE_NAME||' MODIFY (' LINE1,   COLUMN_NAME||' VARCHAR2('||DATA_LENGTH||'));' LINE2 FROM
  DBA_TAB_COLUMNS
WHERE
  OWNER = '&&OWNER_NAME' AND
  DATA_TYPE = 'CHAR'
/

SPOOL OFF SPOOL TOVARCHAR2B.SFS SELECT
  'UPDATE '||TABLE_NAME LINE1,
  'SET '||COLUMN_NAME||' = RTRIM('||COLUMN_NAME||');' LINE2 FROM
  DBA_TAB_COLUMNS
WHERE
  OWNER = '&&OWNER_NAME' AND
  DATA_TYPE = 'CHAR'
/

SPOOL OFF SET HEAD ON
SET LINESIZE 100
SET PAGESIZE 45 @TOVARCHAR2A.SFS
@TOVARCHAR2B.SFS COMMIT; UNDEFINE OWNER_NAME "rockcogar" <rock_cogarNOroSPAM_at_my-deja.com.invalid> wrote in message news:07ad677c.b0fc76ac_at_usw-ex0107-042.remarq.com...
> I have instructions to convert a meduim sized Access 97 database to
> Oracle 8.1.5. I used an excellent tool called "Scriptoria for Oracle"
> by Bunker Hill Software
> (http://www.bunkerhill.com/ScriptoriaPro_forOracle.htm) to do this
> initial conversion.
>
> The problem is that the text type columns are CHAR rather than VARCHAR2
> which works better for compares in WHERE clauses. Obviously the
> solution is to convert all (maybe 200) CHAR columns to VARCHAR2, but I
> think this means multiple ALTER TABLEs and UPDATEs for each one.
>
> QUESTION: What is the best method to convert many CHAR columns to
> VARCHAR2 columns ?
>
> Rock Cogar.
> Radian International LLC.
> Oak Ridge, TN. USA.
>
>
> * Sent from RemarQ http://www.remarq.com The Internet's Discussion Network
*
> The fastest and easiest way to search and participate in Usenet - Free!
>
Received on Tue Jan 11 2000 - 07:56:56 CST