CODEWITHSUNDEEP
oracle-databaseplsql
Alan Shore
Alan Shore

Reputation: 113

How to fetch values of a column into CLOB variable?

I am trying to pass a value of a single INTEGER column inside a CLOB variable so I can do the IN operation (the size of the output of a column is unknown), but I got an error and now I am stuck. First I was trying to use the SELECT statement after IN, but I found out it doesn't work like that in PL/SQL. So, in the nutshell, I want to be able to check the primary key in a table and if it doesn't exist update an existing record with a new value, but if it already exists I shouldn't be able to do that.

The DoGood Donor application contains a page that allows administrators to change the ID assigned to a donor in the DD_DONOR table. Create a PL/SQL block to handle this task. Include exception-handling code to address an error raised by attempting to enter a duplicate donor ID. If this error occurs, display the message “This ID is already assigned.” Test the code by changing donor ID 305.

Here is a table I work with. DD_DONOR

Name      Null?    Type         
--------- -------- ------------ 
IDDONOR   NOT NULL NUMBER(4)    
FIRSTNAME          VARCHAR2(15) 
LASTNAME           VARCHAR2(30) 
TYPECODE           CHAR(1)      
STREET             VARCHAR2(40) 
CITY               VARCHAR2(20) 
STATE              CHAR(2)      
ZIP                VARCHAR2(9)  
PHONE              VARCHAR2(10) 
FAX                VARCHAR2(10) 
EMAIL              VARCHAR2(25) 
NEWS               CHAR(1)      
DTENTERED          DATE

Here is my code:

DECLARE
    all_ids clob;
    newID DD_DONOR.IDDONOR%TYPE;
    fname DD_DONOR.FIRSTNAME%TYPE;
    lname DD_DONOR.LASTNAME%TYPE;    
    CURSOR id IS
    SELECT IDDONOR FROM DD_DONOR;   
BEGIN
    newID := 305;
    fname := 'Thomas';
    lname := 'Sheer';
    
    OPEN id;
    FETCH id BULK COLLECT 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;

And here is the error:

Error report -
ORA-06550: line 14, column 32:
PLS-00497: cannot mix between single row and multi-row (BULK) in INTO list
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.

This code that I created in the beginning compiles, but doesn't do anything to the table, so I've abandoned it:

DECLARE
    newID DD_DONOR.IDDONOR%TYPE;
    fname DD_DONOR.FIRSTNAME%TYPE;
    lname DD_DONOR.LASTNAME%TYPE;
    
BEGIN
    newID := 305;
    fname := 'Thomas';
    lname := 'Sheer';
    
    UPDATE DD_DONOR
    SET IDDONOR = newID
    WHERE NOT EXISTS (SELECT IDDONOR
                      FROM DD_DONOR
                      WHERE FIRSTNAME = fname AND LASTNAME = lname);
    EXCEPTION 
        WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('This ID is already assigned.');
END;

The output:

PL/SQL procedure successfully completed.

Another version that doesn't work:

DECLARE
    newID DD_DONOR.IDDONOR%TYPE;
    fname DD_DONOR.FIRSTNAME%TYPE;
    lname DD_DONOR.LASTNAME%TYPE;
    
BEGIN
    newID := 305;
    fname := 'Thomas';
    lname := 'Sheer';
    
    IF (SELECT IDDONOR FROM DD_DONOR
        WHERE FIRSTNAME = fname AND LASTNAME = lname) = newID
        THEN 
        DBMS_OUTPUT.PUT_LINE('This ID is already assigned.');
    ELSE
        UPDATE DD_DONOR
        SET IDDONOR = newID
        WHERE FIRSTNAME = fname AND LASTNAME = lname;
    END IF;
END;

This one kind works, but again, doesn't output an error message that the ID is taken:

SET SERVEROUTPUT ON SIZE 100000
DECLARE
    newID DD_DONOR.IDDONOR%TYPE;
    fname DD_DONOR.FIRSTNAME%TYPE;
    lname DD_DONOR.LASTNAME%TYPE;     
    CURSOR id IS    
    SELECT IDDONOR FROM DD_DONOR
    WHERE FIRSTNAME = fname AND LASTNAME = lname;       
BEGIN
    newID := 305;
    fname := 'Thomas';
    lname := 'Sheer';    
    OPEN id;
    IF SQL%NOTFOUND THEN
        UPDATE DD_DONOR
        SET IDDONOR = newID
        WHERE FIRSTNAME = fname AND LASTNAME = lname;
        DBMS_OUTPUT.PUT_LINE(fname || ' ' || lname || ' ' || 'New ID: ' || newID);
    ELSIF SQL%FOUND THEN
        DBMS_OUTPUT.PUT_LINE('This ID is already assigned.');
    END IF;
    CLOSE id;
END;

This one doesn't work, but I like it. It looks like it should work.

SET SERVEROUTPUT ON SIZE 100000
DECLARE
    newID DD_DONOR.IDDONOR%TYPE;
    fname DD_DONOR.FIRSTNAME%TYPE;
    lname DD_DONOR.LASTNAME%TYPE;    
    CURSOR id IS    
    SELECT IDDONOR FROM DD_DONOR
    WHERE FIRSTNAME = fname AND LASTNAME = lname;    
    all_IDs DD_DONOR%rowtype;    
BEGIN
    newID := 305;
    fname := 'Thomas';
    lname := 'Sheer';    
    OPEN id;    
    LOOP
        FETCH id into all_IDs;
        UPDATE DD_DONOR
        SET IDDONOR = newID
        WHERE FIRSTNAME = fname AND LASTNAME = lname;
        DBMS_OUTPUT.PUT_LINE(fname || ' ' || lname || ' ' || 'New ID: ' || newID);          
        EXIT WHEN id%FOUND;
        DBMS_OUTPUT.PUT_LINE('This ID is already assigned.');
    END LOOP; 
    CLOSE id;
END;

Error report -
ORA-06550: line 15, column 9:
PLS-00394: wrong number of values in the INTO list of a FETCH statement
ORA-06550: line 15, column 9:
PL/SQL: SQL Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.

Upvotes: 0

Views: 841

Answers (2)

gogocho
gogocho

Reputation: 218

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

Upvotes: 1

eaolson
eaolson

Reputation: 15094

Your id is a NUMBER. Your all_ids is a CLOB, which is basically a very large string field. The IN clause in a query works on an explicit list of discrete values; it's not a string search kind of thing. What you probably want is a collection.

IN works like:

WHERE id IN (1, 2, 3)

the list must be of fixed length. It can also work like:

WHERE id IN ( SELECT id FROM table2 )

I can't quite tell what you're trying to do. Your second query will update every row in the table to newID, which is almost certainly not what you want. How do you identify the row you are you trying to update if fname and lname don't exist in the table already?

Upvotes: 2

Related Questions