This content has been marked as final. Show 2 replies
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 FORYou 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.
SELECT MWAGTN into AgentNo FROM ext_lsp_CASMWAGT WHERE MWCO = '''|||p_CompanyCode|||''' And
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;
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).