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
gupta0kumud
Messages: 3
Registered: July 2013
Location: GURGAON
Junior Member

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 messageGo to next message
Michel Cadot
Messages: 60065
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 messageGo to next message
gupta0kumud
Messages: 3
Registered: July 2013
Location: GURGAON
Junior Member

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
Michel Cadot
Messages: 60065
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
Previous Topic: help me to compile first proc
Next Topic: How to compile ProC ?
Goto Forum:
  


Current Time: Mon Dec 29 03:37:46 CST 2014

Total time taken to generate the page: 0.10712 seconds