Re: ORA-01008 not all variables bound ?

Yes, I believe it is Bug #974519.

Thanks for your time, you've been very much helpful.

Eddie.

"Cliff Dabrowski" <cliffdw_at_my-deja.com> wrote in message news:MQAX5.135755$hD4.33565636_at_news1.rdc1.mi.home.com...
> 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:42:51 CST