CODEWITHSUNDEEP
sqloracle-databaseplsqlplsqldeveloper
roblogic
roblogic

Reputation: 1316

Selecting the dbms_output buffer from a PL/SQL query?

I have a stored procedure mydb.iscustomereligible(someGUID) that returns either 'true' or 'false' in a simple result set.

If the customer is not eligible, the proc prints a reason to dbms_output, for example reason code: Invalid Age: 2.

How can I improve the below statement to include the dbms_output text?

select mydb.iscustomereligible('1F2629379C4FA046E050C90A0C5A3000') from dual;

This is what I have come up with so far (and dismally failed), I can't get dbms_output.get_line to work

declare
  v_Data dbms_output.chararr;
  v_NumLines number;
begin
  -- enable the buffer first
  dbms_output.enable(1000000);
  dbms_output.put_line('hi');
  select mydb.iscustomereligible('1F2629379C4FA046E050C90A0C5A3000') from dual;
  select dbms_output.get_line(???) from dual;
end;

Thanks :)

Upvotes: 1

Views: 1459

Answers (1)

Jeff Holt
Jeff Holt

Reputation: 3190

Here is an example of the function you will not modify. I assume it puts only one line.

create or replace function iscustomereligible return boolean is
begin
   dbms_output.enable(1000000);
   dbms_output.put_line('hi');
   return true;
end;
/

Here is a function that will concatenate the results of the function you will not modify with the dbms_output line apparently written by that function.

create or replace function debug_iscustomereligible return varchar2 is
   v_line varchar2(4000);
   v_status number;
   v_el     boolean;
begin
   v_el := iscustomereligible;
   dbms_output.get_line(v_line, v_status); -- ignoring v_status if null is ok
   return 'customer ' || case v_el when true then 'eligible' else 'ineligible' end || ': ' || v_line;
end;
/

And here is how you would query the results if you were forced to do so only by executing a query.

select debug_iscustomereligible() from dual;

Upvotes: 5

Related Questions