asked on

Question about query for Oracle EXECUTE Into satement

I have the Oracle query below, but when it is executed I get an error message shown at the very bottom of this post.
I believe that the error occurs when no data for GLD is returned from the select, and then that empty/null value is attempted to be stored in TmpGLD. Is there a way to assign TmpGLD an empty string value '' if the select statement does not return a value? See line 4 below.

1 - TmpGLD is 40 characters long
2 - GLD is 36 characters long

3 sqlString := 'select GLD from (select GLD from DETAIL_INFO) where ROWNUM <= 1;

4 EXECUTE IMMEDIATE sqlString INTO TmpGLD; -- Error will occur here on this line

Error message : No data found. Cause: No data was found from the objects. There was no data from the objects which may be due to end of fetch.

slightwv (䄆 Netminder)

Returning a null shouldn't generate an error on the execute immediate statement.

You have a missing single quote in line 3:
sqlString := 'select GLD from (select GLD from DETAIL_INFO) where ROWNUM <= 1';

You also shouldn't need dynamic SQL based on what you've posted.

This runs for me:

declare
  tmpGLD varchar2(10);
  sqlString varchar2(500);
begin
   sqlString := 'select GLD from (select null GLD from dual) where ROWNUM <= 1';

   EXECUTE IMMEDIATE sqlString INTO TmpGLD; -- Error will occur here on this line
end;
/

Open in new window

brgdotnet

ASKER

Hi slightvw. The missing single quote was a typo in the post but not in my actual code.

I pulled the query out of the execute immediate, but am getting the error shown at the bottom. Perhaps an expert can tell me how to store the value even if it is null or empty?

Rewrite:

select GLD INTO TmpGLD from (select null GLD from dual) where ROWNUM <= 1';

Error:
No data found.
No data was found from the objects.
Action: There was no data from the objects which may be due to end of fetch.

slightwv (䄆 Netminder)

A no data found error is likely coming from somewhere else. I cannot reproduce it with what you've provided.

This runs for me:

declare
  tmpGLD varchar2(10);
  sqlString varchar2(500);
begin
   select GLD into tmpGLD from (select null GLD from dual) where ROWNUM <= 1;

   dbms_output.put_line('Here');
end;
/

Open in new window

If it is coming from somewhere else, you probably just need a nested pl/sql block with its own exception handler.

If I change the test case to actually generate a no data found then the nested block goes like this. The code below makes tmpGLD null but you can set it to whatever you want in the exception handler.

declare
  tmpGLD varchar2(10);
  sqlString varchar2(500);
begin

   begin
      select GLD into tmpGLD from (select null GLD from dual) where 1=2;
      exception when no_data_found then null;
   end;

   dbms_output.put_line('Here');
end;
/

Open in new window

brgdotnet

ASKER

Sorry Sir. I can't get your code to work. This query always returns null --> select null GLD from dual
wether the column has data in it or not?

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial