Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2003
    Posts
    4

    Unanswered: ORA-06502: PL/SQL: numeric or value error: -- very new to Ora!

    Hi

    I'm hoping one of you guys can help me out with something.

    I am on a project using Oracle and have NO oracle experience .. so please forgive me if I sound a bit MS SQL ..

    I have a stored proc to either add a new record or update an existing record ..

    the code is below


    Code:
    CREATE OR REPLACE PROCEDURE SP_MAPSITE_STATE
    (
       prm_id IN OUT NUMERIC,
       prm_name IN VARCHAR2,
       prm_folderpath IN VARCHAR2,
       prm_author IN VARCHAR2,
       prm_lastpublishedby IN VARCHAR2,
       prm_version IN VARCHAR2
    
    )
    IS
    /***********************************************************
        Name: SP_MAPSITESTATE
    	Auhtor: Antony Gorman
    	Date: 10th Jan 2003
    	Desc: If the mapsite doesnt currently exist - add a new one
    		  - else update the current one
    		  
    	History:
    ***********************************************************/
    
    	map_count NUMERIC;
    
    BEGIN
    	 SELECT COUNT(*) INTO map_count
    	 FROM   MAP_SITES
    	 WHERE  MPS_ID = prm_id;
    	 
    	 IF map_count = 0 THEN --need to add a new mapsite record
    	 BEGIN
    	 	  /*get a the next available id value*/
    		  SELECT MAX(MPS_ID) + 1 INTO prm_id
    		  FROM 	 MAP_SITES;
    		  
    		  INSERT INTO MAP_SITES
    		  		 (	  MPS_ID, 
    				 	  MPS_NAME,
    					  MPS_FOLDERPATH,
    					  MPS_AUTHOR,
    					  MPS_LASTUPDATED,
    					  MPS_LASTPUBLISHEDBY,
    					  MPS_VERSION
    				  )
    		   VALUES (	  prm_id,
    		   		  	  prm_name,
    					  prm_folderpath,
    					  prm_author,
    					  SYSDATE,
    					  prm_lastpublishedby,
    					  prm_version
    				   );
    	 END;
    	 ELSE --need to update the existing map site
    	 	  UPDATE MAP_SITES
    		  SET	 MPS_NAME = prm_name,
    		  		 MPS_FOLDERPATH = prm_folderpath,
    				 MPS_AUTHOR = prm_author,
    				 MPS_LASTUPDATED = SYSDATE,
    				 MPS_LASTPUBLISHEDBY = prm_lastpublishedby,
    				 MPS_VERSION = prm_version
    		  WHERE	 MPS_ID = prm_id;
    	 END IF;
    END;
    I can call this code fine from TOAD so it seems the PL/SQL is ok .. BUT I can't get it running when called from an ASP app I am working on ..

    the snippet below is where its called

    Code:
    case MAPSITE_STATE:
    			var _index, _count;
    			//'in this case the args points to the mapsite object to save state of
    			_command.CommandType = adCmdStoredProc;
    			_command.CommandText = "SP_MAPSITE_STATE";
    			if (DEBUG)
    				Response.Write("MAPSITE_STATE >> args.getName() == " + args.getName() + "<br>");
    			_param = _command.CreateParameter("prm_name", adVarChar, adParamInput, 255, args.getName());
    			_command.Parameters.Append(_param);
    			_param = _command.CreateParameter("prm_folderpath", adVarChar, adParamInput, 255, args.getFolderPath());
    			_command.Parameters.Append(_param);
    			_param = _command.CreateParameter("prm_author", adVarChar, adParamInput, 255, args.getAuthor());
    			_command.Parameters.Append(_param);
    			//'_param = _command.CreateParameter("prm_lastpublishedby", adVarChar, adParamInput, 255, Session("user")._uid);
    			_param = _command.CreateParameter("prm_lastpublishedby", adVarChar, adParamInput, 255, 'antony');
    			_command.Parameters.Append(_param);
    			if (DEBUG)
    				Response.Write("MAPSITE_STATE >> args.getVersion() == " + args.getVersion() + "<br>")
    			_param = _command.CreateParameter("prm_version", adVarChar, adParamInput, 255, args.getVersion() + "");
    			_command.Parameters.Append(_param);
    			//'last one we define is for for id as is an IN OUT one
    			if (DEBUG)
    				Response.Write("MAPSITE_STATE >> args.getID() == " + args.getID() + "<br>");
    			_param = _command.CreateParameter("prm_id", adNumeric, adParamInputOutput, 8, args.getID());
    			_command.Parameters.Append(_param);
    			if (this._connection == null)
    				this.connect();
    			_command.ActiveConnection = this._connection;
    			_command.execute();
    each time I try this I am getting the following OLEDB Return


    Microsoft OLE DB Provider for Oracle error '80004005'

    ORA-06502: PL/SQL: numeric or value error: character to number conversion error ORA-06512: at line 1

    /ant_dev_hhg/framework/OLEDBProvider.asp, line 173

    I am truly stumped unfortunately .. I am presuming its some sort of implicit cast being made somewhere .. but dont know tbh ,,

    any pearls of wisdom are most welcome

    cheers
    Antony

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I don't know ASP very well, so can't say much about your error - except that I would expect it to be the value of prm_id being passed in that is causing the problem (since it's the only NUMBER parameter).

    Since you are new to Oracle, though, I will give you some feedback on your code from an Oracle perspective:

    1) Rather than select COUNT(*) to see if record already exists, it would be more efficient to just do the UPDATE, then if SQL%ROWCOUNT = 0 (i.e. no rows updated), do an INSERT.
    (Alternatively, if you think most calls will be INSERTs, do the INSERT first then if it fails due to PK violation, do the UPDATE).

    2) This is a really BAD idea (in Oracle at least):
    /*get a the next available id value*/
    SELECT MAX(MPS_ID) + 1 INTO prm_id
    FROM MAP_SITES;

    Why? It will only work properly on a single-user application. If 2 users call the procedure at the same time, they will get the same ID and one of them will fail with a PK violation.
    You could lock the table, but then you are serialising inserts: if 2 users call the procedure, user 2 will hang until user 1 commits.
    You should use a SEQUENCE:
    CREATE SEQUENCE prm_id_seq;
    then user prm_id_seq.NEXTVAL to get a new number
    (Note that this will not guarantee gap-free numbering, but who needs that?)

    3) Trivial point: prefix stored procedures with "SP_" is very SQL-Server-ish, and not usual in Oracle. A procedure name should generally be of the form VERB_NOUN such as RECORD_MAPSITE_STATE.

    4) More important point: you should ALWAYS use packages rather than stand-alone stored procedures. Group related procedures together into packages. This makes the job of managing user privileges much easier.

    I have taken all the above points and modified your code as an example, below. I hope you don't mind me making these comments!

    CREATE OR REPLACE PACKAGE mapsite_pkg IS
    PROCEDURE RECORD_MAPSITE_STATE
    (
    prm_id IN OUT NUMERIC,
    prm_name IN VARCHAR2,
    prm_folderpath IN VARCHAR2,
    prm_author IN VARCHAR2,
    prm_lastpublishedby IN VARCHAR2,
    prm_version IN VARCHAR2

    );
    -- Other MAPSITE procedure specifications here
    END mapsite_pkg;
    /

    CREATE OR REPLACE PACKAGE BODY mapsite_pkg IS
    PROCEDURE RECORD_MAPSITE_STATE
    (
    prm_id IN OUT NUMERIC,
    prm_name IN VARCHAR2,
    prm_folderpath IN VARCHAR2,
    prm_author IN VARCHAR2,
    prm_lastpublishedby IN VARCHAR2,
    prm_version IN VARCHAR2

    )
    IS
    / **************************************************
    *********
    Name: RECORD_MAPSITE_STATE
    Auhtor: Antony Gorman
    Date: 10th Jan 2003
    Desc: If the mapsite doesnt currently exist - add a new one
    - else update the current one

    History:
    **************************************************
    *********/

    BEGIN
    UPDATE MAP_SITES
    SET MPS_NAME = prm_name,
    MPS_FOLDERPATH = prm_folderpath,
    MPS_AUTHOR = prm_author,
    MPS_LASTUPDATED = SYSDATE,
    MPS_LASTPUBLISHEDBY = prm_lastpublishedby,
    MPS_VERSION = prm_version
    WHERE MPS_ID = prm_id;

    IF SQL%ROWCOUNT = 0 THEN --need to add a new mapsite record

    INSERT INTO MAP_SITES
    ( MPS_ID,
    MPS_NAME,
    MPS_FOLDERPATH,
    MPS_AUTHOR,
    MPS_LASTUPDATED,
    MPS_LASTPUBLISHEDBY,
    MPS_VERSION
    )
    VALUES ( mps_id_seq.NEXTVAL,
    prm_name,
    prm_folderpath,
    prm_author,
    SYSDATE,
    prm_lastpublishedby,
    prm_version
    );
    END IF;
    END;
    -- Other MAPSITE procedure bodies here
    END mapsite_pkg;
    /

    (BTW, if someone could explain to me how to preserve indentation when posting code examples, I'd be much obliged!)

Posting Permissions

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