error ORA-06502
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: error ORA-06502

  1. #1
    Join Date
    Dec 2000
    Posts
    126

    error ORA-06502

    Hi,

    I wrote this piece of code to get current procedure name but having error 6502 when executing it.

    Can any one know why is it so?

    Thks





    SQL*Plus: Release 10.2.0.1.0 - Production on Tue Nov 14 17:20:03 2006

    Copyright (c) 1982, 2005, Oracle. All rights reserved.


    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
    With the Partitioning, OLAP and Data Mining options


    SQL> l
    1 create or replace procedure my_proc
    2 is
    3 l_proc varchar2(1024);
    4 begin
    5 l_proc := owa_util.GET_PROCEDURE ;
    6 --raise_application_error(-20001,l_proc);
    7* end;
    SQL> /

    Procedure created.

    SQL> exec my_proc
    BEGIN my_proc; END;

    *
    ERROR at line 1:
    ORA-06502: PL/SQL: numeric or value error
    ORA-06512: at "SYS.OWA_UTIL", line 325
    ORA-06512: at "SYS.OWA_UTIL", line 2389
    ORA-06512: at "D269732.MY_PROC", line 5
    ORA-06512: at line 1


    SQL>

  2. #2
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Hi.

    The OWA_UTIL package is part of the web toolkit, used to write web applications to run with mod_plsql. Although there are a number of functions that can be used in a different context, you should really be using them just for that purpose.

    You've compiled the procedure, then run it in SQL*Plus, which is not a HTTP server and doesn't use mod_plsql, so it is unlikely to work.

    I compiled the following variation on your procedure and called it from a browser via mod_plsql:

    Code:
    create or replace procedure my_proc
    is
      l_proc varchar2(32767);
    begin
      l_proc := owa_util.GET_PROCEDURE ;
      HTP.print(l_proc);
    end;
    /
    It worked fine, giving me a web page with "MY_PROC" written on it.

    This kind-of proves my assumption that this procedure only works when used in conjunction with mod_plsql and a HTTP server.

    Note also, I've set the "l_proc" size to 32767. The "GET_PROCEDURE" function returns a VARCHAR2, so it can theoretically be as big as 32767. You should be careful to size your variables correctly. I'm sure 1024 is fine, but you never know when something will be changed making your code fall in a heap.

    Cheers

    Tim...
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  3. #3
    Join Date
    Dec 2000
    Posts
    126
    Thks Tim

    I will look into it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width