Cannot insert empty string in oracle table

Question

i am trying to insert data into one of my table.

insert into t_transaction_query_log
    (log_id,
     posting_id,
     fee_id,
     fee_status,
     fee_type,
     pay_mode,
     accounting_date,
     policy_id,
     money_id,
     finish_time,
     source_type,
     fee_amount,
     cr_seg,
     dr_seg,
     product_id' || v_insert_str_cr_seg ||
                  v_insert_str_dr_seg || ')       
    select s_transaction_query_log__lg_id.nextval,
           tg.je_posting_id,
           tg.fee_id,
           tg.fee_status,
           tg.fee_type,
           60,
           tg.accounting_date,
           tg.policy_id,
           tg.currency_id,
           trunc(sysdate,
                 ''dd''),
           2,
           tg.fee_amount,' ||v_str_cr_seg
                   || ',' || v_str_dr_seg || ',
           tg.product_id' || v_insert_str_cr_seg ||
                  v_insert_str_dr_seg || '
      from t_ri_fee_gl tg
     where tg.posted = ''Y''

i have declared v_str_cr_seg,v_str_dr_seg as empty strings i.e v_str_dr_seg varchar(50)='';

before executing this query i am able to append some values to these two strings but in case there is no change in these strings from the declaration i am getting the computed query as

 insert into t_transaction_query_log
    (log_id,
     posting_id,
     fee_id,
     fee_status,
     fee_type,
     pay_mode,
     accounting_date,
     policy_id,
     money_id,
     finish_time,
     source_type,
     fee_amount,
     cr_seg,
     dr_seg,
     product_id)       
    select s_transaction_query_log__lg_id.nextval,
           tg.je_posting_id,
           tg.fee_id,
           tg.fee_status,
           tg.fee_type,
           60,
           tg.accounting_date,
           tg.policy_id,
           tg.currency_id,
           trunc(sysdate,
                 'dd'),
           2,
           tg.fee_amount, , ,
           tg.product_id
      from t_ri_fee_gl tg
     where tg.posted = 'Y'

which in turn results in missing expression error. i have tried using nvl function as

nvl(v_str_cr_seg, '') and `nvl(v_str_cr_seg, null)`

but still i am getting the same error

how can i insert even though v_str_cr_seg and v_str_dr_seg are unchanged i.e empty strings ''

Answer 1

Ora DB: empty string = null enter link description here

Note: Oracle Database currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls.

Answer 2

If this is an insert Statement in the middle of a PL/SQL Routine, then you would not string concatenate. It would rather look like this

       tg.fee_amount, v_str_cr_seg, v_str_dr_seg  

But something from your question tells me that you are composing a string that you want to execute immediate.

In this case you have to make sure you are giving quotes:

       tg.fee_amount,''' ||v_str_cr_seg
               || ''',''' || v_str_dr_seg || ''',
       tg.product_id''' || v_insert_str_cr_seg ||
              v_insert_str_dr_seg || '''

You need to escape the single quote (') with two quotes in a row ('')

But if your strings already come with quotes around it unless null you may want to work with nvl this way

       tg.fee_amount,' ||nvl(v_str_cr_seg,'''''')
               || ',' || nvl(v_str_dr_seg,'''''') || ',

Answer 3

Try using chr(0) instead of ''. Null character is a sign of the end of a string.