SELECT INTO and multiple variables for UPDATE

Question

I'm a neophyte with Oracle and PL/SQL; I'm having some issues with a select into several variables. What I'm trying to do is search for multiple items in one table (SA_SPECIFICATION_DETAILS) and concatenate them together into a field in the other table (SA_ASSET). The SA_SPECIFICATION_DETAILS table holds several rows of attributes per each SA_ASSET.ASSET_ID. I've been working on this a few days now and keep getting several errors. Here is my sample code:

DECLARE 
manuf VARCHAR(50);
mods VARCHAR(50);
mvs VARCHAR(50);
yeara VARCHAR(4);
assid VARCHAR(50):= &assid;

BEGIN
SELECT TRIM(ATTRIBUTE_VALUE) INTO manuf      
    FROM
      SA_SPECIFICATION_DETAILS d,
      SA_ASSET a
    WHERE
      d.SPECIFICATION_NO = a.SPECIFICATION_NO
    AND d.ATTRIBUTE_DESC = 'MANUFACTURER'
    AND ASSET_ID = assid;

SELECT TRIM(ATTRIBUTE_VALUE) INTO mods
    FROM
      SA_SPECIFICATION_DETAILS d,
      SA_ASSET a
    WHERE
      d.SPECIFICATION_NO = a.SPECIFICATION_NO
    AND d.ATTRIBUTE_DESC = 'MODEL'
    AND ASSET_ID = assid;

SELECT TRIM(ATTRIBUTE_VALUE) INTO mvs
    FROM
      SA_SPECIFICATION_DETAILS d,
      SA_ASSET a
   WHERE
      d.SPECIFICATION_NO = a.SPECIFICATION_NO
    AND d.ATTRIBUTE_DESC = 'MAIN VALVE SIZE'
    AND ASSET_ID = assid;

SELECT TRIM(ATTRIBUTE_VALUE) INTO yeara
    FROM
      SA_SPECIFICATION_DETAILS d,
      SA_ASSET a
    WHERE
      d.SPECIFICATION_NO = a.SPECIFICATION_NO
    AND d.ATTRIBUTE_DESC = 'YEAR MANUFACTURED'
    AND ASSET_ID = assid;

dbms_output.ENABLE(buffer_size => NULL);
DBMS_OUTPUT.PUT_LINE ('Variables:');
dbms_output.put_line (manuf);
dbms_output.put_line (mods);
dbms_output.put_line (mvs);
dbms_output.put_line (yeara);
dbms_output.put_line (assid);
END;

BEGIN
UPDATE
  SA_ASSET
SET
  ASSET_DESC = TRIM(ATTRIBUTE1)
  || ' - Service Type: PW, Manf: '
  || manuf
  || ', Model: '
  || mods
  || ', Main Valve Size: '
  || mvs
  || ', Year Manf: '
  || yearm
  || ', Location: '
  || TRIM(SA_ASSET.STREET_NUMBER_CHAR)
  || ' '
  || TRIM(SA_ASSET.STREET_NAME)
WHERE
  ASSET_TYPE     = 'HYDRANT'
AND ASSET_STATUS = 'ACTIVE'
AND UPPER(ASSET_DESC) NOT LIKE '%LOCATION:%'
AND UPPER(ASSET_DESC) NOT LIKE '%HYDRANT%'
AND SA_ASSET.ASSET_ID = assid
END;

I've tested this and it fails with: Error report: ORA-06550: line 73, column 1: PLS-00103: Encountered the symbol "BEGIN" ORA-06550: line 96, column 1: PLS-00103: Encountered the symbol "END" when expecting one of the following:

