CODEWITHSUNDEEP
sqloracle-databaseselectplsqloracle-apex
user2924127
user2924127

Reputation: 6242

Getting the error: "ORA-20999: Failed to parse SQL query! <p>ORA-06550: line 3, column 25: ORA-00936: missing expression

I am using Oracle Apex and creating a report based on returning sql from a plsql body.

This is what my statement looks like:

DECLARE
    l_query varchar2(1000); 
BEGIN
   l_query := 'SELECT ' || :P10_MYVAR || ' from dual ';
   return l_query;
END;

I get the following error message:

ORA-20999: Parsing returned query results in "ORA-20999: Failed to parse SQL query! <p>ORA-06550: line 3, column 25: ORA-00936: missing expression</p>".

If I try without any bind variables it compiles fine:

DECLARE
        l_query varchar2(1000); 
    BEGIN
       l_query := 'SELECT sysdate from dual ';
       return l_query;
    END;

I do not see why this error is happening. If I run the command directly in the database:

SELECT :P10_MYVAR from dual

This runs fine. Why am I getting this error?

Upvotes: 1

Views: 20921

Answers (2)

Madhusudhan Rao
Madhusudhan Rao

Reputation: 1

For Same Error while using Apex Expression following query worked for me

 declare
v_sql varchar2(4000);
begin

  if :P8_TYPE = 'C' then
    return q'~
    select ROWID,
           ID,
           C1,
           TXDATE,
           TRANSACTIONTYPE, 
           DEBIT,
           CREDIT,
           BALANCE 
      from TRANSMASTER
      where 
      TXDATE BETWEEN TO_DATE (:P8_FROMDT, 'mm/dd/yyyy') AND TO_DATE (:P8_TODT, 'mm/dd/yyyy') AND CREDIT > 0; 
    ~';
  else
    return q'~
    select ROWID,
           ID,
           C1,
           TXDATE,
           TRANSACTIONTYPE, 
           DEBIT,
           CREDIT,
           BALANCE 
      from TRANSMASTER
      where 
      TXDATE BETWEEN TO_DATE (:P8_FROMDT, 'mm/dd/yyyy') AND TO_DATE (:P8_TODT, 'mm/dd/yyyy')  AND DEBIT > 0; 
    ~';
  end if;
   
end;

Upvotes: 0

GMB
GMB

Reputation: 222432

Presumably, you meant:

DECLARE
    l_query varchar2(1000); 
BEGIN
   l_query := 'SELECT :P10_MYVAR from dual';
   return l_query;
END;

That is: you want to the parameter name inside the query rather than concatenated with the string.

Upvotes: 6

Related Questions