Home » SQL & PL/SQL » SQL & PL/SQL » Problem to run store procedure - ORA-01008: not all variables bound
Problem to run store procedure - ORA-01008: not all variables bound [message #301626] Thu, 21 February 2008 03:27 Go to next message
pyabar
Messages: 3
Registered: February 2008
Junior Member
Good morning, first sorry for my English. I have the next query in a cursor that is call by store procedure who is in package. I confirm that the variables implicated arrived to query, when I run it directly in sql developer this query make insert by when I call by the procedure:
begin
  dw.upload_financialrefundlines( to_char(sysdate-3000, 'dd/mm/yyyy hh24:mi'), to_char(sysdate,         dd/mm/yyyy hh24:mi') );
end;

Return de subject error.

The process in package:
PROCEDURE upload_financialrefundlines (p_start_date  IN VARCHAR2 := null, p_end_date  IN VARCHAR2 := null) AS
BEGIN
    upload_table (c_pro_financialrefundlines, 'dw_financialrefundlines_frl', c_sql_ins_financialrefundlines, c_sql_del_financialrefundlines, p_start_date, p_end_date);
END upload_financialrefundlines;

PROCEDURE upload_table (p_process IN VARCHAR2, p_table_name IN VARCHAR2, p_sql_ins IN VARCHAR2, p_sql_del IN VARCHAR2 := NULL, p_start_date IN VARCHAR2 := NULL, p_end_date IN VARCHAR2 := NULL) AS
    -- log vars
    v_reg_read NUMBER := 0;
    v_reg_write NUMBER := 0;
    v_reg_delete NUMBER := 0;
    v_reg_errors NUMBER := 0;

    v_mesgid NUMBER := 1; -- counter of load messages
    v_loadid NUMBER; -- identifier of the current load process register

    v_start_date DATE;
    v_end_date DATE;
    v_tstart BINARY_INTEGER := 0;
    v_tend BINARY_INTEGER := 0;
    v_ttotal BINARY_INTEGER := 0;

BEGIN
        v_tstart := DBMS_UTILITY.get_time;
        v_start_date := SYSDATE;

        v_loadid := get_loadid();

        BEGIN
            -- remove all registers of table 'v_table_name'
            IF p_sql_del IS null OR p_sql_del = '' THEN
                EXECUTE IMMEDIATE
                    'SELECT COUNT(*) FROM ' || p_table_name INTO v_reg_delete;

                EXECUTE IMMEDIATE
                    'TRUNCATE TABLE ' || p_table_name;
            ELSIF p_sql_del = C_NO_DELETE THEN
                NULL;
                v_reg_delete := 0;
            ELSE
                EXECUTE IMMEDIATE
                    p_sql_del USING p_start_date, p_end_date;

                v_reg_delete := SQL%ROWCOUNT;
            END IF;

            IF p_start_date IS NOT null AND p_end_date IS NOT NULL THEN
                EXECUTE IMMEDIATE
                    'INSERT INTO '|| p_table_name || ' (' || p_sql_ins || ')' USING p_start_date, p_end_date ;
                     v_reg_write := SQL%ROWCOUNT;
            ELSE
                EXECUTE IMMEDIATE
                'INSERT INTO '|| p_table_name || ' (' || p_sql_ins || ')';
                v_reg_write := SQL%ROWCOUNT;
            END IF;

            v_reg_read := v_reg_write;

        EXCEPTION
            WHEN OTHERS THEN
                v_reg_errors := 1;
                put_message (v_loadid, v_mesgid,  SUBSTR(SQLERRM, 1, 1024));
                v_mesgid := v_mesgid + 1;
        END;

        v_end_date := SYSDATE;
        v_tend := DBMS_UTILITY.get_time;
        v_ttotal := v_tend - v_tstart;

        -- write log
        INSERT INTO dw_historyloads_hld VALUES (v_loadid, p_process,
        v_start_date, v_end_date, to_date(p_start_date, c_fmt_date),
        to_date(p_end_date, c_fmt_date), TO_CHAR(v_ttotal/100,'99990.00'),
        v_reg_read, v_reg_write, v_reg_delete, v_reg_errors, 0);

        COMMIT;
END upload_table;


cursor query:
 c_sql_ins_financialrefundlines CONSTANT VARCHAR2(7000):=
      ' SELECT substr(upper(historyrefund.country_code) || ''-'' || upper(historyrefund.partner_secondary_lang), 1, 5) as country,' ||	 
      ' lpad(historyrefund.partner_id, 4, 0)  || ''-'' || substr(historyrefund.partner_name, 1, 50)  as department,'  ||	  
      ' historyrefund.resalenumber AS resalenumber,'  || 	  
      ' historyrefund.createdonorder AS orderdate,' || 	   
      ' historyrefund.completed AS orderstatus,'  || 	   
      ' historyrefund.operation AS bussinescase,' || 	
      ' CASE WHEN historyrefund.operation IN (''CSR'',''RET'') THEN ' ||	    
      '   DECODE(historyrefund.status,2,historyrefund.createdon,null)'  || 	   
      ' ELSE '  ||	    
      '   DECODE(historyrefund.status,0,historyrefund.createdon,null)'|| 	   
      ' END AS requestdate,'  || 	   
      ' CASE WHEN historyrefund.operation IN (''CSR'',''RET'') THEN'  || 	   
      '   DECODE(historyrefund.status,2,historyrefund.agent,null)'  || 	   
      ' ELSE' || 	    
      '   DECODE(historyrefund.status,0,historyrefund.agent,null)'  ||		   
      ' END AS requestagent,' || 	
      ' DECODE(historyrefund.status,1,historyrefund.createdon,null) AS releasedate,'  || 	   
      ' DECODE(historyrefund.status,1,historyrefund.agent,null) AS releaseagent,' || 	  	
      ' dw.get_keyvalue(historyrefund.psdata,''returnreason'') AS returnreason,'  || 	  
      ' historyrefund.returnreason AS comments,'  ||
      ' CASE  WHEN historyrefund.operation IN (''CSR'',''RET'') THEN ' || 	
      '         DECODE(historyrefund.status,0,historyrefund.createdon,null) ' ||	   
      '       WHEN historyrefund.operation IN (''SREF'', ''REF'') THEN '  ||	   
      '         DECODE(historyrefund.status,1,historyrefund.createdon,null)'    || 	   
      '       ELSE '  ||	    
      '         NULL '   || 	   
      ' END AS approvedate, ' ||	   
      ' CASE  WHEN historyrefund.operation IN (''CSR'',''RET'') THEN ' ||	   
      '         DECODE(historyrefund.status,0,historyrefund.agent,null) ' ||	   
      '       WHEN historyrefund.operation IN (''SREF'', ''REF'') THEN '  ||	
      '         DECODE(historyrefund.status,1,historyrefund.agent,null) ' ||	   
      '       ELSE '  ||	    
      '         null '  ||	   
      ' END AS approveagent,' || 	  
      ' historyrefund.clientid AS customer, ' ||	
      ' CASE WHEN historyrefund.operation IN (''SREF'', ''REF'') THEN  '  ||
      '   ''Special Refund'' '  ||	
      ' ELSE '  ||	
      '   linerefund.itemname ' ||	
      ' END AS productname, ' ||	
      ' CASE WHEN historyrefund.operation IN (''SREF'', ''REF'') THEN ' ||	
      '   1 ' ||	
      ' ELSE '  ||	
      '   linerefund.itemid ' ||	
      ' END AS productid, ' ||	  
      ' CASE  WHEN historyrefund.operation IN (''SREF'', ''REF'') THEN ' ||	
      '       null '  ||	
      '       ELSE '  ||	
      '       CASE '  ||		 
      '         WHEN historyrefund.ccnumber=''-1'' THEN ''CASH ON DELIVERY''   '||	
      '         WHEN historyrefund.ccnumber=''-4'' THEN ''CALLBACK''  	' ||
      '         WHEN historyrefund.ccnumber=''-5'' THEN ''DIRECT DEBIT''   ' ||	
      '         WHEN historyrefund.ccnumber=''-6'' THEN ''INVOICE''   	' ||
      '         WHEN historyrefund.ccnumber=''-7'' THEN ''SPECIAL''   	' ||
      '         WHEN historyrefund.ccnumber=''-8'' THEN ''FINANCED''   ' ||	
      '         WHEN historyrefund.ccnumber=''-9'' THEN ''PRE-PAYMENT''   '  ||	
      '         WHEN historyrefund.ccnumber=''-3'' OR historyrefund.ccnumber > 0 THEN  	' ||
      '           CASE ' ||	
      '              WHEN historyrefund.cctype=''VISA'' AND historyrefund.tax1 = 1 THEN ''VISA - Credit'' ' ||
      '              WHEN historyrefund.cctype=''VISA'' AND historyrefund.tax1 = 2 THEN ''VISA - Debit''  ' ||	
      '              WHEN historyrefund.cctype IS NOT NULL THEN historyrefund.cctype '  ||		
      '              ELSE ''CREDIT CARD'' ' ||	
      '           END ' ||	
      '       ELSE ''NONE'' ' ||  		
      '       END ' ||	
      ' END AS paymentmethod,  	' ||
      ' CASE WHEN historyrefund.operation IN (''SREF'', ''REF'') THEN ' ||		
      '   null '  ||	
      ' ELSE '  ||	 
      '   historyrefund.method '  ||	
      ' END AS shipmentmethod,  ' ||	
      ' CASE WHEN historyrefund.operation IN (''SREF'', ''REF'') THEN ' ||		
      '   historyrefund.amount_returned	 - (historyrefund.amount_returned * (linerefund.vatrate/100))' ||	
      ' ELSE  ' ||		
      '   linerefund.net_revenue	' ||	
      ' END AS netprice,' ||	
      ' CASE WHEN historyrefund.operation IN (''SREF'', ''REF'') THEN ' ||		
      '   (historyrefund.amount_returned * (linerefund.vatrate/100)) ' ||		
      ' ELSE  ' ||		
      '   (linerefund.net_revenue * (linerefund.vatrate/100)) +' ||
      '   DECODE(historyrefund.shipping, 1, ' || 
      '     DECODE(ROW_NUMBER() OVER (PARTITION BY historyrefund.resalenumber ORDER BY historyrefund.resalenumber),1,'  ||	
      '     historyrefund.shippingcharges * (historyrefund.ship_tax_percent /100),0) ' ||
      '   ,0) ' ||	
      ' END AS vattax,	' ||	
      ' CASE WHEN historyrefund.operation IN (''SREF'', ''REF'') THEN ' ||		
      '   0 ' ||		
      ' ELSE  ' ||		
      '   DECODE(historyrefund.shipping, 1, ' ||	
      '     DECODE(ROW_NUMBER() OVER (PARTITION BY historyrefund.resalenumber ORDER BY historyrefund.resalenumber),1,' ||	
      '     (historyrefund.shippingcharges * 100) / (100 + historyrefund.ship_tax_percent), 0) '  ||	
      '   ,0) ' ||	
      ' END AS freigth,	' ||	
      ' CASE WHEN historyrefund.operation IN (''SREF'', ''REF'') THEN ' ||		
      '   0 ' ||	
      ' ELSE '  ||	
      '   0 ' ||	
      ' END AS specialservices, ' ||
      ' CASE WHEN historyrefund.operation IN (''SREF'', ''REF'') THEN ' ||		
      '   historyrefund.amount_returned	' ||	
      ' ELSE  ' ||		
      '   linerefund.net_revenue + '  ||	
      '   (linerefund.net_revenue * (linerefund.vatrate/100)) +	' ||	
      '   DECODE(historyrefund.shipping, 1, ' ||	
      '     DECODE(ROW_NUMBER() OVER (PARTITION BY historyrefund.resalenumber ORDER BY historyrefund.resalenumber)' ||	
      '     ,1, historyrefund.shippingcharges * 100 / (100 + historyrefund.ship_tax_percent) *(historyrefund.ship_tax_percent /100),0)'  ||	
      '   ,0) + ' ||				
      '   DECODE(historyrefund.shipping, 1, ' ||			
      '     DECODE(ROW_NUMBER() OVER (PARTITION BY historyrefund.resalenumber ORDER BY historyrefund.resalenumber)	' ||				
      '     ,1, historyrefund.shippingcharges * 100 / (100 + historyrefund.ship_tax_percent), 0) ' ||				
      '   ,0)+ '  ||	
      '    0 ' ||	 
      ' END AS totalrefunded,'  ||		 		
      ' CASE WHEN linerefund.normalprice > 0 THEN ''Y'' ELSE  ''N'' END AS promotion,  '  ||	
      ' linerefund.vatrate AS vatrate,' ||		  		
      ' DECODE(historyrefund.status,4,historyrefund.createdon,NULL) AS refunddate,' ||		  	
      ' DECODE(historyrefund.status,1,''RELEASED'',2,''RELEASED'',3,''CANCELLED'',4,''REFUNDED'',null) AS refundstatus' ||
      ' FROM ' ||
      ' ( SELECT ps.ref, ps.resalenumber, ps.operation, ps.status, ps.createdon, ps.agent, ps.returnreason, ps.psdata, ps.amount_returned, ps.shipping, '  ||	
      ' o.createdon AS createdonorder, o.completed, o.ccnumber, o.cctype, o.shippingcharges, o.ship_tax_percent, o.tax1,' ||		  
      ' p.partner_id, p.partner_secondary_lang, p.partner_name, p.clientid,'  ||		
      ' cl.country_code,' ||		
      ' sm.method'  ||	
      ' FROM ' ||		
      ' psales_history ps,' ||				
      ' orders o, ' ||				
      ' partners p, ' ||				 
      ' shipping_methods sm,' ||		 	
      ' currency_locality cl  '  ||			
      ' WHERE	' ||	
      ' ps.createdon BETWEEN   to_date(:1,''dd/mm/yyyy hh24:mi'')  AND to_date(:2,''dd/mm/yyyy hh24:mi'')' ||
      ' AND o.resalenumber=ps.resalenumber  ' ||		  		
      ' AND o.reserved1 = p.partner_id	' ||	  		
      ' AND sm.id = o.ship_method ' ||		  		
      ' AND p.partner_pbs_locality = cl.localityid '  ||	
      ' AND ps.operation not like ''CAN'' ' ||
      ' AND ps.status=1 '  ||
      ' ) historyrefund ' ||
      ' LEFT JOIN ' ||
      ' (SELECT psi.psalesid, oli.itemname, oli.itemid, oli.net_revenue, oli.vatrate, oli.normalprice' ||		
      ' FROM '  ||		
      ' psales_history ps_aux,	' ||			
      ' psales_items psi, ' ||				
      ' orderlineitem oli ' ||				
      ' WHERE ' ||
      ' ps_aux.createdon BETWEEN   to_date(:1,''dd/mm/yyyy hh24:mi'')  AND to_date(:2,''dd/mm/yyyy hh24:mi'')' ||
      ' AND ps_aux.ref = psi.psalesid	' ||	
      ' AND psi.returned = 1 '  ||		
      ' AND psi.lineid = oli.lineid ' ||
      ' AND ps_aux.operation not like ''CAN'' '||
      ' AND ps_aux.status=1 '  ||
      ' ) linerefund '  ||	
      ' ON ' ||	 
      '(historyrefund.ref = linerefund.psalesid)';


Thanks for your help

[EDITED by LF: added [code] tags to improve readability]

[Updated on: Thu, 21 February 2008 06:32] by Moderator

Report message to a moderator

Re: Problem to run store procedure - ORA-01008: not all variables bound [message #301630 is a reply to message #301626] Thu, 21 February 2008 03:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64124
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Reread your post.
What do you think we can think reading it.
For myself, it is "what a mess", not even the single explaination, how much time will it take to me to reverse engineer this... too much.

Help us to help you.
Format your post.
Give which line raises the error.
Give all what can be useful to understand where the error comes from.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).

Regards
Michel
Re: Problem to run store procedure - ORA-01008: not all variables bound [message #301631 is a reply to message #301630] Thu, 21 February 2008 03:36 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:
What do you think we can think reading it.
For myself, it is "what a mess",

For me it is: You must be joking. LOL, does he seriously expect us to trawl through that? No chance, I'll leave it to some other mug.
Re: Problem to run store procedure - ORA-01008: not all variables bound [message #301634 is a reply to message #301630] Thu, 21 February 2008 03:42 Go to previous messageGo to next message
pyabar
Messages: 3
Registered: February 2008
Junior Member
Thanks for your advice in fact I don't read nothig about it, I register in this moment. Ok so I read first and then if I think that my poor experience in Oracle is for this forum I will be continue.
Thanks again,
Re: Problem to run store procedure - ORA-01008: not all variables bound [message #301641 is a reply to message #301634] Thu, 21 February 2008 03:52 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
What I was thinking was :

Quote:

What's up with the mile-long "execute immediate" dynamic SQL packages these last couple of days? ./fa/1600/0/



Has someone out there told hundreds of developers that they should put all "their SQL" from the application into packages, because that would be better, and they just doing it by copy/pasting the SQL from the application into packages where they call it as dynamic SQL?

Which is just as unmaintainable and near-impossible to debug, as we see here.
Re: Problem to run store procedure - ORA-01008: not all variables bound [message #301646 is a reply to message #301641] Thu, 21 February 2008 04:03 Go to previous messageGo to next message
pyabar
Messages: 3
Registered: February 2008
Junior Member
Yes Thomas you are rigth, but can we do if you have to mantain code make by anothers and it will be resolve yet.
Re: Problem to run store procedure - ORA-01008: not all variables bound [message #301649 is a reply to message #301646] Thu, 21 February 2008 04:16 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Yep, it IS hard to maintain.

Basically when something goes wrong in dynamic SQL, you have no way of finding out what happened when the statement that is executed is not preserved either in some log table or file.

Which means, when you have to start debugging dynamic SQL you usually can't do it without first adding that logging yourself if it's not already there.

Which might take quite a while to figure out. AND you need a test system where you can mess with the code without further breaking things.

If you have that the first thing I would suggest is commenting out the whole "when others" exception handler and see if that produces a more helpful Error.


Re: Problem to run store procedure - ORA-01008: not all variables bound [message #301675 is a reply to message #301626] Thu, 21 February 2008 05:50 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
 c_sql_ins_financialrefundlines CONSTANT VARCHAR2(7000):=
 ' SELECT <...>'
 ' ps.createdon BETWEEN to_date(:1,''dd/mm/yyyy hh24:mi'') AND to_date(:2,''dd/mm/yyyy hh24:mi'')' ||
 <...>
 ' ps_aux.createdon BETWEEN to_date(:1,''dd/mm/yyyy hh24:mi'') AND to_date(:2,''dd/mm/yyyy hh24:mi'')' ||
 <...>;

 EXECUTE IMMEDIATE
'INSERT INTO '|| p_table_name || ' (' || p_sql_ins || ')' USING p_start_date, p_end_date ;

The error message is quite clear in this case. You have 4 binds in the query, but two expression in USING clause.

Not sure about ':' in the format mask; however much more better would be converting the column in the USING clause, like
 c_sql_ins_financialrefundlines CONSTANT VARCHAR2(7000):=
 ' SELECT <...>'
 ' ps.createdon BETWEEN :1 AND :2' ||
 <...>
 ' ps_aux.createdon BETWEEN :3 AND :4' ||
 <...>;

 EXECUTE IMMEDIATE
'INSERT INTO '|| p_table_name || ' (' || p_sql_ins || ')'
 USING to_date(p_start_date,'dd/mm/yyyy hh24:mi'), to_date(p_end_date,'dd/mm/yyyy hh24:mi'),
       to_date(p_start_date,'dd/mm/yyyy hh24:mi'), to_date(p_end_date,'dd/mm/yyyy hh24:mi');

Previous Topic: PL/SQL
Next Topic: funda of NULL
Goto Forum:
  


Current Time: Wed Dec 07 06:42:38 CST 2016

Total time taken to generate the page: 0.18646 seconds