3 Replies Latest reply on Jan 7, 2013 9:50 AM by 970843

    Warning:Procedure with compilation errors

      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)
      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
        • 1. Re: Warning:Procedure with compilation errors
          Herald ten Dam

          there are mistakes, but wouldn't it be better if you try to learn the Oracle Syntax of SQL and pl/sql? The mistakes you make are very basic, and a little more knowledge of pl/sql will solve these errors.

          Furthermore you can format the code for a better read for us with
          Your error is in the select .. into, first the columns then the into (see http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/selectinto_statement.htm#i36066)
          Further, you open the cursor, but you also closes it. What do you need for your output, the caller will get no cursor, because it is closed.
          Herald ten Dam
          • 2. Re: Warning:Procedure with compilation errors
            I corrected it now in SELECT>
            select ISSUERTABLEID,ACQUIRERTABLEID into outissu,outacq ....
            and im getting all the columns in cursor and going to use it afterwards like below

            oresult oot = odb.ExecuteSQL("Begin CRangeproc(...,:CIssuer,...); End;");
                      OParameter EmpcurParam = odb.GetParameters().GetParameter("CIssuer") ;
                      ODynaset EmpcurDynaset;
                      const char *issuid;
                      OField Enamefld = EmpcurDynaset.GetField("ISSUERTABLEID");

            Am i doing in correct way?
            • 3. Re: Warning:Procedure with compilation errors
              I found out solution myself