Forum Stats

  • 3,827,736 Users
  • 2,260,814 Discussions
  • 7,897,363 Comments

Discussions

FN_WHO_AM_I gives ORA-06502: PL/SQL: nummeric or value error: character to number conversion error

User_I32GM
User_I32GM Member Posts: 1 Green Ribbon

At first let me tell everybody that I'm not very skilled in programming.


I have created a package spec & body.

I copied the code of the function FN_WHO_AM_I of GARBUYA.

(https://community.oracle.com/tech/developers/discussion/1051771/efficient-who-am-i-and-who-called-me)

Added one function of my own, here it is:

------------------------------------------------

create or replace package body VGG.WAI is


 function Module_Info return varchar2 is

  Tmp_Object varchar2(256 char) := vgg.wai.FN_WHO_AM_I(p_lvl => 1);

  Module   varchar2(30 char);

 begin

  Module := Substr(Tmp_Object, 1 + Instr(Tmp_Object ,'.' ,-1));

  return(Module);

 end;


FUNCTION FN_WHO_AM_I ( p_lvl NUMBER DEFAULT 0) RETURN VARCHAR2

IS

/***********************************************************************************************

FN_WHO_AM_I returns . . . . . . . etc etc . . . .

------------------------------------------------

I Compiled succesfuly.

Start testing my function like this:

------------------------------------------------

begin

 -- Call the function

 :result := vgg.WAI.Module_Info;

end;

------------------------------------------------

It gives an error ORA-06502 on the following line of the function FN_WHO_AM_I:

  v_line_nbr := TO_NUMBER(SUBSTR(v_stack, 1, instr(v_stack, ' ') - 1)); -- get line number

Because it is not my code I don't like to change anything.

variable v_stack has a value '  OBJECT   LINE OBJECT'.

two spaces followed by 'OBJECT'

followed by three spaces followed by 'LINE OBJECT'.

I was thinking of trimming the result of substr and then testing for null.

If so making it 0.

Erik Alers

Tagged:

Answers

  • BluShadow
    BluShadow Member, Moderator Posts: 41,978 Red Diamond

    It gives an error ORA-06502 on the following line of the function FN_WHO_AM_I:

      v_line_nbr := TO_NUMBER(SUBSTR(v_stack, 1, instr(v_stack, ' ') - 1)); -- get line number

    Because it is not my code I don't like to change anything.

    variable v_stack has a value ' OBJECT   LINE OBJECT'.

    two spaces followed by 'OBJECT'

    followed by three spaces followed by 'LINE OBJECT'.

    I was thinking of trimming the result of substr and then testing for null.

    If so making it 0.


    Not sure why that would give you the error.

    instr(v_stack,' ') on the string you describe would give a value of 1 because of the first space at the start of the string, and subtracting 1 from that gives 0.

    substr(v_stack,1,0) would give you null because you're not getting any characters.

    to_number(null) would result in null

    It would help if you could provide some example strings that you want to extract the line number from. If it's the only number in the string, or the first one in the string, then it could be just as easy as using regular expressions:

    v_line_nbr := to_number(regexp_substr(v_stack, '\d+'));
    


  • Hub Tijhuis
    Hub Tijhuis Member Posts: 181 Gold Badge

    I can't reproduce your problem by creating a simular package.

    Can you show us the first 6 lines of v_stack before and after the first substring by adding output lines before and after the first line after the beginning of FN_WHO_AM_I?

      dbms_output.put_line(v_stack);

      v_stack := SUBSTR(v_stack,INSTR(v_stack,CHR(10),INSTR(v_stack,'FN_WHO_AM_I'))+1)||'ORACLE'; -- skip myself

      dbms_output.put_line(v_stack);

    Because the second one should not contain the line with "OBJECT LINE" . (and if it does there is no number in it , probably causing your error)

    For me it looks like

    1:

    ----- PL/SQL CALL STACK -----

     OBJECT     LINE FRAME      OBJECT

     HANDLE   NUMBER SIZE       NAME

    0X3EEBB1958       19       8264 PACKAGE BODY SQL_FBBUHWABJWAVLLZCQMHKXCBQG.P1.FN_WHO_AM_I

    0X370E2D3D8        3        232 ANONYMOUS BLOCK

    0X427D81610     1721         88 PACKAGE BODY SYS.DBMS_SQL.EXECUTE

    2:

    0X370E2D3D8        3        232 ANONYMOUS BLOCK

    0X427D81610     1721         88 PACKAGE BODY SYS.DBMS_SQL.EXECUTE

    0X427141538     1721       4872 PACKAGE BODY LIVESQL.ORACLE_SQL_EXEC.RUN_BLOCK

    0X427141538     1821        656 PACKAGE BODY LIVESQL.ORACLE_SQL_EXEC.RUN_SQL

    0X427141538     1920        464 PACKAGE BODY LIVESQL.ORACLE_SQL_EXEC.RUN_A_STATEMENT

    0X427141538     2254       5008 PACKAGE BODY LIVESQL.ORACLE_SQL_EXEC.RUN_STATEMENTS