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

    Unanswered: Stored Procedure...Insert statement problem

    Hi all. I have a stored procedure that does an insert statement and throws the ID value back out (to my ASP code) The insert statement has 28 different values it inserts. The insert works fine in SQL Plus and from my ASP page, the problem occurs when I put the insert into my package body. I receive the following errors [full output with line numbers below]:

    LINE/COL ERROR
    -------- -----------------------------------------------------
    41/1 PL/SQL: SQL Statement ignored
    97/13 PL/SQL: ORA-00984: column not allowed here

    If I change my insert statement to only 25 values - everything works fine. Is there anyway I can do this with all my 28 values? My entire stored procedure is below with line numbers and the error

    Code:
    SQL> CREATE OR REPLACE PACKAGE ReqSubmit
      2  AS
      3   
      4   PROCEDURE ReqSubmit
      5   (
      6    
      7   IcReqName IN CHAR,
      8   IcReqEmail IN CHAR,
      9   IcReqID  IN CHAR,
     10   IcApprName IN CHAR,
     11   IcApprID  IN NUMBER,
     12   IcMarket  IN CHAR,
     13   IcChannel IN CHAR,
     14   IcAccount IN CHAR,
     15   IcMobile  IN CHAR,
     16   IcPricePlan IN CHAR,
     17   IcPromoCode IN CHAR,
     18   IcDiscountCode IN CHAR,
     19   IcFeatureCode IN CHAR,
     20   IdBackDate IN DATE,
     21   IiReason  IN NUMBER,
     22   IdSUbmit  IN DATE,
     23   IdSubmitTime IN DATE,
     24   IcAppr2ID IN NUMBER,
     25   IcRegion  IN CHAR,
     26   IcBillSys IN CHAR,
     27   IiFinRvwRequired IN NUMBER,
     28   IcFeaturecode2 IN CHAR,
     29   IcFeatureCode3 IN CHAR,
     30   IcFeatureCode4 IN CHAR,
     31   IcFeatureCode5 IN CHAR,
     32   IcBillAcct IN CHAR,
     33   IcBGSA  IN CHAR,
     34   IcISIGID  IN CHAR,
     35   IcReason  IN CHAR,
     36   nID_out  OUT NUMBER
     37  
     38  
     39    
     40   );
     41  END;
     42  / 
    
    Package created.
    
    SQL> CREATE OR REPLACE PACKAGE BODY ReqSubmit
      2  AS
      3   PROCEDURE ReqSubmit
      4   ( 
      5   IcReqName IN CHAR,
      6   IcReqEmail IN CHAR,
      7   IcReqID  IN CHAR,
      8   IcApprName IN CHAR,
      9   IcApprID  IN NUMBER,
     10   IcMarket  IN CHAR,
     11   IcChannel IN CHAR,
     12   IcAccount IN CHAR,
     13   IcMobile  IN CHAR,
     14   IcPricePlan IN CHAR,
     15   IcPromoCode IN CHAR,
     16   IcDiscountCode IN CHAR,
     17   IcFeatureCode IN CHAR,
     18   IdBackDate IN DATE,
     19   IiReason  IN NUMBER,
     20   IdSUbmit  IN DATE,
     21   IdSubmitTime IN DATE,
     22   IcAppr2ID IN NUMBER,
     23   IcRegion  IN CHAR,
     24   IcBillSys IN CHAR,
     25   IiFinRvwRequired IN NUMBER,
     26   IcFeaturecode2 IN CHAR,
     27   IcFeatureCode3 IN CHAR,
     28   IcFeatureCode4 IN CHAR,
     29   IcFeatureCode5 IN CHAR,
     30   IcBillAcct IN CHAR,
     31   IcBGSA  IN CHAR,
     32   IcISIGID  IN CHAR,
     33   IcReason  IN CHAR,
     34   nID_out  OUT NUMBER
     35  
     36   )
     37   IS
     38    nID tbl_OpenWindowRequests.id%type;
     39    
     40   BEGIN 
     41  Insert Into tbl_OpenWindowRequests 
     42  (cReqName,
     43  cReqEmail,
     44  cReqID,
     45  cApprName,
     46  cApprID,
     47  cMarket,
     48  cChannel,
     49  cAccount,
     50  cMobile,
     51  cPricePlan,
     52  cPromoCode,
     53  cDiscountCode,
     54  cFeatureCode,
     55  dBackDate,
     56  iReason,
     57  dSubmit,
     58  dSubmitTime,
     59  cAppr2ID,
     60  cRegion,
     61  cBillSys,
     62  iFinRvwRequired,
     63  cFeatureCode2,
     64  cFeatureCode3,
     65  cFeatureCode4,
     66  cFeatureCode5,
     67  cBillAcct,
     68  cISGID,
     69  cReason
     70   ) 
     71  Values 
     72  (IcReqName,
     73  IcReqEmail,
     74  IcReqID,
     75  IcApprName,
     76  IcApprID,
     77  IcMarket,
     78  IcChannel,
     79  IcAccount,
     80  IcMobile,
     81  IcPricePlan,
     82  IcPromoCode,
     83  IcDiscountCode,
     84  IcFeatureCode,
     85  IdBackDate,
     86  IiReason,
     87  IdSubmit,
     88  IdSubmitTime,
     89  IcAppr2ID,
     90  IcRegion,
     91  IcBillSys,
     92  IiFinRvwRequired,
     93  IcFeatureCode2,
     94  IcFeatureCode3,
     95  IcFeatureCode4,
     96  IcFeatureCode5,
     97  IcBillAcct,
     98  IcISGID,
     99  IcReason
    100  )
    101   returning ID into nID;
    102  
    103  
    104  nID_out := nID;
    105  
    106   
    107  
    108   END;  
    109  END;
    110  / 
    
    Warning: Package Body created with compilation errors.
    
    SQL> 
    SQL> 
    SQL> show err
    Errors for PACKAGE BODY REQSUBMIT:
    
    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    41/1     PL/SQL: SQL Statement ignored
    97/13    PL/SQL: ORA-00984: column not allowed here
    SQL>

  2. #2
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    It seems that 'IcBillAcct' exists in the table you are trying to insert into and you referenced it in the VALUES of the INSERT statement. I see you didn't capture IcBillAcct as a parameter to the procedure, did you missed it unintentionally or something ?

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    No, Martinez ... you're close, but not close enough
    In package declaration the procedure has ICBGSA and it dissapeared in procedures' declaration in package body.

  4. #4
    Join Date
    Dec 2003
    Posts
    148

    forgive me, for i must be losing my mind...

    Hi guys. The code below is pasted from my previous post.....I see both IcBSGA and icBillAcct as parameters in package and package body...Did I miss something else?

    Code:
    SQL> CREATE OR REPLACE PACKAGE ReqSubmit
      2  AS
      3   
      4   PROCEDURE ReqSubmit
      5   (
      6    
      7   IcReqName IN CHAR,
      8   IcReqEmail IN CHAR,
      9   IcReqID  IN CHAR,
     10   IcApprName IN CHAR,
     11   IcApprID  IN NUMBER,
     12   IcMarket  IN CHAR,
     13   IcChannel IN CHAR,
     14   IcAccount IN CHAR,
     15   IcMobile  IN CHAR,
     16   IcPricePlan IN CHAR,
     17   IcPromoCode IN CHAR,
     18   IcDiscountCode IN CHAR,
     19   IcFeatureCode IN CHAR,
     20   IdBackDate IN DATE,
     21   IiReason  IN NUMBER,
     22   IdSUbmit  IN DATE,
     23   IdSubmitTime IN DATE,
     24   IcAppr2ID IN NUMBER,
     25   IcRegion  IN CHAR,
     26   IcBillSys IN CHAR,
     27   IiFinRvwRequired IN NUMBER,
     28   IcFeaturecode2 IN CHAR,
     29   IcFeatureCode3 IN CHAR,
     30   IcFeatureCode4 IN CHAR,
     31   IcFeatureCode5 IN CHAR,
     32   IcBillAcct IN CHAR,
     33   IcBGSA  IN CHAR,
     34   IcISIGID  IN CHAR,
     35   IcReason  IN CHAR,
     36   nID_out  OUT NUMBER
     37  
     38  
     39    
     40   );
     41  END;
     42  / 
    
    Package created.
    
    SQL> CREATE OR REPLACE PACKAGE BODY ReqSubmit
      2  AS
      3   PROCEDURE ReqSubmit
      4   ( 
      5   IcReqName IN CHAR,
      6   IcReqEmail IN CHAR,
      7   IcReqID  IN CHAR,
      8   IcApprName IN CHAR,
      9   IcApprID  IN NUMBER,
     10   IcMarket  IN CHAR,
     11   IcChannel IN CHAR,
     12   IcAccount IN CHAR,
     13   IcMobile  IN CHAR,
     14   IcPricePlan IN CHAR,
     15   IcPromoCode IN CHAR,
     16   IcDiscountCode IN CHAR,
     17   IcFeatureCode IN CHAR,
     18   IdBackDate IN DATE,
     19   IiReason  IN NUMBER,
     20   IdSUbmit  IN DATE,
     21   IdSubmitTime IN DATE,
     22   IcAppr2ID IN NUMBER,
     23   IcRegion  IN CHAR,
     24   IcBillSys IN CHAR,
     25   IiFinRvwRequired IN NUMBER,
     26   IcFeaturecode2 IN CHAR,
     27   IcFeatureCode3 IN CHAR,
     28   IcFeatureCode4 IN CHAR,
     29   IcFeatureCode5 IN CHAR,
     30   IcBillAcct IN CHAR,
     31   IcBGSA  IN CHAR,
     32   IcISIGID  IN CHAR,
     33   IcReason  IN CHAR,
     34   nID_out  OUT NUMBER
     35  
     36   )
     37   IS
     38    nID tbl_OpenWindowRequests.id%type;
     39    
     40   BEGIN 
     41  Insert Into tbl_OpenWindowRequests 
     42  (cReqName,
     43  cReqEmail,
     44  cReqID,
     45  cApprName,
     46  cApprID,
     47  cMarket,
     48  cChannel,
     49  cAccount,
     50  cMobile,
     51  cPricePlan,
     52  cPromoCode,
     53  cDiscountCode,
     54  cFeatureCode,
     55  dBackDate,
     56  iReason,
     57  dSubmit,
     58  dSubmitTime,
     59  cAppr2ID,
     60  cRegion,
     61  cBillSys,
     62  iFinRvwRequired,
     63  cFeatureCode2,
     64  cFeatureCode3,
     65  cFeatureCode4,
     66  cFeatureCode5,
     67  cBillAcct,
     68  cISGID,
     69  cReason
     70   ) 
     71  Values 
     72  (IcReqName,
     73  IcReqEmail,
     74  IcReqID,
     75  IcApprName,
     76  IcApprID,
     77  IcMarket,
     78  IcChannel,
     79  IcAccount,
     80  IcMobile,
     81  IcPricePlan,
     82  IcPromoCode,
     83  IcDiscountCode,
     84  IcFeatureCode,
     85  IdBackDate,
     86  IiReason,
     87  IdSubmit,
     88  IdSubmitTime,
     89  IcAppr2ID,
     90  IcRegion,
     91  IcBillSys,
     92  IiFinRvwRequired,
     93  IcFeatureCode2,
     94  IcFeatureCode3,
     95  IcFeatureCode4,
     96  IcFeatureCode5,
     97  IcBillAcct,
     98  IcISGID,
     99  IcReason
    100  )
    101   returning ID into nID;
    102  
    103  
    104  nID_out := nID;
    105  
    106   
    107  
    108   END;  
    109  END;
    110  / 
    
    Warning: Package Body created with compilation errors.
    
    SQL> 
    SQL> 
    SQL> show err
    Errors for PACKAGE BODY REQSUBMIT:
    
    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    41/1     PL/SQL: SQL Statement ignored
    97/13    PL/SQL: ORA-00984: column not allowed here
    SQL>

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    LOL, right ... I'd better go to sleep! I overlooked ICBGSA, sorry.
    However, when I read better what Martinez said, I'd say he was right. Do you have IcBillAcct column in your table? If so, rename your parameter ...
    Actually, how about posting description of tbl_OpenWindowRequests table?

Posting Permissions

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