CODEWITHSUNDEEP
oracle-databaseplsqloracle10gora-00984
Orapps
Orapps

Reputation: 631

procedure problem

create or replace
PROCEDURE XXB_RJT_HEADER_PROCEURE 
  (
    V_PROD_ID  IN NUMBER,
    V_WARE_ID  IN XXB_RJT_HEADER.WAREHOUSE_ID% TYPE,
    V_PAY_METH IN XXB_RJT_HEADER.PAYMENT_METHOD% TYPE,
    V_PAY_STAT IN XXB_RJT_HEADER.PAYMENT_STATUS% TYPE,
    V_ORD_ID   IN XXB_RJT_HEADER.ORDER_ID% TYPE,
    V_ORD_DT   IN XXB_RJT_HEADER.ORDER_DATE% TYPE )
AS
  V_PROD_NM VARCHAR2(50);
  V_WAR_NM  VARCHAR2(15);
BEGIN
  SELECT PRODUCT_CAT
  INTO V_PROD_NM
  FROM xxb_rjt_inventory
  WHERE XXB_RJT_INVENTORY.product_id= V_prod_id;
  SELECT WAREHOUSE_NAME
  INTO V_WAR_NM
  FROM xxb_rjt_inventory
  WHERE XXB_RJT_INVENTORY.product_id= V_prod_id;

  INSERT
  INTO XXB_RJT_HEADER
    (                  /*second error*/
      warehouse_id,
      PAYMENT_METHOD,
      payment_status,
      product_name,
      order_id,
      wareshouse_name,
      order_date
    )
    VALUES
    (
      V_warehouse_id,
      v_pay_meth,  /*First error*/
      V_pay_stat,
      V_prod_nm,
      V_ord_id,
      V_war_nm,
      V_ord_dt
    );



END XXB_RJT_HEADER_PROCEURE;

when i compile this i get the following errors

Error(37,7): PL/SQL: ORA-00984: column not allowed here

Error(24,65530): PL/SQL: SQL Statement ignored

thanks for the help in advance

Upvotes: 0

Views: 175

Answers (3)

tuinstoel
tuinstoel

Reputation: 7316

You can rewrite is in something like (untested):

create or replace
PROCEDURE XXB_RJT_HEADER_PROCEURE 
  (
    V_PROD_ID  IN xxb_rjt_inventory.product_id%type,
    V_WARE_ID  IN XXB_RJT_HEADER.WAREHOUSE_ID% TYPE,
    V_PAY_METH IN XXB_RJT_HEADER.PAYMENT_METHOD% TYPE,
    V_PAY_STAT IN XXB_RJT_HEADER.PAYMENT_STATUS% TYPE,
    V_ORD_ID   IN XXB_RJT_HEADER.ORDER_ID% TYPE,
    V_ORD_DT   IN XXB_RJT_HEADER.ORDER_DATE% TYPE )
AS
BEGIN

  INSERT
  INTO XXB_RJT_HEADER
    (                  
      warehouse_id,
      PAYMENT_METHOD,
      payment_status,
      product_name,
      order_id,
      wareshouse_name,
      order_date
    )
    select 
      V_ware_id,
      v_pay_meth,  
      V_pay_stat,
      product_cat,
      V_ord_id,
      warehouse_name,
      V_ord_dt
    from xxb_rjt_inventory
    where product_id= V_prod_id;

END XXB_RJT_HEADER_PROCEURE;

This means two less sql statements and two less variables to declare. Also change the name of the procedure, you write proceure instead of procedure. I also changed the type of the first parameter of your procedure.

Upvotes: 2

jva
jva

Reputation: 2805

"V_warehouse_id" is no declared anywhere.

Upvotes: 4

JeffH
JeffH

Reputation: 10482

Your ORA-00984 error means:

A column name was used in an expression where it is not permitted, such as in the VALUES clause of an INSERT statement.

Check the VALUES part of the INSERT to be sure none of the arguments are columns.

Once you fix that, see if the other error goes away. "PL/SQL: SQL Statement ignored" seems to appear after there's already another error.

Upvotes: 2

Related Questions