CODEWITHSUNDEEP
sqloracle-databasefunctionplsql
olovholm
olovholm

Reputation: 1392

PL/SQL functions leads to "No more data to read from socket error"

I wrote two function to convert datatype in Oracle, and called these in a select statement on a huge table like this: 

SELECT
safe_to_number(sid_day) sid_day, 
safe_to_number(albumid) album_id,
safe_to_number(sid_album) sid_album,
safe_to_number(sid_artist) sid_artist,
safe_to_number(sid_channel) sid_channel 
FROM big_table_with_data;

after the database has been running for some time, I got a "No more data to read from socket error", and I need to restart Oracle SQL developer to again get access to the database.

Why does this error occur, and is there a way to write PL/SQL functions to avoid this error?

The functions I have implemented are: 

CREATE OR REPLACE FUNCTION
safe_to_number (p varchar2)
RETURN number
IS
retval number;

BEGIN
  IF p IS NULL THEN
   retval := null;
  ELSE
    retval := safe_to_number(p);
  END IF;
  return retval;

EXCEPTION
WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Could not change value to number');
    retval := null;
    return retval;

END;

and

create or replace function is_a_timestamp
    ( p_str in varchar2
      , p_mask in varchar2)
    return timestamp
is
    d date;
begin
    d := to_timestamp(p_str, p_mask);
    return d;
exception
    when others then
    DBMS_OUTPUT.PUT_LINE('Could not change value to timestamp');
    return null;
end;

Upvotes: 1

Views: 2248

Answers (1)

Marco Baldelli
Marco Baldelli

Reputation: 3728

At line 11 of function safe_to_number you are recursively calling function safe_to_number itself.

This line:

retval := safe_to_number(p);

should probably be:

retval := to_number(p);

Upvotes: 3

Related Questions