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

    Warning:Procedure with compilation errors

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

          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
          http://htendam.wordpress.com                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
          • 2. Re: Warning:Procedure with compilation errors
            970843
            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;
                      EmpcurParam.GetValue(&EmpcurDynaset);
                      const char *issuid;
                      OField Enamefld = EmpcurDynaset.GetField("ISSUERTABLEID");
                      Enamefld.GetValue(&issuid);


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