Ref Cursor Exceptions

Question

I have a couple of questions arounbd ref_cursors. Below is a ref_cursor that returns a a single row to a Unix calling script based on what is passed in and although the select looks a little untidy, it works as expected.

My first question is that in the select I join to a lookup table to retrieve a single lookup value 'trigram' and on testing found that this join will occasionally fail as no value exists. I have tried to capture this with no_data_found and when others exception but this does not appear to be working.

Ideally if the join fails I would still like to return the values to the ref_cursor but add something like 'No Trigram' into the trigram field - primarily I want to capture exception.

My second question is more general about ref_cursors - While initially I have created this in its own procedure, it is likely to get called by the main processing procedure a number of times, one of the conditions requires a seperate select but the procedure would only ever return one ref_cur when called, can the procdure ref_cur out be associated with 2 queries.

CREATE OR REPLACE PROCEDURE OPC_OP.SiteZone_status
    (in_site_id IN AW_ACTIVE_ALARMS.site_id%TYPE
    ,in_zone_id IN AW_ACTIVE_ALARMS.zone_id%TYPE
    ,in_mod     IN AW_ACTIVE_ALARMS.module%TYPE
    ,p_ResultSet        OUT  TYPES.cursorType
    )
AS
 BEGIN
 OPEN p_ResultSet FOR
 SELECT a.site_id,'~',a.zone_id,'~',b.trigram,'~',a.module,'~',a.message_txt,'~',a.time_stamp
 FROM AW_ACTIVE_ALARMS a, AW_TRIGRAM_LOCATION b
 WHERE a.site_id = b.site_id 
 AND a.zone_id = b.zone_id
 AND a.site_id = in_site_id 
 AND a.zone_id = in_zone_id
 AND a.module LIKE substr(in_mod,1,3)||'%'
 AND weight = (select max(weight) from AW_ACTIVE_ALARMS c
               WHERE c.site_id = in_site_id 
               AND c.zone_id = in_zone_id
               AND c.module LIKE substr(in_mod,1,3)||'%');
EXCEPTION
    WHEN OTHERS
    THEN
        DBMS_OUTPUT.PUT_LINE('No Data Found');

END SiteZone_status;

I have modified my code to adopt answers provided and this now works as expected as a standalone procedure within my package, which when called via a UNIX script using: v_process_alarm=$(sqlplus -s user/pass < set colsep ',' set linesize 500 set pages 0 feedback off; set serveroutput on; VARIABLE resultSet REFCURSOR EXEC alarm_pkg.rtn_active_alarm($site,$zone,$module, :resultSet); PRINT :resultSet EOF )

However the procedure returning the ref cursor is to be called from the main processing procedure as I only want to return values if certain criteria are met. I have add an out refcurosr to my main procedure and set a variable to match, I then call my ref cursor procedure from here but this fails to compile with the message 'Wrong number or types of argument in call'

My question is what is the correct way to call a procedure that has out refcursor from within a procedure and then return these values from there back to the calling script.

Answer 1

You can use a left outer join to your look-up table, which is clearer if you use ANSI join syntax rather than Oracle's old syntax. If there is no record in AW_TRIGRAM_LOCATION then b.trigram will be null, and you can then use NVL to assign a dummy value:

 OPEN p_ResultSet FOR
 SELECT a.site_id,'~',a.zone_id,'~',NVL(b.trigram, 'No Trigram'),'~',
   a.module,'~',a.message_txt,'~',a.time_stamp
 FROM AW_ACTIVE_ALARMS a
 LEFT JOIN AW_TRIGRAM_LOCATION b
 ON b.site_id = a.site_id 
 AND b.zone_id = a.zone_id
 WHERE a.zone_id = in_zone_id
 AND a.module LIKE substr(in_mod,1,3)||'%'
 AND weight = (select max(weight) from AW_ACTIVE_ALARMS c
               WHERE c.site_id = in_site_id 
               AND c.zone_id = in_zone_id
               AND c.module LIKE substr(in_mod,1,3)||'%');

You won't get NO_DATA_FOUND opening a cursor, only when you fetch from it (depending on what is actually consuming this). It's a bad idea to catch WHEN OTHERS anyway - you would want to catch WHEN NO_DATA_FOUND, though it wouldn't help here. And using dbms_output to report an error relies on the client enabling its display, which you can't generally assume.

Answer 2

Oracle doesn't know whether a query will return rows until you fetch from the cursor. And it is not an error for a query to return 0 rows. So you will never get a no_data_found exception from opening a cursor. You'll only get that if you do something like a select into a local variable in which case a query that returns either 0 or more than 1 row is an error.

It sounds like you want to do an outer join to the AW_TRIGRAM_LOCATION table rather than a current inner join. This will return data from the other tables even if there is no matching row in aw_trigram_location. That would look something like this (I have no idea why every other column is a hard-coded tilde character, that seems exceptionally odd)

SELECT a.site_id,'~',
       a.zone_id,'~',
       nvl(b.trigram, 'No Trigram Found'),'~',
       a.module,'~',
       a.message_txt,'~',
       a.time_stamp
  FROM AW_ACTIVE_ALARMS a
       LEFT OUTER JOIN AW_TRIGRAM_LOCATION b
         ON( a.site_id = b.site_id AND 
             a.zone_id = b.zone_id )
 WHERE a.site_id = in_site_id 
   AND a.zone_id = in_zone_id
   AND a.module LIKE substr(in_mod,1,3)||'%'
   AND weight = (select max(weight) 
                   from AW_ACTIVE_ALARMS c
                  WHERE c.site_id = in_site_id 
                    AND c.zone_id = in_zone_id
                    AND c.module LIKE substr(in_mod,1,3)||'%');

I'm not quite sure that I understand your last question. You can certainly put logic in your procedure to run a different query depending on an input parameter. Something like

IF( <<some condition>> )
THEN
  OPEN p_ResultSet FOR <<query 1>>
ELSE
  OPEN p_ResultSet FOR <<query 2>>
END IF;

Whether it makes sense to do this rather than adding additional predicates or creating separate procedures is a question you'd have to answer.