Home » Developer & Programmer » Precompilers, OCI & OCCI » not all variable bound (oracle 10g ,aix )
not all variable bound [message #595767] |
Fri, 13 September 2013 06:50 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
|
Hi
I am Kumud GUpta and got a problem of not all variable bound while executing functions or method in pro*C in oracle.Please help me for giving your valuable answer...
#######################################################
int Get_Part()
{
ftrace("\nGet_Part()");
ftrace("\n\tline# entered = '%d'", td.line_nbr);
EXEC SQL BEGIN DECLARE SECTION;
char newquery[3000];
char temp_bin[9];
int temp_pri;
int binorder;
int temp_qty;
EXEC SQL END DECLARE SECTION;
sprintf(newquery,
" SELECT DISTINCT ord_unit,"
"line_nbr,"
"ord_desc,"
"NVL(line_qty,0),"
"NVL(pick_qty,0),"
" txn_nbr,"
" bin,"
"bin_pri,"
"rem_qty FROM "
" ("
" SELECT DISTINCT od.ord_unit,"
" od.line_nbr,"
"NVL(od.line_qty,0) line_qty,"
"NVL(od.pick_qty,0) pick_qty,"
"od.txn_nbr,"
" od.ord_prod_id,"
" i.bin,"
" i.binorder,"
"i.bin_pri,"
" CASE WHEN "
"(NVL(i.inv_qty,0) + NVL(od.pick_qty,0) - NVL(od.line_qty,0)) < 0 THEN NULL "
" ELSE (NVL(i.inv_qty,0) + NVL(od.pick_qty,0) - NVL(od.line_qty,0)) END "
" rem_qty "
" FROM OLPADMIN.ORD_DTL od, "
"(SELECT i.unit,"
" i.bin,CASE WHEN i.bin = 'ZPWALL' THEN 13 "
"WHEN g.bin_order = 1 THEN TO_NUMBER(h.binmax) - TO_NUMBER(SUBSTR(i.bin,3,2)) "
"WHEN g.bin_order = 0 THEN "
" TO_NUMBER(SUBSTR(i.bin,3,2)) END binorder,"
" i.inv_qty,"
" b.bin_pri "
" FROM olpadmin.inv_loc i, "
" olpadmin.bins b , olpadmin.nds_bin_order g,"
"(SELECT SUBSTR(bin,1,2) bin_prefix, "
" MAX(SUBSTR(bin,3,2)) binmax FROM "
" olpadmin.bins WHERE owner = :customer.ordTypeStr "
" AND bin_grp = 'MAIN' AND "
"UPPER(SUBSTR(bin,3,2)) = LOWER(SUBSTR(bin,3,2)) "
" GROUP BY SUBSTR(bin,1,2) ) h "
" WHERE "
" i.bin = b.bin AND "
" SUBSTR(i.bin,1,2) = g.bin_prefix (+) AND "
" g.bin_prefix = h.bin_prefix (+) AND "
" UPPER(SUBSTR(i.bin,3,2)) = LOWER(SUBSTR(i.bin,3,2)) AND "
" i.owner = :customer.ordTypeStr "
" AND i.stock_loc = 'BSTOCK' "
" AND b.bin_grp = 'MAIN') i "
" WHERE od.ORD_NBR = '%s' "
" AND od.ORD_UNIT NOT LIKE 'NO_LOAD' "
" AND od.ord_unit = i.unit(+) "
" AND NVL(od.line_qty,0) > NVL(od.pick_qty,0) "
" AND od.ORD_STATUS = 'W')"
" ORDER BY bin_pri ASC, SUBSTR(bin,1,2) DESC NULLS LAST,"
" binorder DESC NULLS LAST,"
" rem_qty ASC ",td.ord_nbr);
EXEC SQL PREPARE stmt from :newquery;
EXEC SQL DECLARE GET_PARTS CURSOR FOR stmt;
EXEC SQL OPEN GET_PARTS;
if (rc = DBStatus(&stat) != SUCCESS) {
EXEC SQL CLOSE GET_PARTS;
EXEC SQL COMMIT;
" AND b.bin_grp = 'MAIN') i "
" WHERE od.ORD_NBR = '%s' "
" AND od.ORD_UNIT NOT LIKE 'NO_LOAD' "
" AND od.ord_unit = i.unit(+) "
" AND NVL(od.line_qty,0) > NVL(od.pick_qty,0) "
" AND od.ORD_STATUS = 'W')"
" ORDER BY bin_pri ASC, SUBSTR(bin,1,2) DESC NULLS LAST,"
" binorder DESC NULLS LAST,"
" rem_qty ASC ",td.ord_nbr);
EXEC SQL PREPARE stmt from :newquery;
EXEC SQL DECLARE GET_PARTS CURSOR FOR stmt;
EXEC SQL OPEN GET_PARTS;
if (rc = DBStatus(&stat) != SUCCESS) {
EXEC SQL CLOSE GET_PARTS;
EXEC SQL COMMIT;
ftrace("\n\tERROR opening GET_PARTS cursor");
POPUP_ERROR_("\n\nNO MORE PARTS\nEXIST FOR THIS LINE");
return ERROR;
}
EXEC SQL FETCH GET_PARTS
INTO
:td.part_nbr,
:td.line_nbr,
:td.part_desc,
:td.ord_qty,
:td.pick_qty,
:td.txn_nbr,
:td.prod_id,
:temp_bin,
:binorder,
:temp_qty;
EXEC SQL CLOSE GET_PARTS;
EXEC SQL COMMIT;
ftrace("\n\tERROR FECHING GET_PARTS cursor");
return ERROR;
}
EXEC SQL CLOSE GET_PARTS;
EXEC SQL COMMIT;
return SUCCESS;
##################################################################
and when I have trace this function I got this so u can take help from that
InitModule():
DBConnect()
GetUserInfo('hmatlapu')
BEGIN PromptCust()
GetCust()
SelectCust()
GetBenchNum () :
PromptBench() : Bench Entered <NONE> Label Que <test> Packing List Que <test>
RESET_LINE()
Get_ORD_NBR()
BACK_ORDER_LINE()
LINE# entered = '0'
ORD# entered = ''
ORD# entered = ''
LINE# entered = '0'
ORD entered = '8000010'
Validate_Ord_Nbr()
LOCK_LINE()
Get_Part()
"NDS_picking_1441836.out" [Last line is not complete] 28 lines, 724 characters
DB ERROR CODE: -1008
ORA-01008: not all variables bound
ERROR opening GET_PARTS cursor
####################################################
thanks in advance for sending me valuable help
[Edit MC: add code tags]
[Updated on: Fri, 13 September 2013 10:48] by Moderator Report message to a moderator
|
|
|
Re: not all variable bound [message #595792 is a reply to message #595767] |
Fri, 13 September 2013 10:56 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.
I advise you to also format your SQL statement in your Pro*C code, these ones are unreadable and unmaintainable.
In your first statement you have (maybe among others but I stopped there) ":customer.ordTypeStr" which indicates you use a bind variable "customer" which is a record containing an "ordTypeStr" field. This variable is declared nowhere, so the error.
In addition, you use "%s" to insert constant string inside your statement, this is very bad (except in very specific cases), a bind variable must be used there.
Regards
Michel
|
|
|
Re: not all variable bound [message #596090 is a reply to message #595792] |
Tue, 17 September 2013 23:28 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
|
Thanks michel for great support but i have checked all these but i am not able to found exact error for variable not found.so if u help me regarding me for that I shall be highly oblidge to u.
|
|
|
Re: not all variable bound [message #596096 is a reply to message #596090] |
Wed, 18 September 2013 00:35 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Sorry I don't understand what you did'nt find. I gave you the reason: usage in statement of variable that is not declared.
Regards
Michel
|
|
|
Goto Forum:
Current Time: Sat Sep 07 18:37:31 CDT 2024
|