title: No result from a query stored in a VARCHAR2 variable is treated as an empty string or a NULL? - Oracle PL/SQL - Oracle 11g
body: <p>I have a procedure which performs a SELECT INTO and stores the returned result in a VARCHAR2 variable called <code>account_</code>:</p>
  <pre><code>SELECT DISTINCT NVL(XYZ_API.Get_Ref(company, currency, pay_type, order_id), 
                      XYZ_API.Get_Id(company, currency, pay_type, order_id)) account
  INTO account_
  FROM some_table
  WHERE company                     = company_
       AND payment_type             = 'SUPP'
       AND order_id                 = order_id_
       AND payment_date             = pay_date_;
  </code></pre>
  <p><code>company_</code>, <code>order_id_</code>, and <code>pay_date_</code> are all VARCHAR2 variables I am using to filter out the records.</p>
  <p>The result taken into <code>account_</code> is then formatted as follows and stored in another VARCHAR2 variable named <code>giro_</code>:</p>
  <p><code>giro_ := LPAD( TRANSLATE( account_, '1234567890- ','1234567890' ), 16, '0' );</code></p>
  <p>Then I check whether <code>giro_</code> is a number, as follows with a FOR loop.</p>
  <pre><code>FOR i_ IN 1..LENGTH( giro_ ) LOOP
       c_ := ASCII( SUBSTR( giro_ , i_, 1 ) );
       IF ( c_ &lt; ASCII( '0' ) OR c_ &gt; ASCII( '9' ) ) THEN
          RETURN FALSE;
       END IF;
    END LOOP;
  </code></pre>
  <p>I have a scenario where the SELECT query shown above does not pick up any records.</p>
  <p>This eventually introduces an exception in the FOR loop with errors ORA-06502 and ORA-06512.</p>
  <p>As per my understanding the cause is <code>LENGTH( giro_ )</code>.</p>
  <p><code>1..LENGTH( giro_ )</code> is failing as the LENGTH of <code>giro_</code> value (LENGTH being a NULL) cannot be converted into a NUMBER.</p>
  <p>My question is, by this time in this scenario, is <code>giro_</code> an empty string or a NULL?
  What exactly happens here?</p>
  <p>Thanks!</p>