# Errors when running the CASTOR to CTA migration sql scripts

**URL:** <https://cta-community.web.cern.ch/t/errors-when-running-the-castor-to-cta-migration-sql-scripts/115>\
**Category:** Troubleshooting\
**Created:** [13 December 2021 14:31 UTC](https://cta-community.web.cern.ch/t/errors-when-running-the-castor-to-cta-migration-sql-scripts/115 "2021-12-13T14:31:11Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![george\_patargias](https://cta-community.web.cern.ch/letter_avatar_proxy/v4/letter/g/b9bd4f/32.png) [@george\_patargias](https://cta-community.web.cern.ch/u/george_patargias)\
**Post date:** [13 December 2021 14:31 UTC](https://cta-community.web.cern.ch/t/errors-when-running-the-castor-to-cta-migration-sql-scripts/115/1 "2021-12-13T14:31:12Z")

</div>

Hi Michael,

Posting here what I asked in my email. If you could give us any clue that would be great. Thanks,

We inserted the function code and the above error is resolved but  
now the following INSERT fails (line 33-36 of castorns\_build\_dirsfullpath.sql)

INSERT INTO CTADirsFullPathMigration select a.fileid, a.parent\_fileid, ‘/’ || a.name, a.filemode, a.nlink,  
a.owner\_uid, a.gid, a.filesize, a.atime, a.mtime, a.ctime, a.status, a.fileclass, a.guid, a.csumtype,  
a.csumvalue, a.acl, a.stagertime, a.oncta, b.depth+1, 0 from CTADirsFullPathNames a, CTADirsFullPathMigration b  
where a.parent\_fileid=b.fileid and b.depth=0;

with the error

11:55:56 SQL\> INSERT INTO CTADirsFullPathMigration select a.fileid, a.parent\_fileid, ‘/’ || a.name, a.filemode, a.nlink,  
11:55:56 2 a.owner\_uid, a.gid, a.filesize, a.atime, a.mtime, a.ctime, a.status, a.fileclass, a.guid, a.csumtype,  
11:55:56 3 a.csumvalue, a.acl, a.stagertime, a.oncta, b.depth+1, 0 from CTADirsFullPathNames a, CTADirsFullPathMigration b  
11:55:56 4 where a.parent\_fileid=b.fileid and b.depth=0;  
a.owner\_uid, a.gid, a.filesize, a.atime, a.mtime, a.ctime, a.status, a.fileclass, a.guid, a.csumtype,  
\*  
ERROR at line 2:  
ORA-01722: invalid number

We are not sure which column is this.

We noticed that the table ctadirsfullpathmigration only has 1 row with depth 0

## SQL\> select depth from castor\_ns.ctadirsfullpathmigration; DEPTH

```
     0

```

12:33

but according to the script it should have 2 rows.

---

<div class="post-metadata">

**Author:** ![mdavis](https://cta-community.web.cern.ch/user_avatar/cta-community.web.cern.ch/mdavis/32/16_2.png) [@mdavis](https://cta-community.web.cern.ch/u/mdavis)\
**Post date:** [14 December 2021 15:52 UTC](https://cta-community.web.cern.ch/t/errors-when-running-the-castor-to-cta-migration-sql-scripts/115/2 "2021-12-14T15:52:48Z")

</div>

CTADIRSFULLPATHMIGRATION has 0 rows because the INSERT failed.

The schema you sent me for your CNS\_FILE\_METADATA table does not match the INSERT statement: the STATUS and FILECLASS columns are transposed.

The `invalid number` error is because you are trying to insert the value of `CTADirsFullPathNames.status` (a string) into `CTADirsFullPathMigration.fileclass` (a number).
