Re: ORA-01008 not all variables bound ?

Glad to help Eddie, Good luck to you.

Cliff

"Eddie" <edawad_at_hotmail.com> wrote in message news:f2BX5.836$2W6.235845_at_paloalto-snr1.gtei.net...
> 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:46:41 CST