how do i fetch values of a column in a clob variable?

Please try this piece of code:

DECLARE
    newID DD_DONOR.IDDONOR%TYPE;
    oldID DD_DONOR.IDDONOR%TYPE;
    fname DD_DONOR.FIRSTNAME%TYPE;
    lname DD_DONOR.LASTNAME%TYPE;    
BEGIN
    newID := 305;
    fname := 'Thomas';
    lname := 'Sheer';    
    select
        iddonor
    into
        oldId
    from
        dd_donor
    WHERE iddonor = newId;
    dbms_output.put_line('This ID is already assigned');
exception
    when NO_DATA_FOUND then
        UPDATE DD_DONOR
        SET IDDONOR = newID
        WHERE FIRSTNAME = fname AND LASTNAME = lname;
        DBMS_OUTPUT.PUT_LINE(fname || ' ' || lname || ' ' || 'New ID: ' || newID); 
END;
/

If you want to stick to your approach with cursor here is the changes to your initial script:

Changes are in :

all_ids DD_DONOR.IDDONOR%TYPE – no need of CLOB as you expect only one record with this ID.

Declaration and Call of cursor – parameter added so you are able to search for a specific id

DECLARE
    all_ids DD_DONOR.IDDONOR%TYPE;
    newID DD_DONOR.IDDONOR%TYPE;
    fname DD_DONOR.FIRSTNAME%TYPE;
    lname DD_DONOR.LASTNAME%TYPE;    
    CURSOR id(newID in DD_DONOR.IDDONOR%TYPE) IS
    SELECT IDDONOR FROM DD_DONOR where iddonor = newID;   
BEGIN
    newID := 305;
    fname := 'Thomas';
    lname := 'Sheer';
    
    OPEN id(newId);
    FETCH id  INTO all_ids;

    IF id%NOTFOUND THEN
        UPDATE DD_DONOR
        SET IDDONOR = newID
        WHERE FIRSTNAME = fname AND LASTNAME = lname;
            DBMS_OUTPUT.PUT_LINE(fname || ' ' || lname || ' ' || 'New ID: ' || newID);
    ELSIF id%FOUND THEN
            DBMS_OUTPUT.PUT_LINE('This ID is already assigned.');
    END IF;

    CLOSE id;
END;
/

Thanks

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top