CODEWITHSUNDEEP
databaseoracle-databasestored-procedures
D.madushanka
D.madushanka

Reputation: 563

Bad bind variables in oracle stored procedure

I am totally new to stored procedures. I have a query and I wanna use it for a web service. I am going to use Nhibernate. I have tried couple of procedures and worked well. But this one giving me problems. I tried to compile the stored procedure but giving me errors.

create or replace
PROCEDURE GET_RSM_LIFE_AGENT_DUES (p_recordset OUT SYS_REFCURSOR, :Year IN number, :Month IN number, :Branch IN number, :Agency IN number) AS
BEGIN
OPEN p_recordset for

select     :Year  as required_year, :Month  as required_month, 
           case when pmagt = 0 then D.branch_code else E.branch_code end as branch_code,
           case when pmagt = 0 then D.branch_name else E.branch_name end as branch_name,
           case when pmagt = 0 then D.region else E.region end as region,
           pmagt as agency,
           status || ' ' || int || ' ' || name as agent_name,
           pmpol as policy_no,
           pmcom as commence_date,
           pmtrm as term,
           pmmod as policy_mode,

            case WHEN pmtbl not in (51,22,74,75,76) and ((:Year - SUBSTR (pmcom,1,4)) * 12 + to_number(:Month) - SUBSTR (pmcom,-4,2)) < 12 then 'FYR'
            WHEN pmtbl not in (51,22,74,75,76) and ((:Year - SUBSTR (pmcom,1,4)) * 12 + to_number(:Month) - SUBSTR (pmcom,-4,2)) >= 12  then 'Renewal' else '' end as premium_type,

           case when llprm is not null and pmprm < llprm then llprm else pmprm end as due_Premium,
           case when llprm is not null then llprm else 0 end as due_paid_premium            

    FROM   lphs.premast A left outer join lclm.ledger B on (A.pmpol = B.llpol) and (to_number(:Year || :Month) = lldue)
           left outer join agent.agent C on (A.pmagt = C.agency)
           left outer join BAU.SLIC_BRANCH_LIST D on (A.pmobr = D.csp_code)
           left outer join BAU.SLIC_BRANCH_LIST E on (C.branch = E.csp_code)

    WHERE   add_months(to_date(PMCOM,'YYYYMMDD'),PMTRM*12) >= add_months( to_date(:Year || :Month || 01 ,'YYYYMMDD'),1) 
            and to_date(pmcom,'yyyymmdd') < to_date(:Year || :Month || 01 ,'YYYYMMDD') 
            and pmmod <> 5
            and stid in ('Ag' , 'ME', 'Or')
            and case when to_date(pmcom,'yyyymmdd') >= to_date(:Year || :Month || 01 ,'YYYYMMDD') then 'N' 
                when pmmod = 4 then 'Y'
                when pmmod = 3 and remainder ( abs( to_number ( substr (pmcom, 5,2) )  - to_number ( :Month ) )   , 3 ) =0 then 'Y' 
                when pmmod = 2 and remainder ( abs( to_number ( substr (pmcom, 5,2) )  - to_number ( :Month ) )   , 6 ) =0 then 'Y' 
                when pmmod = 1 and remainder ( abs( to_number ( substr (pmcom, 5,2) )  - to_number ( :Month ) )   , 12 ) =0 then 'Y' else 'N' end = 'Y'

           and case when pmagt = 0 then D.branch_code else E.branch_code end = :Branch      
          and pmagt = :Agency    

END GET_RSM_LIFE_AGENT_DUES;

Error.....

Error(2,67): PLS-00049: bad bind variable 'YEAR'
Error(2,67): PLS-00103: Encountered the symbol "" when expecting one of the following:     <an identifier> <a double-quoted delimited-identifier>    current delete exists prior 
Error(2,84): PLS-00049: bad bind variable 'MONTH'

Upvotes: 1

Views: 762

Answers (1)

Littlefoot
Littlefoot

Reputation: 142788

Remove colons from within the procedure. Those are procedure's parameters, so use them as such. Pass their values as e.g. 

declare
  l_out sys_refcursor;
begin
  GET_RSM_LIFE_AGENT_DUES (l_out, :Year, :Month, :Branch, :Agency);
end;

Upvotes: 2

Related Questions