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.
LVL 2
brgdotnetcontractorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
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

0
brgdotnetcontractorAuthor Commented:
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.
0
slightwv (䄆 Netminder) Commented:
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

0
brgdotnetcontractorAuthor Commented:
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?
0
slightwv (䄆 Netminder) Commented:
>>select null GLD from dual

My code was a simple test case because I don't have your tables and data.

I selected a null value from the DUAL table.  It does exactly what I told it to do.  I originally read the question that the value returned was null.  You then specified you were generating a no_data_found exception.  I just tweaked my previous example.

You need to take my test case and use your real tables.

Here is my guess based on everything you posted with some unnecessary code removed:

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

   begin
      select GLD into tmpGLD from DETAIL_INFO where rownum=1;
      exception when no_data_found then null;
   end;

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

Open in new window


I have issues with this part of the query:  where "rownum=1".  If the table has more than one row, you are only getting a random row.  That doesn't make sense to me but it is your data.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.