CODEWITHSUNDEEP
sqlplsqlcursor
KLeonine
KLeonine

Reputation: 187

How to display the datatype of a variable in pl-sql

I have a field "DIVISIONS" which has pipe (|) separated NUMBERs. I split the pipe separated values using following cursor in DECLARE section as below. I join it with a table because I need to obtain some other field from the table based on each value I split and add those values. The field in table is NUMBER(38).

FUNCTION foo (divisions IN VARCHAR2)
RETURN NUMBER
IS
      CURSOR splitDiv
      IS
        SELECT ud.* FROM (
          SELECT REGEXP_SUBSTR(divisions, '[^|]+', 1, ROWNUM) AS division
          FROM DUAL
          CONNECT BY LEVEL <= LENGTH (REGEXP_REPLACE(divisions, '[^|]+'))  + 1
          ) d
        JOIN ud
        ON NVL(TO_NUMBER(d.division),0) = NVL(TO_NUMBER(ud.udValue),0);

  divStatusVar NUMBER(38) := 0;

BEGIN

  FOR d IN splitDiv
  LOOP
    divStatusVar := divStatusVar + TO_NUMBER(d.status);
  END LOOP;

  RETURN divStatusVar ;

EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Code : ' || SQLCODE || ' Error : ' || SQLERRM);
    RETURN divStatusVar ;
END foo;

This is how I call the function:

DECLARE
...
status NUMBER(38) := 0;
...
BEGIN
...
status := foo('1234|6789');
...
END;

When I execute the code above, I get following error:

ORA-01722: invalid number ORA-01403: no data found

I am clueless as to why this error is thrown. I want to see the datatype of "d.status". I have access to the database and the table UD, and the datatype of this field is NUMBER(38), but I am not sure if it's still the same after obtaining the rows in Cursor.

Is there any way I can print the datatype of this field/variable? Or is there anything else that I am missing here? Any pointers will be of great help.

Upvotes: 0

Views: 220

Answers (1)

KLeonine
KLeonine

Reputation: 187

Thanks everyone for providing inputs! Here is what happened -

As mentioned in one of the comments on the question, I am loading a csv format input file into the staging table. And then one of the columns in staging table was passed as a parameter to the function FOO. While loading the data I had used following property of SQL Loader.

OPTIONALLY ENCLOSED BY '"'

But in the sample file I was using the values were simply separated by comma WITHOUT double quotes (""). I changed the file to have each value in the file enclosed by doubt quotes, loaded this file in staging table and then executed the above procedure, it's working perfectly fine. So the problem lies not in the code but in the file.

Thanks everyone for taking time to go through my question, and for providing inputs!

Upvotes: 1

Related Questions