Re: ORA-01008 not all variables bound ?

Eddie,

I don't see any problems within the code you sent (but it is impossible to validate totally without tables and data) and I believe you are focused correctly on the problem.

You stated that the problem went away when you removed the database link so I would suggest comparing the table structures of the tables referenced locally with those referenced via the link to make sure that all attributes (name, type, len, precision, etc) match exactly.

In addition, although you appear to be doing so I wanted to just remind you to make sure that you initialize all parameters used in a SP call before making the call as this can sometimes result in the ORA-01008 error.

I did find some reference to similar problems on MetaLink.

The first issue was fixed in 8.0.6 but you never know if it may have cropped back up or you may be on a different platform. Take a look at this link to see if this applies to you: http://support.oracle.com/metalink/plsql/ml2_documents.showFrameDocument?p_d atabase_id=BUG&p_id=720059

The other sounds more like your problem. Bug #974519 that was fixed in 8.1.6.2 patchset and also 8.1.7 release. Take a look at this link for the forum thread: http://support.oracle.com/metalink/plsql/ml2_documents.showFOR?p_id=3473.996 &p_showHeader=1&p_showHelp=1

Hth,

Cliff

"Eddie" <edawad_at_hotmail.com> wrote in message news:nMyX5.729$2W6.180024_at_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
> 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||'-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 - 18:28:28 CST