CODEWITHSUNDEEP
oracle-apex
smajo
smajo

Reputation: 61

ORA-01756: quote missing from string </p> - APEX, Oracle, PL/SQL

now I have almost finished my PL / SQL code in APEX. I have been stuck with a mistake since yesterday.

While X < l_date_diff+1 Loop
        l_date_string := l_date_string ||''','''||to_char(TO_DATE(:P2066_DATE_FROM, 'dd.mm.yyyy')+X,'dd.mm.yyyy') ;
        X := X + 1;
End Loop;

l_date_string := substr(l_date_string,3)|| chr(39);
   
   l_script := 'Select * from
                    (Select 
                        pkey, 
                        to_char(createdformat,''dd.mm.yyyy'') business_date, 
                        regexp_substr(statistics, ''business_\w*'') business_statistics 
                    from 
                        gss.business_data 
                    where 
                        statistics like ''%business_%'' 
                        and createdformat between :P2066_DATE_FROM and :P2066_DATE_UNTIL
                     ) ';
   
   
   l_script_pivot := l_script || ' pivot(
                    count(pkey) 
                    for business_date 
                    in ('||l_date_string||') 
                    )';

I built the code step by step and so I could define exactly where the error occurs.

When returning "Return" I now get the error: ORA-20999: Parsing returned query results in "ORA-20999: SQL query could not be parsed.

ORA-06550: row 18, column 6: ORA-01756: quote missing from string

".

If I just return the first part instead of l_script_pivot, i.e. l_script and look at the value of l_script_pivot, the code looks correct. I could even enter it in SQL and get a result. Somewhere I am missing an apostrophe in the compound string l_script_pivot

Upvotes: 0

Views: 125

Answers (1)

Imran
Imran

Reputation: 169

while X < l_date_diff+1
Loop
    l_date_string := l_date_string||','||to_char(TO_DATE(:P2066_DATE_FROM,'dd.mm.yyyy')+X,'dd.mm.yyyy') ;

    X := X + 1;
End Loop;

   l_script := 'Select * from
                (Select 
                    pkey, 
                    to_char(createdformat,"dd.mm.yyyy") business_date, 
                    regexp_substr(statistics, "business_\w*") business_statistics 
                from 
                    gss.business_data 
                where 
                    statistics like "%business_%"
                    and createdformat between :P2066_DATE_FROM and :P2066_DATE_UNTIL
                 ) ';


 l_script_pivot := l_script || ' pivot(
                count(pkey) for business_date   in (l_date_string) 
                )';

Upvotes: 0

Related Questions