2 Replies Latest reply: Apr 24, 2013 3:11 AM by Billy~Verreynne RSS

    not able to see output

    1003231
      Dear all,
      In the following procedure, i am not able to see output, if i done this by hardcoding values then working fine. while testing i got the error is :"no data found"
      could anyone help me to overcome this problem. iam using 11g.

      create or replace Procedure SP_GET
      (
      p_CompanyCode IN VARCHAR2,
      p_PolicyNo IN VARCHAR2,
      p_resultset OUT TYPES.cursortype
      )
      AS
      AgentNo VARCHAR2(9);
      Clientno VARCHAR2(10);
      Agentname CHAR(60);
      begin
      OPEN p_resultset FOR
      SELECT MWAGTN into AgentNo FROM ext_lsp_CASMWAGT WHERE MWCO = '''|||p_CompanyCode|||''' And
      MWPOLN = '''|||p_PolicyNo|||''' and
      Exists (SELECT MCCSTA FROM ext_LSP_CASCNTRM WHERE MCCO= '''|||p_CompanyCode|||''' And MCCNTR = '''|||p_PolicyNo|||''');
      IF AgentNo IS NOT NULL THEN
      SELECT CRCLTN into Clientno FROM ext_lsp_CMSUSREL
      WHERE CRCO= '''|||p_CompanyCode|||''' And CRALPH = 'AGT' And CRCTL1 = AgentNo;
      END IF;
      SELECT CMNAME into Agentname FROM ext_lsp_CMSCLNTM WHERE CMCLTN = Clientno ;
      dbms_output.put_line(AgentNo||Agentname||Clientno);
      end;
        • 1. Re: not able to see output
          _Karthick_
          Your procedure looks completely incorrect. It has lot of unnecessory code in it. Tell us what is your objective. What do you want SP_GET to do.
          OPEN p_resultset FOR
          SELECT MWAGTN into AgentNo FROM ext_lsp_CASMWAGT WHERE MWCO = '''|||p_CompanyCode|||''' And
          You are opening a cursor and at the same time you are selecting the value into a variable. That is wrong. And you don't have to enclose variables with single quotes in the where clause.

          All the SELECT statement in your procedure can be combined into a single statement like this
           select a.mwagtn as agentno 
               , c.crcltn as clientno
               , d.cmname as agentname 
            from ext_lsp_casmwagt a
            join ext_lsp_cascntrm b
              on a.mwco   = b.mcco
             and a.mwpoln = b.mccntr
            left 
            join ext_lsp_cmsusrel c
              on c.crctl1 = a.mwagtn
             and c.crco   = a.mwco
             and c.cralph = 'AGT'
            join ext_lsp_cmsclntm d
              on d.cmcltn = c.crcltn
           where mwco   = p_companycode
             and mwpoln = p_policyno;
          • 2. Re: not able to see output
            Billy~Verreynne
            Cursors are NOT resullt sets. Treating them as result sets invariable leads to hacks and unscalable and slow performing code.

            The use of TYPES.cursortype is superfleous as Oracle provides the PL/SQL data type sys_refcursor.

            Using the prefix SP is just silly. In SQL-Server, it means "system procedure" (not stored proc), and specifies an explicit resolution scope.

            Not only is the SP prefix totally unnecesary in Oracle, the old style Hungarian notation has ceased to exist in all modern programming languages as it is archaiec, illogical and just plain silly. (statements made by people line Linus and Bjarne, creators of Linux and C++ respectively).