4 Replies Latest reply on Jan 26, 2011 6:22 PM by LKBrwn_DBA

    ORA-00917

    msb
      Hi

      i am trying to insert the values like this

      insert into process
      values (1,'stock','select id,e_name,case when email is null Then ' 'else 'masked' end as email address,status from stock',sysdate);

      it shows an error like
      Error report:
      SQL Error: ORA-00917: missing comma
      00917. 00000 - "missing comma"

      how can i rectify this
        • 1. Re: ORA-00917
          SomeoneElse
          as email address

          You should also always explicitly specify the columns you are inserting into, not just the values.
          • 2. Re: ORA-00917
            Frank Kulash
            Hi,

            It looks like the 3rd value is a string that contains single-quotes.
            To include single-quotes inside a string literal, use two of them together:
            INSERT INTO process (col1, col2, col3, col4)
            VALUES ( 1
                   , 'stock'
                   , 'select id,e_name,case when email is null Then '' ''else ''masked'' end as email address,status from stock'
                   , SYSDATE
                   )
            ;
            or use Q-notation:
            INSERT INTO process (col1, col2, col3, col4)
            VALUES ( 1
                   , 'stock'
                   , Q'[select id,e_name,case when email is null Then ' 'else 'masked' end as email address,status from stock]'
                   , SYSDATE
                   )
            ;
            • 3. Re: ORA-00917
              msb
              i couldnot understand you are telling like this

              insert into process(id,name,query,date)
              values (1,'stock','select id,e_name,case when email is null Then ' 'else 'masked' end as email address,status from stock',sysdate);
              • 4. Re: ORA-00917
                LKBrwn_DBA
                Maybe you mean something like this:
                INSERT INTO process
                SELECT id
                     , e_name
                     , CASE
                         WHEN email IS NULL THEN ' '
                         ELSE 'masked'
                       END AS email_address
                     , status
                     , SYSDATE
                FROM   stock;  
                :p