Errors when running the CASTOR to CTA migration sql scripts

Troubleshooting
1

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.

2

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).