ORACLE Error ORA-01723: zero-length columns are not allowed

[na1004dang]

Expected behavior

successful running 'achilles' command on Achilles 1.6.0 (master)
with oracle database

DBMS:
oracle

Error:
java.sql.SQLSyntaxErrorException: ORA-01723:  zero-length columns are not allowed


SQL:
--HINT DISTRIBUTE_ON_KEY(stratum_1)
CREATE TABLE ckwnf8oys_tmpach_0
 AS
SELECT
0 as analysis_id,  CAST('My Source Name' AS VARCHAR(255)) as stratum_1, CAST('1.6' AS VARCHAR(255)) as stratum_2, 
CAST(SYSDATE AS VARCHAR(255)) as stratum_3,
null as stratum_4, null as stratum_5,
COUNT(distinct person_id) as count_value

FROM
CDM.PERSON 

R version:
R version 3.5.1 (2018-07-02)

Platform:
x86_64-w64-mingw32

Attached base packages:
- stats
- graphics
- grDevices
- utils
- datasets
- methods
- base

Other attached packages:
- Achilles (1.6)
- OhdsiRTools (1.5.5)
- httr (1.3.1)
- openxlsx (4.1.0)
- rjson (0.2.20)
- DatabaseConnector (2.1.4)
- SqlRender (1.5.2)
- devtools (1.13.6)

Actual behavior

run 'achilles' failed

Steps to reproduce behavior

1. use oracle database
2. run achilles(connectionDetails, cdmDatabaseSchema = ...)

[t-abdul-basser]

Please include the full achilles() function invocation (with sensitive date such as passwords masked or pseudonymized as necessary).

[chrisknoll]

I think we've seen this before:

0 as analysis_id,  CAST('My Source Name' AS VARCHAR(255)) as stratum_1, CAST('1.6' AS VARCHAR(255)) as stratum_2, 
CAST(SYSDATE AS VARCHAR(255)) as stratum_3,
null as stratum_4, null as stratum_5,
COUNT(distinct person_id) as count_value

it can't figure out what 'null' is supposed to be. Ths fix is likely to cast the null in stratum_4 and null from stratum 5 as a varchar(255) (or whatever the stratum_4, stratum_5 column type is in the achilles_results table.

[na1004dang]

@t-abdul-basser
this is my achilles setting.

achilles(connectionDetails, 
         cdmDatabaseSchema = cdmDatabaseScheme, 
         resultsDatabaseSchema=resultsDatabaseScheme,
         vocabDatabaseSchema = vocabDatabaseScheme,
         numThreads = 1,
         sourceName = "My Source Name", 
         cdmVersion = "5",
         runHeel = FALSE,
         runCostAnalysis = FALSE)

[na1004dang]

