Warning:Procedure with compilation errors

Question

Am i handling the below procedure in a correct way, there are some compilation errors, can someone help me the errors i did.

create or replace PROCEDURE CRangeproc(in_termid IN VARCHAR2,in_cardno IN VARCHAR2,outcount OUT NUMBER,outissu OUT VARCHAR2,outacq OUT VARCHAR2,CIssuer OUT SYS_REFCURSOR,CAcquirer OUT SYS_REFCURSOR)
AS
BEGIN
select count(*) into outcount from cardrangetable where PAN_LOW <= in_cardno AND  PAN_HIGH >=  in_cardno and terminal_id = in_termid;
if outCount = 1 then
select ISSUERTABLEID into outissu,ACQUIRERTABLEID into outacq from cardrangetable where PAN_LOW <= in_cardno AND  PAN_HIGH >= in_cardno and terminal_id = in_termid;
Open CIssuer FOR
select * from Issuer_tble where TERMINAL_ID = in_termid and ISSUERTABLEID = outissu;
CLOSE CIssuer;
Open CAcquirer FOR
select * from ACQUIRERTABLE where TERMINAL_ID = in_termid and ACQUIRERID = outacq;
CLOSE CAcquirer;
end if;
End CRangeproc;

Thankyou

What are the compilation errors? We're not telepathic and without your schema objects we cannot compile your code for ourselves. — APC, Nov 01 '12 at 12:31

score 0 · Answer 1 · answered Nov 01 '12 at 11:36

Hi in this part I see some error

 if Outcount = 1 then
select Issuertableid
  into Outissu, Acquirertableid
  into Outacq 
 from Cardrangetable
 where Pan_Low <= In_Cardno
   and Pan_High >= In_Cardno
   and Terminal_Id = In_Termid;

it should be like this

 if Outcount = 1 then
select Issuertableid,  Acquirertableid
  into Outissu, Outacq
 from Cardrangetable
 where Pan_Low <= In_Cardno
   and Pan_High >= In_Cardno
   and Terminal_Id = In_Termid;

and open/closes incorrectly used (maybe you're trying to use cursors)

 open Cissuer for
   ------
close Cissuer;
open Cacquirer for
     ----
close Cacquirer;

Im getting Encountered the symbol "THENSELECT" when expecting one of the following and Encountered the symbol "OPEN" I didnt get wats the problem here,Help please — 1001, Nov 01 '12 at 11:54

score 0 · Answer 2 · answered Nov 01 '12 at 12:44

It's one clause INTO per SELECT statement. You shouldn't close the REF CURSORs in this function. The program which consumes them needs to handle them.

This is what your code should like:

create or replace PROCEDURE CRangeproc
    (in_termid IN VARCHAR2
    ,in_cardno IN VARCHAR2
    ,outcount OUT NUMBER
    ,outissu OUT VARCHAR2
    ,outacq OUT VARCHAR2
    ,CIssuer OUT SYS_REFCURSOR
    ,CAcquirer OUT SYS_REFCURSOR)
AS
BEGIN
    select count(*) 
    into outcount 
      from cardrangetable 
    where PAN_LOW <= in_cardno 
    AND  PAN_HIGH >=  in_cardno 
    and terminal_id = in_termid;

    if outCount = 1 then
        select ISSUERTABLEID,ACQUIRERTABLEID 
        into outissu, outacq 
                from cardrangetable 
        where PAN_LOW <= in_cardno 
        AND  PAN_HIGH >= in_cardno 
        and terminal_id = in_termid;
        Open CIssuer FOR
            select * from Issuer_tble 
                          where TERMINAL_ID = in_termid 
                          and ISSUERTABLEID = outissu;
        Open CAcquirer FOR
            select * from ACQUIRERTABLE 
                          where TERMINAL_ID = in_termid 
                          and ACQUIRERID = outacq;
    end if;
End CRangeproc;

I think you need to learn to use carriage returns to improve the layout of your code.

Warning:Procedure with compilation errors

2 Answers2