Re: ORA-01008 not all variables bound ?

From: Eddie <edawad_at_hotmail.com>
Date: Wed, 06 Dec 2000 22:07:16 GMT
Message-ID: <nMyX5.729$2W6.180024@paloalto-snr1.gtei.net>

Thank you Cliff for your reply, here is the PL/SQL that calls the stored procedure:

DECLARE

out_jan NUMBER;
out_feb NUMBER;
out_mar NUMBER;
out_apr NUMBER;
out_may NUMBER;
out_jun NUMBER;
out_jul NUMBER;
out_aug NUMBER;
out_sep NUMBER;
out_oct NUMBER;
out_nov NUMBER;
out_dec NUMBER;

BEGIN

Now call the stored program esco_web_po_plan_orders_mon( '1580', '4078675', 'DNV', 'Brown, Richard S',

out_jan,out_feb,out_mar,out_apr,out_may,out_jun,out_jul,out_aug,out_sep,out_ oct,out_nov,out_dec);

Output the results dbms_output.put_line('out_jan = '||TO_CHAR(out_jan)); dbms_output.put_line('out_feb = '||TO_CHAR(out_feb)); dbms_output.put_line('out_mar = '||TO_CHAR(out_mar)); dbms_output.put_line('out_apr = '||TO_CHAR(out_apr)); dbms_output.put_line('out_may = '||TO_CHAR(out_may)); dbms_output.put_line('out_jun = '||TO_CHAR(out_jun)); dbms_output.put_line('out_jul = '||TO_CHAR(out_jul)); dbms_output.put_line('out_aug = '||TO_CHAR(out_aug)); dbms_output.put_line('out_sep = '||TO_CHAR(out_sep)); dbms_output.put_line('out_oct = '||TO_CHAR(out_oct)); dbms_output.put_line('out_nov = '||TO_CHAR(out_nov)); dbms_output.put_line('out_dec = '||TO_CHAR(out_dec));

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM); RAISE;
END;

---

When I run the above code I get the following error:

ORA-01008: not all variables bound
ORA-02063: preceding line from RPT1
ORA-06512: at line 36


-----------------

RPT1 is a database link that is used in a view. The view is used in the

stored procedure.
Here is the stored procedure code:

---

procedure esco_web_po_plan_orders_mon

    (in_vendor in varchar2,
     in_item in varchar2,
     in_org in varchar2,
     in_buyer in varchar2,
  out_jan out number,
  out_feb out number,
  out_mar out number,
  out_apr out number,
  out_may out number,
  out_jun out number,
  out_jul out number,
  out_aug out number,
  out_sep out number,
  out_oct out number,

out_nov out number,
out_dec out number) as

     begin
        declare
        tmp_vendor  varchar2(30);
        tmp_item    varchar2(20);
        tmp_org     varchar2(3);
        tmp_buyer   varchar2(60);

        CURSOR c1 is
     SELECT ewp.sched_month,ewp.sched_qty
      FROM apps.esco_web_po_plan_orders_month ewp
        WHERE ewp.vendor_number = tmp_vendor
       and ewp.item_number = tmp_item
       and ewp.organization_code = tmp_org
       and ewp.buyer_name = tmp_buyer;

         in_month  varchar2(3);
         in_qty    number;
         in_Jan     number;
         in_Feb     number;
         in_Mar     number;
         in_Apr     number;
         in_May     number;
         in_Jun     number;
         in_Jul     number;
         in_Aug     number;
         in_Sep     number;
         in_Oct     number;
         in_Nov     number;
         in_Dec     number;

         begin
                tmp_vendor  := in_vendor;
                tmp_item    := in_item;
                tmp_org     := in_org;
                tmp_buyer   := in_buyer;

                out_jan    := 0;
                out_feb     := 0;
                out_mar     := 0;
                out_apr     := 0;
                out_may     := 0;
                out_jun     := 0;
                out_jul     := 0;
                out_aug     := 0;
                out_sep     := 0;
                out_oct     := 0;
                out_nov     := 0;
                out_dec     := 0;

                open c1;
                 loop
                    fetch c1 into in_month, in_qty;
                    exit when c1%NOTFOUND;

                    if in_month = 'Jan'
                        then
                            out_jan := in_qty;
                    end if;
                    if in_month = 'Feb'
                        then
                            out_feb := in_qty;
                    end if;
                    if in_month = 'Mar'
                        then
                            out_mar := in_qty;
                    end if;
                    if in_month = 'Apr'
                        then
                            out_apr := in_qty;
                    end if;
                    if in_month = 'May'
                        then
                            out_may := in_qty;
                    end if;
                    if in_month = 'Jun'
                        then
                            out_jun := in_qty;
                    end if;
                    if in_month = 'Jul'
                        then
                            out_jul := in_qty;
                    end if;
                    if in_month = 'Aug'
                        then
                            out_aug := in_qty;
                    end if;
                    if in_month = 'Sep'
                        then
                            out_sep := in_qty;
                    end if;
                    if in_month = 'Oct'
                        then
                            out_oct := in_qty;
                    end if;
                    if in_month = 'Nov'
                        then
                            out_nov := in_qty;
                    end if;
                    if in_month = 'Dec'
                        then
                            out_dec := in_qty;
                    end if;
                end loop;
     end;

