Re: Oracle Stored Proc returning null

Terren wrote:
> I am a total noob when it comes to stored proces in oracle. I created
> this stored proc but it is returning null. It is just supposed to join
> all the records into one string. And I cannot use a function
>
>
> Procedure SPECIFIEDMOTORVALUE
> (V_POLICY_KEY IN VARCHAR2,
> V_SECTION_KEY IN NUMBER,
> V_SUBSECTION_KEY IN NUMBER,
> V_ITEM_SEQ IN NUMBER,
> V_EQUIPMENTDESC OUT VARCHAR2) IS
>
> V_TEMPDESC VARCHAR2(2000);
>
> CURSOR CUR_SPECMOTOR IS
> SELECT MTS_DESCRIPTION
> FROM MOTORSPECIFIED
> WHERE MTS_POLICY_CDE= V_POLICY_KEY AND
> MTS_SECTION_CDE = V_SECTION_KEY AND
> MTS_SUBSECTION_CDE = V_SUBSECTION_KEY AND
> MTS_ITEM_SEQ = V_ITEM_SEQ;
>
> BEGIN
> FOR REC_SPECMOTOR in CUR_SPECMOTOR LOOP
> V_TEMPDESC :=V_TEMPDESC + ', ' +
> REC_SPECMOTOR.MTS_DESCRIPTION;
> END LOOP;
>
> V_EQUIPMENTDESC := V_TEMPDESC;
>
> EXCEPTION
> WHEN OTHERS THEN
> V_EQUIPMENTDESC :='';
> END;

• Concatenation operator in PL/SQL is || and not +. Your code is most likely falling into exception block.
• Use dbms_output package for debugging your PL/SQL code

Regards
/Rauf Received on Tue Jul 04 2006 - 04:20:08 CDT