Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Re: ORA-01008 not all variables bound ?
Thank you Cliff for your reply, here is the PL/SQL that calls the stored procedure:
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;
out_jan,out_feb,out_mar,out_apr,out_may,out_jun,out_jul,out_aug,out_sep,out_ oct,out_nov,out_dec);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM);
RAISE;
END;
ORA-01008: not all variables bound ORA-02063: preceding line from RPT1 ORA-06512: at line 36stored procedure.
-----------------
RPT1 is a database link that is used in a view. The view is used in the
(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,
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;
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
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_SCHEDULEWHERE DATAMART_MASTER_SCHEDULE.A$ITEM_NUMBER = DATAMART_ITEM_ATTRIBUTES.A$ITEM_NUMBER
('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
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
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_SCHEDULEWHERE DATAMART_MASTER_SCHEDULE.A$ITEM_NUMBER = DATAMART_ITEM_ATTRIBUTES.A$ITEM_NUMBER
('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||'-OSP1' =DATAMART_VENDOR_ITEMS.A$ITEM_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')---
----------------------------------------------------------------------------
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 notprovide
> 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