end;

---

I think that the problem lies in the query (and more specifically, in the view):

     SELECT ewp.sched_month,ewp.sched_qty
      FROM apps.esco_web_po_plan_orders_month ewp
        WHERE ewp.vendor_number = tmp_vendor
       and ewp.item_number = tmp_item
       and ewp.organization_code = tmp_org
       and ewp.buyer_name = tmp_buyer

apps.esco_web_po_plan_orders_month is a view that contains a database link, Once the database link is removed, the error is gone, I do not know why? Here is the SQL of the view:

---

SELECT DISTINCT DATAMART_VENDOR_ITEMS.A$VENDOR_NUMBER,

       DATAMART_VENDOR_ITEMS.VENDOR_NAME,
       DATAMART_MASTER_SCHEDULE.A$ITEM_NUMBER,
       DATAMART_MASTER_SCHEDULE.A$ORGANIZATION_CODE,
       nvl(DATAMART_ITEM_ATTRIBUTES.BUYER_NAME,'X'),
       to_char(DATAMART_MASTER_SCHEDULE.SCHEDULE_DATE,'Mon'),
       SUM(DATAMART_MASTER_SCHEDULE.SCHEDULE_QUANTITY)
 FROM DATAMART.ITEM_ATTRIBUTES_at_rpt1 DATAMART_ITEM_ATTRIBUTES,
      DATAMART.VENDOR_ITEMS_at_rpt1 DATAMART_VENDOR_ITEMS,
      DATAMART.MASTER_SCHEDULE_at_rpt1 DATAMART_MASTER_SCHEDULE

WHERE DATAMART_MASTER_SCHEDULE.A$ITEM_NUMBER = DATAMART_ITEM_ATTRIBUTES.A$ITEM_NUMBER
AND DATAMART_MASTER_SCHEDULE.A$ORGANIZATION_CODE = DATAMART_ITEM_ATTRIBUTES.A$ORGANIZATION_CODE AND DATAMART_MASTER_SCHEDULE.A$ORGANIZATION_CODE Not In

('UKG','ESC','VCA','VUK','VGM','VFR','RCA','VBG') AND (DATAMART_MASTER_SCHEDULE.SUPPLY_DEMAND = 'Supply' Or DATAMART_MASTER_SCHEDULE.SUPPLY_DEMAND Is Null) AND (DATAMART_ITEM_ATTRIBUTES.ITEM_PROCURMENT_SOURCE = 'Supplier'

   Or DATAMART_ITEM_ATTRIBUTES.ITEM_PROCURMENT_SOURCE Is Null)
  AND DATAMART_MASTER_SCHEDULE.SCHEDULE_DESIGNATOR Like '%LV%'
  AND DATAMART_MASTER_SCHEDULE.A$ITEM_NUMBER =

