Home » SQL & PL/SQL » SQL & PL/SQL » PLS-00049: bad bind variable -- please help (plsql)
PLS-00049: bad bind variable -- please help [message #327413] Mon, 16 June 2008 05:53 Go to next message
sammeras
Messages: 28
Registered: September 2007
Location: Israel
Junior Member

Hello
I have package that insert data to table (after checking complicated logic)

when i run it like this:

variable out_var VARCHAR2;
begin
    leads_validation.sp_valid_leads('','','','','054','7318633','','','' ,'','name','CC',10,'','','',123456,'',:out_var);
end;
/
print out_var


It work great and pritn: # (insert success)


I trinig to to run the package from another procedure:
CREATE OR REPLACE PROCEDURE load_data_to_ext_prospect AS
    t_point NUMBER := 0; 
BEGIN
     --EXECUTE IMMEDIATE 'variable out_var VARCHAR2'USING OUT out_var;
    FOR rec IN (SELECT * FROM out_prospect
                    WHERE DATE_INSERTED > TO_DATE('01/01/2008 00:00:00','mm/dd/yyyy HH24:MI:SS') AND status LIKE 'O' AND rownum <=1
               ) 
    LOOP
        t_point := 1;
        leads_validation.sp_valid_leads(
            rec.first_name,
            rec.last_name, 
            null,
            rec.account_number,
            rec.area_home_phone,
            rec.home_phone,
            rec.area_work_phone,
            rec.work_phone,
            rec.area_mobile_phone,
            rec.mobile_phone,            
            rec.city,
            rec.user_code,
            rec.source,
            rec.campaign_code,
            rec.comments,            
            rec.name,
            rec.work_order_number,  
            rec.relationship,            
            :out_var -- Get '#' if sp_valid_leads success  (return-> #)
        );
        t_point := 2;
        UPDATE out_prospect SET status = 'S'
        WHERE out_prospect_id = rec.out_prospect_id;
        COMMIT; 
        t_point:=3;
    END LOOP;
    EXCEPTION WHEN OTHERS THEN
        EXTRACT_CONTROL.insert_ext_error(SQLERRM,t_point,'load_data_to_ext_prospect'); 
END;
/
sho err



I got error: 28/13 PLS-00049: bad bind variable 'OUT_VAR'

I searched the net for solution , but unfortuanatly i didn't found seriose one.
I tried many many of solution and treks, but no one works (execute immediate, using, ....)

Can anyone help me for this issu please ?

Best regards,
Sammer.
Re: PLS-00049: bad bind variable -- please help [message #327445 is a reply to message #327413] Mon, 16 June 2008 07:53 Go to previous messageGo to next message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
Is there any reason you can't just declare a variable (like you declare t_point) to receive the output?
Re: PLS-00049: bad bind variable -- please help [message #327446 is a reply to message #327413] Mon, 16 June 2008 07:56 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't use a SQL*Plus bind variable inside a procedure.
It must be a parameter of your procedure "load_data_to_ext_prospect".

Regards
Michel

[Updated on: Mon, 16 June 2008 07:57]

Report message to a moderator

Previous Topic: ORA-00001: unique constraint (PK) violated
Next Topic: Use Alias in Query
Goto Forum:
  


Current Time: Sat Dec 03 11:53:24 CST 2016

Total time taken to generate the page: 0.04684 seconds