Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Posts
    148

    Unanswered: Passing Date to Stored Proc

    Hi all... I have a stored procedure which is below, I am receiving an Ora-01858 error : A non numeric character was found where a numeric character was expected...i think it is the date I am passing to it, although I suppose it may not be....I have tried using to_date and to_char in my stored proc but to no avail...this is gettin very frustrating although I know its probably something small..

    my stored proc is just insertnig a value and then updating a table with ID column from the previous insert....code follows as well as error

    Code:
    SQL> CREATE OR REPLACE PACKAGE NewBatch
      2  AS
      3   
      4   PROCEDURE NewBatch
      5   (
      6    
      7    Dt     IN  VARCHAR2,
      8    glbRegion    IN  VARCHAR2,
      9    glbUser    IN   VARCHAR2,
     10    cBillSys               IN      VARCHAR2,
     11    strPromoCodes   IN   VARCHAR2,
     12    BatchID   IN   VARCHAR2
     13    
     14   );
     15  END;
     16  / 
    
    Package created.
    
    SQL> CREATE OR REPLACE PACKAGE BODY NewBatch
      2  AS
      3   PROCEDURE NewBatch
      4   ( 
      5    Dt     IN  VARCHAR2,
      6    glbRegion    IN  VARCHAR2,
      7    glbUser    IN   VARCHAR2,
      8    cBillSys               IN      VARCHAR2,
      9    strPromoCodes   IN   VARCHAR2,
     10    BatchID   IN   VARCHAR2
     11   )
     12   IS
     13    nID tbl_OWR_Batch.id%type;
     14   BEGIN 
     15  
     16  Insert INTO tbl_OWR_Batch (dCreated, cRegion, cCreatorID, cBillSys, cComment) Values ('
    Region','glbUser','cBillSys', 'strPromoCodes') returning ID into nID;
     17  
     18  Update tbl_OpenWindowRequests SET iBatch = 'nID' WHERE ID IN 'BatchID' AND cBillSys = '
    ';
     19  
     20   END;  
     21  END;
     22  / 
    
    Package body created.
    
    SQL> execute NewBatch.NewBatch('15-SEP-2004','NY','uttamsa','I','','8400');
    BEGIN NewBatch.NewBatch('15-SEP-2004','NY','uttamsa','I','','8400'); END;
    
    *
    ERROR at line 1:
    ORA-01858: a non-numeric character was found where a numeric was expected
    ORA-06512: at "SOEC.NEWBATCH", line 16
    ORA-06512: at line 1
    
    
    SQL>

  2. #2
    Join Date
    Apr 2004
    Posts
    246
    you put quotes around the variables in the insert. therefore, you're inserting a bunch of text strings.
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  3. #3
    Join Date
    Dec 2003
    Posts
    148
    You hit that on the nose...worked perfect without them. I didn't even contemplate that, thank you!

Posting Permissions

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