Forum Stats

  • 3,757,039 Users
  • 2,251,189 Discussions
  • 7,869,698 Comments

Discussions

oracle q delimiter - ORA-01036 - illegal variable name/number

user5716448
user5716448 Member Posts: 1,737 Silver Badge
edited Oct 16, 2020 8:08PM in SQL & PL/SQL

Hi,


version 10.2.0.4


using sql below get ORA-01036 - illegal variable name/number


 v_sql := q'[

               select to_date('05/10/2020 22:22:43','DD/MM/YYYYHH24:MI:SS')

               from dual]';

                

                dbms_output.put_line(v_sql);  


want to have dynamic sql which has dates such as this - had hoped to use the q delimiter for this but doesn't seem to like.


How can we achieve this with q delimiter- didn't really want to have to use quotes like below a quite alot of dates in the sql concerned.


v_sql := '

               select to_date(''05/10/2020 22:22:43'',''DD/MM/YYYYHH24:MI:SS'')

               from dual';

                

                dbms_output.put_line(v_sql);  


Thanks

Best Answers

  • user5716448
    user5716448 Member Posts: 1,737 Silver Badge
    Accepted Answer

    Thanks for reply.


    using this in pl/sql developer rather than sqlplus.


    Not sure why it thinks they are variables?




  • Jason_(A_Non)
    Jason_(A_Non) Member Posts: 2,036 Silver Trophy
    Accepted Answer

    My response was actually from the Command Window in PL/SQL Developer 14. I ran the same thing in a Test window and it also works fine. It does NOT pick up on the : as being the delimiter/identifier for a bind variable when it scans the code. Looks like it was fixed between your version and the latest.

    user5716448

Answers

  • Jason_(A_Non)
    Jason_(A_Non) Member Posts: 2,036 Silver Trophy

    This works for me

    Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
    
    SQL> set serveroutput on;
    SQL>
    SQL> declare
      2     v_sql   varchar2(100);
      3  begin
      4     v_sql := q'[select to_date('05/10/2020 22:22:43','DD/MM/YYYYHH24:MI:SS') from dual]';
      5     dbms_output.put_line(v_sql);
      6  end;
      7  /
    
    select to_date('05/10/2020 22:22:43','DD/MM/YYYYHH24:MI:SS') from dual
    
    PL/SQL procedure successfully completed
    
    
    Frank Kulash
  • user5716448
    user5716448 Member Posts: 1,737 Silver Badge
    Accepted Answer

    Thanks for reply.


    using this in pl/sql developer rather than sqlplus.


    Not sure why it thinks they are variables?




  • user5716448
    user5716448 Member Posts: 1,737 Silver Badge

    Thanks for reply.


    Have worked around,

  • Jason_(A_Non)
    Jason_(A_Non) Member Posts: 2,036 Silver Trophy
    Accepted Answer

    My response was actually from the Command Window in PL/SQL Developer 14. I ran the same thing in a Test window and it also works fine. It does NOT pick up on the : as being the delimiter/identifier for a bind variable when it scans the code. Looks like it was fixed between your version and the latest.

    user5716448
  • user5716448
    user5716448 Member Posts: 1,737 Silver Badge

    Thanks for update