1 Reply Latest reply: Dec 11, 2012 7:06 AM by Christian Erlinger RSS

    Please help me to find out solution to following problem

    961624
      create or replace procedure load_prod_dim
      is

      cursor c2 is select p.productid,p.productname,p.unitprice,c.categoryname,s.companyname from products p

      join categories c on p.categoryid = c.categoryid

      join suppliers s on p.supplierid = s.supplierid;

      type c2_prod is table of c2%rowtype index by pls_integer;

      type typ_prod_dim is table of product_dim%rowtype index by pls_integer;

      p_dim typ_prod_dim;

      p_dim_up typ_prod_dim;

      c1_var     typ_prod_dim;

      k number := 1;

      m number := 1;

      flag number := 1;

      y number := 1;

      j number := 1;

      v_sdate date := to_date('1-Jan-1995','DD-MON-YYYY');

      v_edate date := to_date('31-Dec-2050','DD-MON-YYYY');

      begin

           select * bulk collect into p_dim from

                               (select * from product_dim order by eff_start_dt desc);

           if p_dim.count = 0 then

           dbms_output.put_line('Insert all rows in the table...');

           for i in c2

           loop

                     select Product_Seq.nextval into c1_var(k).seq_id from dual;

                     c1_var(k).product_id := i.productid;

                     c1_var(k).product_name := i.productname;

                     c1_var(k).list_price := i.unitprice;

                     c1_var(k).category := i.categoryname;

                     c1_var(k).supplier_name := i.companyname;

                     c1_var(k).eff_start_dt := v_sdate;

                     c1_var(k).eff_end_dt := v_edate;

                     k := k + 1;

           end loop;

           forall i in c1_var.first..c1_var.last insert into product_dim values c1_var(i);

      else

      dbms_output.put_line('Inside the else part..');

           for i in c2

           loop

                for j in p_dim.first..p_dim.last

                loop

                if p_dim(j).product_id = i.productid

                          and p_dim(j).product_name = i.productname

                          and p_dim(j).list_price = i.unitprice

                          and p_dim(j).category = i.categoryname

                          and p_dim(j).supplier_name = i.companyname

                then

                     flag := 1;

                     exit;

                else

                     if p_dim(j).product_id = i.productid then

                          p_dim_up(y).eff_end_dt := sysdate - 1;

                          p_dim_up(y).seq_id := p_dim(j).seq_id;

                          flag := flag + 1;

                          y := y + 1;

                          exit;

                     end if;

                end if;

                end loop;



                if flag != 1 then



                select Product_Seq.nextval into c1_var(m).seq_id from dual;

                     --c1_var(m).seq_id := Product_Seq.nextval;

                     c1_var(m).product_id := i.productid;

                     c1_var(m).product_name := i.productname;

                     c1_var(m).list_price := i.unitprice;

                     c1_var(m).category := i.categoryname;

                     c1_var(m).supplier_name := i.companyname;

                     c1_var(m).eff_start_dt := sysdate;

                     c1_var(m).eff_end_dt := v_edate;

                     m := m + 1;

                end if;



           end loop;


           forall i in p_dim_up.first..p_dim_up.last
                update product_dim set eff_end_dt = p_dim_up(i).eff_end_dt where seq_id = p_dim_up(i).seq_id;



           forall i in c1_var.first..c1_var.last insert into product_dim values c1_var(i);

      --     forall i in p_dim_up.first..p_dim_up.last update product_dim set eff_end_dt = p_dim_up(i).eff_end_dt where seq_id = p_dim_up(i).seq_id;

      end if;
      end;
      /
      show err

      Error in procedure are:

      @load_prod_dim.sql

      Warning: Procedure created with compilation errors.

      Errors for PROCEDURE LOAD_PROD_DIM:

      LINE/COL ERROR
      -------- -----------------------------------------------------------------
      98/3     PL/SQL: SQL Statement ignored
      98/39     PL/SQL: ORA-22806: not an object or REF
      98/39     PLS-00382: expression is of wrong type
      98/39     PLS-00436: implementation restriction: cannot reference fields of
           BULK In-BIND table of records

      98/77     PLS-00382: expression is of wrong type
      98/77     PLS-00436: implementation restriction: cannot reference fields of
           BULK In-BIND table of records

      Edited by: 958621 on Dec 11, 2012 3:31 AM