2 Replies Latest reply: Mar 5, 2013 1:00 PM by Solomon Yakobson RSS

    single quotes

    nydba
      DB:11gr2 and 10203
      Os: enterprise Linux/rhel

      hello

      I am trying to generate output into excel using this,

      http://technology.amis.nl/2011/02/19/create-an-excel-file-with-plsql/

      when I run my code with out any single quotes ,no issues

       
      begin 
      as_xlsx.query2sheet( 'SELECT *  FROM clients'); 
      as_xlsx.save( 'TEST_DIR', 'clients_list.xlsx' ); 
      end; 
      / 
      but when I run the below code I run into following errors and i am running from Linux
       
      
      begin 
      as_xlsx.query2sheet( 'SELECT order_id, 
        TO_CHAR(created_date, 'YYYY-MM-DD HH24:MI:SS')            
        DECODE(review_date,NULL,'Ini','Sub/Rec') AS review, 
      FROM orders 
      WHERE 
      TO_CHAR (created_date, 'YYYYMM') IN TO_CHAR (ADD_MONTHS (SYSDATE, -1),'YYYYMM')' ); 
      as_xlsx.save( 'TEST_DIR', 'ord_report.xlsx' ); 
      end; 
      / 
      
      
      
      PLS-00103: Encountered the symbol "YYYYMM" when expecting one of the following: 
      ) , * & = - + < / > at in is mod remainder not rem => 
      <an exponent (**)> <> or != or ~= >= <= <> and or like like2 
      like4 likec as between from using || multiset member 
      submultiset 
      any help is much appreciated

      thanks
        • 1. Re: single quotes
          Solomon Yakobson
          Quote inside a quoted string must be escaped with a second quote (and you also missing a comma before DECODE):
          begin 
          as_xlsx.query2sheet( 'SELECT order_id, 
            TO_CHAR(created_date, ''YYYY-MM-DD HH24:MI:SS''),            
            DECODE(review_date,NULL,''Ini'',''Sub/Rec'') AS review, 
          FROM orders 
          WHERE 
          TO_CHAR (created_date, ''YYYYMM'') IN TO_CHAR (ADD_MONTHS (SYSDATE, -1),''YYYYMM'')' ); 
          as_xlsx.save( 'TEST_DIR', 'ord_report.xlsx' ); 
          end; 
          / 
          Or better use Q-literals
          begin 
          as_xlsx.query2sheet( Q'[SELECT order_id, 
            TO_CHAR(created_date, 'YYYY-MM-DD HH24:MI:SS'),            
            DECODE(review_date,NULL,'Ini','Sub/Rec') AS review, 
          FROM orders 
          WHERE 
          TO_CHAR (created_date, 'YYYYMM') IN TO_CHAR (ADD_MONTHS (SYSDATE, -1),'YYYYMM')]' ); 
          as_xlsx.save( 'TEST_DIR', 'ord_report.xlsx' ); 
          end; 
          / 
          SY.
          • 2. Re: single quotes
            nydba
            thank you