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

    Forum Stats

  • 3,681,515 Users
  • 2,238,028 Discussions
  • 7,831,260 Comments

Discussions

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

user5716448user5716448 Posts: 1,670 Silver Badge
edited October 16 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

  • user5716448user5716448 Posts: 1,670 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) Posts: 1,978 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.

Answers

  • Jason_(A_Non)Jason_(A_Non) Posts: 1,978 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
    
    
  • user5716448user5716448 Posts: 1,670 Silver Badge

    Thanks for reply.


    Have worked around,

  • user5716448user5716448 Posts: 1,670 Silver Badge

    Thanks for update

Sign In or Register to comment.