by Eddi » Sun, 31 Dec 1899 09:00:00
by Cliff Dabrowsk » Sun, 31 Dec 1899 09:00:00
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
Quote:> 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.
by Eddi » Sun, 31 Dec 1899 09:00:00
-- 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 -- Output the results EXCEPTION CURSOR c1 is in_month varchar2(3); begin out_jan := 0; open c1; if in_month = 'Jan' Thanks for your help. > Your application is apparently making a call to a stored procedure that read more »
oct,out_nov,out_dec);
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));
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);
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_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;
tmp_vendor := in_vendor;
tmp_item := in_item;
tmp_org := in_org;
tmp_buyer := in_buyer;
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;
loop
fetch c1 into in_month, in_qty;
exit when c1%NOTFOUND;
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@rpt1 DATAMART_ITEM_ATTRIBUTES,
DATAMART.VENDOR_ITEMS@rpt1 DATAMART_VENDOR_ITEMS,
DATAMART.MASTER_SCHEDULE@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@rpt1 DATAMART_ITEM_ATTRIBUTES,
DATAMART.VENDOR_ITEMS@rpt1 DATAMART_VENDOR_ITEMS,
DATAMART.MASTER_SCHEDULE@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.
Eddie.
"Cliff Dabrowski" <clif...@my-deja.com> wrote in message news:QqyX5.135596$hD4.33495744@news1.rdc1.mi.home.com...
> Eddie,
...
by Cliff Dabrowsk » Fri, 08 Dec 2000 09:28:28
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.showFrameDocum...
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=3...
&p_showHeader=1&p_showHelp=1
Hth,
Cliff
> -- Now call the stored program
> esco_web_po_plan_orders_mon(
> '1580',
> '4078675',
> 'DNV',
> 'Brown, Richard S',
> -- 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@rpt1 DATAMART_ITEM_ATTRIBUTES,
> DATAMART.VENDOR_ITEMS@rpt1 DATAMART_VENDOR_ITEMS,
> DATAMART.MASTER_SCHEDULE@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,
read more »
by Eddi » Fri, 08 Dec 2000 09:42:51
Thanks for your time, you've been very much helpful.
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:
> 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:
> Hth,
> Cliff
> "Eddie" <eda...@hotmail.com> wrote in message
> news: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',
> > -- 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@rpt1 DATAMART_ITEM_ATTRIBUTES,
> > DATAMART.VENDOR_ITEMS@rpt1 DATAMART_VENDOR_ITEMS,
> > DATAMART.MASTER_SCHEDULE@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
read more »
by Cliff Dabrowsk » Fri, 08 Dec 2000 09:46:41
Cliff
> Thanks for your time, you've been very much helpful.
> Eddie.
> "Cliff Dabrowski" <clif...@my-deja.com> wrote in message
> news:MQAX5.135755$hD4.33565636@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:
> > 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:
> > Hth,
> > Cliff
> > "Eddie" <eda...@hotmail.com> wrote in message
> > news: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',
> > > -- 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@rpt1 DATAMART_ITEM_ATTRIBUTES,
> > > DATAMART.VENDOR_ITEMS@rpt1 DATAMART_VENDOR_ITEMS,
read more »
1. ORA-01008: not all variables bound error in WebLOgic connection pooling
I am getting "ORA-01008: not all variables bound" when using the
prepareStatement method when connected via connection pooling with an
OCI driver, but it works when using a direct connection with a thin
ORACLE JDBC driver (classes12.zip). This failure is on both NT and
Solaris 2.6.
Here's the relevant code snippets:
public static Connection conn;
...
Context myCtx = new InitialContext();
javax.sql.DataSource ds = (javax.sql.DataSource)myCtx.lookup("Keown");
conn = ds.getConnection();
...
PreparedStatement myPs = null;
String myAgmntSQL = "select start_dt, end_dt " +
"from sma_agmnts " +
"where agmnt_sys_id = ?";
myPs = myConnect.conn.prepareStatement(myAgmntSQL);
If I hardcode a value like 767 instead of ?, there is no error.
I believe that the error is occurring in the prepareStatement method
as I've inserted system outs before and after it to pin down where it
was failing.
The weblogic.properties file contains this:
weblogic.jdbc.connectionPool.banana=\
url=jdbc:weblogic:oracle,\
driver=weblogic.jdbc.oci.Driver,\
loginDelaySecs=1,\
initialCapacity=1,\
maxCapacity=10,\
capacityIncrement=2,\
allowShrinking=true,\
shrinkPeriodMins=15,\
refreshMinutes=10,\
testTable=dual,\
props=user=pcm8744;password=password;server=dldv
weblogic.allow.reserve.weblogic.jdbc.connectionPool.banana=ba,guest
# Data Source definition for banana
weblogic.jdbc.DataSource.Keown=banana
--
Visit Caribbean Aviation:
http://www.caribbeanaviation.com/
2. Sort problem
3. ORA-01008: not all variables bound .
4. Need suggestions for performance tuning
5. Urgent !!!! -- ORA-01008: not all variables bound
6. Sql Question
7. ORA-01008: not all variables bound
10. OCI: Binding collections (varray), Ora 1008: not all variables bound
11. help!!- jdbc prepared statement=error ora-01008
12. sql loader and ora-01008 (cross posted to tools)