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 previous 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

 
Read Message
Read Message
Read Message
Read Message
Previous Topic: help me to compile first proc
Next Topic: How to compile ProC ?
Goto Forum:
  


Current Time: Mon Jan 27 01:28:40 CST 2020