This discussion is archived
2 Replies Latest reply: Apr 24, 2013 1:11 AM by BillyVerreynne RSS

not able to see output

1003231 Newbie
Currently Being Moderated
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_Arp Guru
    Currently Being Moderated
    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
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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).

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points