plsql function below (packaged) giving error, while placing error, and after begin statement, can anybody suggest

Anshul Lavaniya 0
Viewed 23
Function GET_SIC_ATTRIBUTES_VALUE (p_recipe_id IN NUMBER) RETURN RECIPE_VIEW_SET pipelined
IS 
 BEGIN
   v_sic_wafer_type := get_recipe_attr_rawvalue(p_recipe_id,'SIC_WAFER_TYPE',v_ts);
   v_sic_target_wafer_temp := get_recipe_attr_rawvalue(p_recipe_id,'SIC_TARGET_WAFER_TEMPERATURE',v_ts);
   v_sic_temp_mode := get_recipe_attr_rawvalue(p_recipe_id,'SIC_TEMPERATURE_MODE',v_ts);


IF v_sic_temp_mode = c_hot AND v_sic_wafer_type > 0 AND v_sic_target_wafer_temp > 0 
THEN

for i in
( 
 SELECT 
               ATTRIBUTE_NAME,
               PARAMETER_VALUE as ATTRIBUTE_VALUE
 FROM RECIPE_SIC_PARAMETER_SETTINGS
               UNPIVOT INCLUDE NULLS
                   (PARAMETER_VALUE
                   FOR ATTRIBUTE_NAME
                   IN (SIC_HEAT_SOAK_TIME,
                      SIC_COOL_DWELL_TIME,
                      SIC_PRE_CLAMP_SOAK_TIME,
                      SIC_INNER_ZONE_TEMPERATURE_MIN,
                      SIC_INNER_ZONE_TEMPERATURE,
                      SIC_INNER_ZONE_TEMPERATURE_MAX,
                      SIC_OUTER_ZONE_TEMPERATURE_MIN,
                      SIC_OUTER_ZONE_TEMPERATURE,
                      SIC_OUTER_ZONE_TEMPERATURE_MAX,
                      SIC_PYROMETER_ENABLED,
                      SIC_PYROMETER_MIN_START_TEMP,
                      SIC_PYROMETER_MAX_START_TEMP,
                      SIC_INNER_ZONE_TEMP_INTLK,
                      SIC_OUTER_ZONE_TEMP_INTLK,
                      SIC_WAFER_PREHEAT_ENABLED))
         WHERE PERIOD_END IS NULL
                  AND SIC_WAFER_TYPE = v_sic_wafer_type
               AND SIC_TARGET_WAFER_TEMPERATURE = v_sic_target_wafer_temp;
                ELSE
  SELECT attribute_name, recipe_value_num as attribute_value,
         CASE
             WHEN ra.enum_list IS NOT NULL 
             THEN (SELECT choice_str
                     FROM recipe_enums_v
                    WHERE enum_list = ra.enum_list
                      AND choice_id = rd.recipe_value_num)
             ELSE TO_CHAR (NULL)
           END AS enum_value,
           ra.datatype_id  
                FROM recipe_attributes ra, recipe_data rd
                WHERE ra.attribute_id = rd.attribute_id
                  AND rd.recipe_id = p_recipe_id
                AND rd.period_end IS NULL
                AND attribute_name in ('SIC_HEAT_SOAK_TIME',
                      'SIC_COOL_DWELL_TIME',
                      'SIC_PRE_CLAMP_SOAK_TIME',
                      'SIC_INNER_ZONE_TEMPERATURE_MIN',
                      'SIC_INNER_ZONE_TEMPERATURE',
                      'SIC_INNER_ZONE_TEMPERATURE_MAX',
                      'SIC_OUTER_ZONE_TEMPERATURE_MIN',
                      'SIC_OUTER_ZONE_TEMPERATURE',
                      'SIC_OUTER_ZONE_TEMPERATURE_MAX',
                      'SIC_PYROMETER_ENABLED',
                      'SIC_PYROMETER_MIN_START_TEMP',
                      'SIC_PYROMETER_MAX_START_TEMP',
                      'SIC_INNER_ZONE_TEMPERATURE_INTLK',
                      'SIC_OUTER_ZONE_TEMPERATURE_INTLK',
                      'SIC_WAFER_PREHEAT_ENABLED'));
     END IF;
    loop  
    pipe row(RECIPE_VIEW_REC_TYPE(i.attribute_name, i.attribute_value));
    end loop;
END GET_SIC_ATTRIBUTES_VALUE;
plsql oracle
1 Answers

Usually, it helps if you post the whole error stack because saying that "it is giving error" (which error?) "while placing error" (huh?).

Some things are obvious, while others are not. For example:

  • you didn't declare v_sic_... variables nor c_hot (is that a constant? Declared globally somewhere in the packages?)
    • if they aren't declared elsewhere, you'll have to do it within the function
  • as you use IF-THEN-ELSE, you'll need two different loops, one for each of select statements you wrote

Simplified, function might/should look like this:

function get_sic... is
  -- declare local variables
  v_sic_wafer_type ...
  v_sic...
begin
  v_sic_wafer_Type := get_recipe...
  
  -- where is C_HOT declared?
  if v_sic_temp_mode = c_hot and ...
  then
    -- first SELECT
    for i in (select ... from ... where ...) loop
      pipe row(RECIPE_VIEW_REC_TYPE(i.attribute_name, i.attribute_value));
    end loop;

  else
    -- second SELECT
    for i in (select ... from ... where ...) loop
      pipe row(RECIPE_VIEW_REC_TYPE(i.attribute_name, i.attribute_value));
    end loop;
  end if;
end;
Littlefoot0
Related