@chrisknoll
thanks for reply. as your comment, CAST(null AS VARCHAR(255)) as stratum_4 will best solution i think.
so how(where) can i fix that code in Achilles R code? (null -> cast(null as varchar(255))
maybe that query is auto-generated by sqlRender package.

[alondhe]

@na1004dang: In 1.6, the queries are split into their own files, and each one uses a "select into" pattern in which there could be nulls hard-coded in as you had in your first post. I will create a fix for this, I estimate it'll be ready for you to test by Friday, 14 Sept.

[alondhe]

Hi @na1004dang , can you try this version: https://github.com/alondhe/Achilles

You can try devtools::install_github("alondhe/Achilles") to install it.

[na1004dang]

@alondhe
thanks for your help. but ORA-01723 error still occurring.

Error:
java.sql.SQLSyntaxErrorException: ORA-01723: zero-length columns are not allowed


SQL:
--HINT DISTRIBUTE_ON_KEY(stratum_1)
CREATE TABLE ohxpxvf0s_tmpach_dist_0
 AS
SELECT
0 as analysis_id, CAST('My Source Name' AS VARCHAR(255)) as stratum_1, 
cast(null as varchar(255)) as stratum_2, cast(null as varchar(255)) as stratum_3, cast(null as varchar(255)) as stratum_4, cast(null as varchar(255)) as stratum_5,
COUNT(distinct person_id) as count_value, 
  null as min_value,
	null as max_value,
	null as avg_value,
	null as stdev_value,
	null as median_value,
	null as p10_value,
	null as p25_value,
	null as p75_value,
	null as p90_value

FROM
CDM.PERSON 

[alondhe]

Ah! I just need to cast all of the other nulls. I'll adjust and push a new commit.

[alondhe]

@na1004dang I pushed a new commit, in which I cast all null values in the staging SQL tables to their eventual data type. Can you try installing again?

devtools::install_github("alondhe/Achilles")

[na1004dang]

@alondhe thanks for your help. Achilles no longer occur ORA-01723 error.
(but executing another SQL keeps hanging almost two days.. )
by the way, ORA-01723 error is solved. thank you!

[alondhe]

How many threads are you using? It may be better to stick with 1 when using Oracle.

[mgweiner]

What a timely question and answer! I was stuck with the same problem, posted to the forum site and was directed here (thanks Eunsil_YOON). The new version of the Achilles R package worked to a point, but after completing the Achilles results, it moved on to the Concept Hierarchy creation, producing the familiar zero-length error:

Executing SQL took 0.563 secs
Done. Achilles results can now be found in schema mark
Executing Concept Hierarchy creation. This could take a while
Connecting using Oracle driver

• using THIN to connect
  | | 0%Error: Error executing SQL:
  java.sql.SQLSyntaxErrorException: ORA-01723: zero-length columns are not allowed

Below is the output of the error report showing the familiar uncasted null column

"null as concept_hierarchy_type,"

👍
DBMS:
oracle

Error:
java.sql.SQLSyntaxErrorException: ORA-01723: zero-length columns are not allowed

SQL:
--HINT DISTRIBUTE_ON_KEY(concept_id)
CREATE TABLE suj9wqrzs_tmpach_ch_condition
AS
SELECT
snomed.concept_id,
snomed.concept_name AS concept_name,
'Condition' AS treemap,
null as concept_hierarchy_type,
pt_to_hlt.pt_concept_name as level1_concept_name,
hlt_to_hlgt.hlt_concept_name as level2_concept_name,
hlgt_to_soc.hlgt_concept_name as level3_concept_name,
soc.concept_name AS level4_concept_name

FROM
(SELECT concept_id,
concept_name
FROM mark.concept
WHERE domain_id = 'Condition'
) snomed
LEFT JOIN
(SELECT c1.concept_id AS snomed_concept_id,
max(c2.concept_id) AS pt_concept_id
FROM mark.concept c1
INNER JOIN
mark.concept_ancestor ca1
ON c1.concept_id = ca1.descendant_concept_id
AND c1.domain_id = 'Condition'
AND ca1.min_levels_of_separation = 1
INNER JOIN
mark.concept c2
ON ca1.ancestor_concept_id = c2.concept_id
AND c2.vocabulary_id = 'MedDRA'
GROUP BY c1.concept_id
) snomed_to_pt
ON snomed.concept_id = snomed_to_pt.snomed_concept_id

LEFT JOIN
(SELECT c1.concept_id      AS pt_concept_id,
   c1.concept_name    AS pt_concept_name,
   max(c2.concept_id) AS hlt_concept_id
 FROM mark.concept c1
   INNER JOIN
   mark.concept_ancestor ca1
     ON c1.concept_id = ca1.descendant_concept_id
        AND c1.vocabulary_id = 'MedDRA'
        AND ca1.min_levels_of_separation = 1
   INNER JOIN
   mark.concept c2
     ON ca1.ancestor_concept_id = c2.concept_id
        AND c2.vocabulary_id = 'MedDRA'
 GROUP BY c1.concept_id, c1.concept_name
 ) pt_to_hlt
  ON snomed_to_pt.pt_concept_id = pt_to_hlt.pt_concept_id

LEFT JOIN
(SELECT c1.concept_id      AS hlt_concept_id,
   c1.concept_name    AS hlt_concept_name,
   max(c2.concept_id) AS hlgt_concept_id
 FROM mark.concept c1
   INNER JOIN
   mark.concept_ancestor ca1
     ON c1.concept_id = ca1.descendant_concept_id
        AND c1.vocabulary_id = 'MedDRA'
        AND ca1.min_levels_of_separation = 1
   INNER JOIN
   mark.concept c2
     ON ca1.ancestor_concept_id = c2.concept_id
        AND c2.vocabulary_id = 'MedDRA'
 GROUP BY c1.concept_id, c1.concept_name
 ) hlt_to_hlgt
  ON pt_to_hlt.hlt_concept_id = hlt_to_hlgt.hlt_concept_id

LEFT JOIN
(SELECT c1.concept_id      AS hlgt_concept_id,
   c1.concept_name    AS hlgt_concept_name,
   max(c2.concept_id) AS soc_concept_id
 FROM mark.concept c1
   INNER JOIN
   mark.concept_ancestor ca1
     ON c1.concept_id = ca1.descendant_concept_id
        AND c1.vocabulary_id = 'MedDRA'
        AND ca1.min_levels_of_separation = 1
   INNER JOIN
   mark.concept c2
     ON ca1.ancestor_concept_id = c2.concept_id
        AND c2.vocabulary_id = 'MedDRA'
 GROUP BY c1.concept_id, c1.concept_name
 ) hlgt_to_soc
  ON hlt_to_hlgt.hlgt_concept_id = hlgt_to_soc.hlgt_concept_id

LEFT JOIN mark.concept soc
  ON hlgt_to_soc.soc_concept_id = soc.concept_id 

R version:
R version 3.5.1 (2018-07-02)

Platform:
x86_64-w64-mingw32

Attached base packages:

• stats
• graphics
• grDevices
• utils
• datasets
• methods
• base

Other attached packages:

• Achilles (1.6)
• OhdsiRTools (1.5.5)
• httr (1.3.1)
• openxlsx (4.1.0)
• rjson (0.2.20)
• DatabaseConnector (2.2.0)
• SqlRender (1.5.2)
• devtools (1.13.6)

[na1004dang]

@alondhe I use 1 thread for achilles (numThreads = 1)
when I SELECT v$lock table, i found SQL_TEXT below.

--HINT DISTRIBUTE_ON_KEY(stratum_1) 
CREATE TABLE x786kodxs_tmpach_110  
AS
SELECT 110 as analysis_id,    
CAST(t1.obs_month AS VARCHAR(255)) as stratum_1,  
cast(null as varchar(255)) as stratum_2, 
cast(null as varchar(255)) as stratum_3, 
cast(null as varchar(255)) as stratum_4, 
cast(null as varchar(255)) as stratum_5,  
COUNT(distinct op1.PERSON_ID) as count_value  
FROM CDM_DEV.observation_period op1 
join  
(SELECT DISTINCT      
EXTRACT(YEAR FROM observation_period_start_date)*100 
+ EXTRACT(MONTH FROM observation_period_start_date) AS obs_month,     
TO_DATE(TO_CHAR(EXTRACT(YEAR FROM observation_period_start_date),'0000')||'-'||
TO_CHAR(EXTRACT(MONTH FROM observation_period_start_date),'00')||'-'||
TO_CHAR(1,'00'), 'YYYY-MM-DD')     AS obs_month_start,     
LAST_DAY(observation_period_start_date) AS obs_month_end   
FROM CDM_DEV.observation_period  ) t1 
on op1.observation_period_start_date <= t1.obs_month_start  
and op1.observation_period_end_date >= t1.obs_month_end 
group by t1.obs_month