0

I have a oracle script to convert table structure from Oracle to Redshift kind of below,

WITH TABLE_SCRIPT AS (
SELECT
    TABLE_NAME,
    COLUMN_NAME,
    CASE
        WHEN DATA_TYPE= 'DATE' THEN 'DATE'
        WHEN DATA_TYPE= 'DATETIME' THEN 'TIMESTAMP'
        WHEN DATA_TYPE LIKE 'TIMESTAMP%' THEN 'TIMESTAMP'
        WHEN DATA_TYPE= 'LONG' THEN 'TEXT'
        WHEN DATA_TYPE= 'NCHAR' THEN 'NCHAR(' || DATA_LENGTH || ')'
        WHEN DATA_TYPE= 'NVARCHAR' THEN 'NVARCHAR(' || DATA_LENGTH || ')'
FROM ALL_TAB_COLUMNS
WHERE
    OWNER= 'SMART_DS'
    AND TABLE_NAME = 'BATCH'
ORDER BY DATA_TYPE,COLUMN_ID
)
SELECT 'CREATE TABLE '||MAX(TABLE_NAME) ||' ('  as text   FROM TABLE_SCRIPT
UNION ALL
SELECT '    '||COLUMN_NAME||' '||REDSHIFT_COLUMN_DEFINITION || ', '  AS TEXT FROM TABLE_SCRIPT 
UNION ALL
SELECT ' );'  AS TEXT FROM dual

When I run this script it has to run perfectly. My problem is ' , ' should not come at the end of second row, how to change that?

CREATE TABLE VERSION 
(
  RELEASE_ID DOUBLE PRECISION NOT NULL , 
  VERSION_ID DOUBLE PRECISION NOT NULL , 
)
APC
  • 144,005
  • 19
  • 170
  • 281
KarthiK
  • 71
  • 2
  • 10
  • 1
    you should also conside the aws schema conversion tool / DMS. https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Source.Oracle.html – Jon Scott Nov 07 '18 at 11:15

2 Answers2

1

N.B. you missed out the end of your case statement in your table_script subquery, so I had to guess what it should be.

You can do this by using listagg, which removes the need to have separate union all'd select statements:

WITH table_script AS
 (SELECT owner,
         table_name,
         column_name,
         data_type,
         CASE
           WHEN data_type = 'DATE' THEN 'DATE'
           WHEN data_type = 'DATETIME' THEN 'TIMESTAMP'
           WHEN data_type LIKE 'TIMESTAMP%' THEN 'TIMESTAMP'
           WHEN data_type = 'LONG' THEN 'TEXT'
           WHEN data_type = 'NCHAR' THEN 'NCHAR(' || data_length || ')'
           WHEN data_type = 'NVARCHAR' THEN 'NVARCHAR(' || data_length || ')'
           ELSE
            data_type
         END redshift_column_definition,
         column_id
  FROM   all_tab_columns
  WHERE  owner = 'SMART_DS'
  AND    table_name = 'BATCH')
SELECT 'create table ' || owner || '.' || table_name || ' (' || chr(10) || listagg(column_name || ' ' || redshift_column_definition, ',' || chr(10)) within GROUP(ORDER BY column_id) || chr(10) || ' );' AS text
FROM   table_script
GROUP  BY owner,
          table_name
ORDER  BY owner,
          table_name;

Example output:

create table SYS.ALL_IDENTIFIERS (
OWNER VARCHAR2,
NAME VARCHAR2,
SIGNATURE VARCHAR2,
TYPE VARCHAR2,
OBJECT_NAME VARCHAR2,
OBJECT_TYPE VARCHAR2,
USAGE VARCHAR2,
USAGE_ID NUMBER,
LINE NUMBER,
COL NUMBER,
USAGE_CONTEXT_ID NUMBER
 );

You'll note that I have added owner into the mix; this means you can create all the create table scripts in one go.

Boneist
  • 22,910
  • 1
  • 25
  • 40
  • i have modified my script.when i run modified script result came in a single line. How do i get this into new line?? and I want to know one more thing, how to add primary keys to this script?? – KarthiK Nov 07 '18 at 10:53
  • did you try copying and pasting from the results? If it still shows on one line after that, you may need to change the `CHR(10)`s to `CHR(13)||CHR(10)`s. – Boneist Nov 07 '18 at 10:57
  • No, I have tried with CHR(13)||CHR(10) result came in a single line – KarthiK Nov 07 '18 at 11:02
  • Where are you running your query? (e.g. SQL*PLUS, TOAD, SQL Developer, etc) – Boneist Nov 07 '18 at 11:07
  • oracle sql developer – KarthiK Nov 07 '18 at 11:09
  • And did you try copying the results and pasting them elsewhere? – Boneist Nov 07 '18 at 11:10
  • I'm using PL/SQL Developer, and the results appear to be on one line in the grid output, but when I copy the results from the grid and paste the results into the editor window / text file, they are correctly formatted on different lines. I think that's what you're also seeing in SQL Developer. – Boneist Nov 07 '18 at 11:55
  • Yes,when i try to paste it in the notepad it is working, Do you know how to get primarkey and foreign key constraints using this script?? – KarthiK Nov 07 '18 at 13:55
  • you would need to query all_constraints to get that info. – Boneist Nov 07 '18 at 14:16
  • Thank you, I will look into that – KarthiK Nov 08 '18 at 08:50
0

If you won't to use ListAgg. I Can suggest you to this query below, I tried it and it worked fine:

WITH
TABLE_SCRIPT
AS
    (  SELECT TABLE_NAME,
              COLUMN_NAME,
              (CASE
                  WHEN DATA_TYPE = 'DATE'
                  THEN
                      'DATE'
                  WHEN DATA_TYPE = 'DATETIME'
                  THEN
                      'TIMESTAMP'
                  WHEN DATA_TYPE LIKE 'TIMESTAMP%'
                  THEN
                      'TIMESTAMP'
                  WHEN DATA_TYPE = 'LONG'
                  THEN
                      'TEXT'
                  WHEN DATA_TYPE = 'NCHAR'
                  THEN
                      'NCHAR(' || DATA_LENGTH || ')'
                  WHEN DATA_TYPE = 'NVARCHAR'
                  THEN
                      'NVARCHAR(' || DATA_LENGTH || ')'
                  ELSE
                    DATA_TYPE
              END) REDSHIFT_COLUMN_DEFINITION
     ,
              ROWNUM
                  RankOfCol
         FROM ALL_TAB_COLUMNS
        WHERE OWNER = 'SMART_DS' AND TABLE_NAME = 'BATCH'
     ORDER BY rankofcol, DATA_TYPE, COLUMN_ID)
(SELECT 'CREATE TABLE ' || MAX (TABLE_NAME) || ' (' AS TEXT
   FROM TABLE_SCRIPT
  UNION ALL
 SELECT ' ' || COLUMN_NAME ||' '||REDSHIFT_COLUMN_DEFINITION || ' ' AS   TEXT  FROM TABLE_SCRIPT  WHERE rankofcol = 1
  UNION ALL
 SELECT ', ' || COLUMN_NAME || ' '||REDSHIFT_COLUMN_DEFINITION ||' ' AS TEXT   FROM TABLE_SCRIPT  WHERE rankofcol > 1
 UNION ALL
 SELECT ' );' AS TEXT FROM DUAL);

Hope this can help you.

Mohamad TAGHLOBI
  • 581
  • 5
  • 11