DATAMART_VENDOR_ITEMS.A$ITEM_NUMBER
AND DATAMART_ITEM_ATTRIBUTES.PLANNING_MAKE_BUY_CODE = 'Buy' AND DATAMART_VENDOR_ITEMS.VENDOR_RANK = 1 GROUP BY DATAMART_VENDOR_ITEMS.A$VENDOR_NUMBER,

       DATAMART_VENDOR_ITEMS.VENDOR_NAME,
       DATAMART_MASTER_SCHEDULE.A$ITEM_NUMBER,
       DATAMART_MASTER_SCHEDULE.A$ORGANIZATION_CODE,
       nvl(DATAMART_ITEM_ATTRIBUTES.BUYER_NAME,'X'),
      to_char(DATAMART_MASTER_SCHEDULE.SCHEDULE_DATE,'Mon')

UNION
SELECT DISTINCT DATAMART_VENDOR_ITEMS.A$VENDOR_NUMBER,

       DATAMART_VENDOR_ITEMS.VENDOR_NAME,
       DATAMART_MASTER_SCHEDULE.A$ITEM_NUMBER,
       DATAMART_MASTER_SCHEDULE.A$ORGANIZATION_CODE,
       nvl(DATAMART_ITEM_ATTRIBUTES.BUYER_NAME,'X'),
       to_char(DATAMART_MASTER_SCHEDULE.SCHEDULE_DATE,'Mon'),
       SUM(DATAMART_MASTER_SCHEDULE.SCHEDULE_QUANTITY)
 FROM DATAMART.ITEM_ATTRIBUTES_at_rpt1 DATAMART_ITEM_ATTRIBUTES,
      DATAMART.VENDOR_ITEMS_at_rpt1 DATAMART_VENDOR_ITEMS,
      DATAMART.MASTER_SCHEDULE_at_rpt1 DATAMART_MASTER_SCHEDULE

   Or DATAMART_ITEM_ATTRIBUTES.ITEM_PROCURMENT_SOURCE Is Null)
  AND DATAMART_MASTER_SCHEDULE.SCHEDULE_DESIGNATOR Like '%LV%'
  AND DATAMART_MASTER_SCHEDULE.A$ITEM_NUMBER||'-OSP1' =

DATAMART_VENDOR_ITEMS.A$ITEM_NUMBER
AND DATAMART_ITEM_ATTRIBUTES.PLANNING_MAKE_BUY_CODE = 'Make' AND DATAMART_VENDOR_ITEMS.VENDOR_RANK = 1 GROUP BY DATAMART_VENDOR_ITEMS.A$VENDOR_NUMBER,

       DATAMART_VENDOR_ITEMS.VENDOR_NAME,
       DATAMART_MASTER_SCHEDULE.A$ITEM_NUMBER,
       DATAMART_MASTER_SCHEDULE.A$ORGANIZATION_CODE,
       nvl(DATAMART_ITEM_ATTRIBUTES.BUYER_NAME,'X'),
      to_char(DATAMART_MASTER_SCHEDULE.SCHEDULE_DATE,'Mon')


----------------------------------------------------------------------------

---

I did not write any of the code I included here. We use Oracle 8.1.6 and SQL Net8 connection.

Thanks for your help.
Eddie.

"Cliff Dabrowski" <cliffdw_at_my-deja.com> wrote in message news:QqyX5.135596$hD4.33495744_at_news1.rdc1.mi.home.com...

> Eddie,
>
> Your application is apparently making a call to a stored procedure that
> accepts parameters. The ORA-01008 indicates that the caller did not

provide

> data bindings for all of the parameters.
>
> What is the application? Something you wrote or a third party.
> What connectivity layer are you using? (ADO, OO4O, OCI, OLE-DB, ODBC, etc)
>
> Post a sample of the app and SP source and maybe I can help.
>
> Hth,
>
> Cliff
>
> "Eddie" <edawad_at_hotmail.com> wrote in message
> news:pPxX5.702$2W6.159425_at_paloalto-snr1.gtei.net...
> > Hi,
> > Has anybody got the error "ORA-01008 not all variables bound" before?
> > If yes, what was the problem and How did you solve it?
> > Thanks for your help.
> > Eddie.
> >
> >
> >
> >
>
>

Received on Wed Dec 06 2000 - 16:07:16 CST