How to select into a variable in PL/SQL when the result might be null?

Question

Is there a way in to just run a query once to select into a variable, considering that the query might return nothing, then in that case the variable should be null.

Currently, I can't do a select into a variable directly, since if the query returns nothing, the PL/SQL would complain variable not getting set. I can only run the query twice, with the first one do the count and if the count is zero, set the variable to null, and if the count is 1, select into the variable.

So the code would be like:

v_column my_table.column%TYPE; v_counter number; select count(column) into v_counter from my_table where ...; if (v_counter = 0) then     v_column := null; elsif (v_counter = 1) then     select column into v_column from my_table where ...; end if; 

thanks.

Update: The reason I didn't use exception is I still have some following logic after assigning the v_column, and I have to use goto in the exception section to jump back to the following code. I'm kind of hesitate of goto lines.

Answer 1

You can simply handle the NO_DATA_FOUND exception by setting your variable to NULL. This way, only one query is required.

    v_column my_table.column%TYPE;  BEGIN      BEGIN       select column into v_column from my_table where ...;     EXCEPTION       WHEN NO_DATA_FOUND THEN         v_column := NULL;     END;      ... use v_column here END; 

Answer 2

I know it's an old thread, but I still think it's worth to answer it.

select (         SELECT COLUMN FROM MY_TABLE WHERE ....         ) into v_column from dual; 

Example of use:

declare v_column VARCHAR2(100); begin   select (SELECT TABLE_NAME FROM ALL_TABLES WHERE TABLE_NAME = 'DOES NOT EXIST')   into v_column    from dual;   DBMS_OUTPUT.PUT_LINE('v_column=' || v_column); end;