So I commented out the update portion and tried this:

    declare 
    manuf sa_specification_details.attribute_value%type;
    mods sa_specification_details.attribute_value%type;
    mvs sa_specification_details.attribute_value%type;
    yeara sa_specification_details.attribute_value%type;
    assid varchar2(15):= &assid;

    --dbms_output.ENABLE(buffer_size => NULL);

    BEGIN
    SELECT ATTRIBUTE_VALUE INTO manuf      
        FROM
          SA_SPECIFICATION_DETAILS d,
          SA_ASSET a
        WHERE
          d.SPECIFICATION_NO = a.SPECIFICATION_NO
        AND d.ATTRIBUTE_DESC = 'MANUFACTURER'
        AND ASSET_ID = assid;
    dbms_output.put_line (manuf);

   SELECT ATTRIBUTE_VALUE INTO mods
        FROM
          SA_SPECIFICATION_DETAILS d,
          SA_ASSET a
        WHERE
          d.SPECIFICATION_NO = a.SPECIFICATION_NO
        AND d.ATTRIBUTE_DESC = 'MODEL'
        AND ASSET_ID = assid;
    dbms_output.put_line (mods);

    SELECT ATTRIBUTE_VALUE INTO mvs
        FROM
          SA_SPECIFICATION_DETAILS d,
          SA_ASSET a
       WHERE
          d.SPECIFICATION_NO = a.SPECIFICATION_NO
        AND d.ATTRIBUTE_DESC = 'MAIN VALVE SIZE'
        AND ASSET_ID = assid;
    dbms_output.put_line (mvs);

    SELECT ATTRIBUTE_VALUE INTO yeara
        FROM
          SA_SPECIFICATION_DETAILS d,
          SA_ASSET a
        WHERE
          d.SPECIFICATION_NO = a.SPECIFICATION_NO
        AND d.ATTRIBUTE_DESC = 'YEAR MANUFACTURED'
        AND ASSET_ID = assid;
    dbms_output.put_line (yeara);  

    SYS.dbms_output.ENABLE;
    DBMS_OUTPUT.PUT_LINE ('Variables:');
    dbms_output.put_line (manuf);
    dbms_output.put_line (mods);
    dbms_output.put_line (mvs);
    dbms_output.put_line (yeara);
    dbms_output.put_line (assid); 
    END;

Which gets me the error: Error report: ORA-01403: no data found ORA-06512: at line 11 01403. 00000 - "no data found" *Cause:
*Action:

So I ran the query without the select into:

SELECT
  ATTRIBUTE_VALUE
FROM
  SA_SPECIFICATION_DETAILS d,
  SA_ASSET a
WHERE
  d.SPECIFICATION_NO = a.SPECIFICATION_NO
AND d.attribute_desc = 'MANUFACTURER'
AND asset_id = '001722';

And it returns a single row/single column (aka it works). So what am I doing wrong? I've declared the variables, I've got the selects to run correctly but it's not passing the variables (that I can tell - haven't got dbms_output.put_line (manuf); to work). Am I close or on the wrong track completely?

Solution

in your code, you need to add single quotes here:

assid VARCHAR2(50):= '&assid';

also remove the end/begin here:

dbms_output.put_line (assid);
END;

BEGIN

as you want this as one block (as you're using the variables from the first selects in the update).

finally i think you mean this to be yeara and not yearm

  || ', Year Manf: '
  || yearm <-- yeara?

an optimisation you can do too, is replace the 4 selects with one:

select max(case d.attribute_desc
             when 'MANUFACTURER' then trim(attribute_value)
           end) manuf,
       max(case d.attribute_desc
             when 'MODEL' then trim(attribute_value)
           end) mods,
       max(case d.attribute_desc
             when 'MAIN VALVE SIZE' then trim(attribute_value)
           end) mvs,
       max(case d.attribute_desc
             when 'YEAR MANUFACTURED' then trim(attribute_value)
           end) yeara
  into manuf, mods, mvs, yeara
  from sa_specification_details d,
       sa_asset a
 where d.specification_no = a.specification_no
   and d.attribute_desc in ( 'MANUFACTURER', 'MODEL', 'MAIN VALVE SIZE',
                             'YEAR MANUFACTURED' )
   and asset_id = assid; 

Comments

[Gaurav Soni]

:May be one of your query is not returning data ,thats why its giving you No_DATA_FOUND exception.Please do exception handling first .And second check all your query passing your asset_id

[Gaurav Soni]

check for MODEL,MAIN VALVE SIZE,YEAR MANUFACTURED too

[DazzaL]

@robartle p.s. don't use VARCHAR, use VARCHAR2.

[robartle]

Awesome! Works perfect! Yeah, the 'yearm' was a typo but I think the quotes around &assid was the missing piece of the puzzle. The BEGIN, END might have been an artifact of my testing - I've done it so many times I've forgotten. Thank you for the help!

[robartle]

Yes I changed that in one of my iterations - forgot to change it back. Thanks