cancel
Showing results for 
Search instead for 
Did you mean: 

Migration problem

Level 2 Adventurer

Migration problem

Dear  Team,
 
Iam trying to migrate data oracle to postgres, I am using conversion tool ora2pg, data is converted, but I am dumping data into postgres I will get following error message,
 
 

[postgres@localhost daily_jagran]$ psql < VIEW_output_daily_jagran.sql
Password:
SET
SET
ERROR:  syntax error at or near "id"
LINE 7: FROM category a  JOIN cte c ON (c.prior id
                                                ^

=================================================
 
[postgres@localhost daily_jagran]$ cat VIEW_output_daily_jagran.sql
-- Generated by Ora2Pg, the Oracle database Schema converter, version 20.0
-- Copyright 2000-2019 Gilles DAROLD. All rights reserved.
-- DATASOURCE: dbi:Oracle:host=prdb.jagran.com;sid=orcl;port=1521

SET client_encoding TO 'UTF8';

SET search_path = daily_jagran;
\set ON_ERROR_STOP ON

CREATE OR REPLACE VIEW category_tree_vw (nodeid, nodename, parentid, nodelevel) AS WITH RECURSIVE cte AS (
SELECT  a.id,a.identifier,CASE WHEN a.parent_id IS NULL THEN  a.CAT_TYPE_ID  ELSE a.parent_id END  parent_id,1 as level
 FROM category a
 WHERE parent_id is null
  UNION ALL
select a.id,a.identifier,CASE WHEN a.parent_id IS NULL THEN  a.CAT_TYPE_ID  ELSE a.parent_id END  parent_id,(c.level+1)
FROM category a  JOIN cte c ON (c.prior id

UNION
select id, b.name, 0 parent_id, 0
from CATEGORY_TYPE b
where is_active = a.parent_id AND c.1 and is_deleted = a.0
)

) SELECT * FROM cte WHERE is_active = 1 and is_deleted = 0
UNION

select 0,cast('Categories / Sub-categories' as varchar(100)),-1,-1

order by 1
 ;


CREATE OR REPLACE VIEW hp_main_slider (article_id, tags, created_date, language_id, title, bigtitle, summary, slide_path, display_title, thumbnail_path, article_priority, rank) AS SELECT  ARTICLE_ID,TAGS,CREATED_DATE,LANGUAGE_ID,TITLE,BIGTITLE,SUMMARY,SLIDE_PATH,DISPLAY_TITLE,THUMBNAIL_PATH,ARTICLE_PRIORITY,RANK
 FROM (SELECT a.id article_id,
    a.tags,
    TO_CHAR(b.created_date,'dd Mon, yyyy') created_date,
    b.language_id,
    d.title,
    b.title bigtitle,
    b.summary,
    d.path slide_path,
    b.comments display_title,
    d.thumbnail_path,
    a.article_priority,
    rank() over (order by CASE WHEN d.modified_date='' THEN  d.created_date  ELSE d.modified_date END  DESC) rank
  FROM article a,
    article_language b,
    article_media d
  WHERE a.id              = b.article_id
  AND a.id                = d.article_id
  AND a.IS_ACTIVE         = 1
  AND a.is_deleted        = 0
  AND a.deleted_date     IS NULL
  AND b.IS_ACTIVE         = 1
  AND b.is_deleted        = 0
  AND b.deleted_date     IS NULL
  AND d.IS_ACTIVE         = 1
  AND d.is_deleted        = 0
  AND d.DELETED_DATE     IS NULL
  AND d.media_type_id     = 4
  AND (b.expiry_date      > LOCALTIMESTAMP
  OR b.EXPIRY_DATE       IS NULL)
  AND a.ARTICLE_PRIORITY = userenv('client_info')
  order by rank
  ) alias5
WHERE RANK <= 1;

CREATE OR REPLACE VIEW topic_category_tree_vw (nodeid, nodename, parentid, nodelevel) AS WITH RECURSIVE cte AS (
SELECT  a.id,a.identifier,CASE WHEN a.parent_id IS NULL THEN  a.CAT_TYPE_ID  ELSE a.parent_id END  parent_id,1 as level
 FROM category a
 WHERE id =1296817087
  UNION ALL
select a.id,a.identifier,CASE WHEN a.parent_id IS NULL THEN  a.CAT_TYPE_ID  ELSE a.parent_id END  parent_id,(c.level+1)
FROM category a
 JOIN cte c ON (c.prior id

UNION

select id, b.name, 0 parent_id, 0
from CATEGORY_TYPE b
where is_active = a.parent_id AND c.1 and is_deleted = a.0
)

) SELECT * FROM cte WHERE is_active = 1 and is_deleted = 0
UNION

select 0,cast('Categories / Sub-categories' as varchar(100)),-1,-1

order by 1;



[postgres@localhost daily_jagran]$
 
Kindly check and revert back, the issue.
 
Regards,
Mallikarjunarao,
+91-8142923383.
2 REPLIES 2
EDB Team Member

Re: Migration problem

Hi Mallikarjuna,

 

Thanks for writing us over the issue at your end.

 

We would like to let know that the tool ora2pg is not EDB supported Migration Tool.

 

However, we would like you to check the EDB Migration Tool, where you have the option of Migration to the EnterpriseDB Postgres Advanced Server.

 

https://www.enterprisedb.com/edb-docs/d/edb-postgres-migration-portal/user-guides/user-guide/2.3.0/i...

 

https://www.enterprisedb.com/edb-docs/d/edb-postgres-migration-toolkit/user-guides/user-guide/52.0.3...

 

 

Level 2 Adventurer

Re: Migration problem

Dear Team,

 

 

Thanks for replay, I want to get solution of this problem.

I know this is community version related issue, I think somebody know about this issue, how to resolve that?

 

Thanks for replying this issue. Thanks alot.

 

Regards,

Mallikarjunarao,

+91-8142923383.