• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 46
  • Last Modified:

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.
0
brgdotnet
Asked:
brgdotnet
  • 3
  • 2
1 Solution
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now