Selecting the dbms_output buffer from a PL/SQL query?

Question

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 :)

PS: I don't have access nor intention to change the source of the iscustomereligible stored proc I just want to query the results... — roblogic
– roblogic, Commented Aug 2, 2017 at 2:03
you typically need set serveroutput on to see the output of dbms_output - I assume it's the same for PL/SQL Developer — user330315
– user330315, Commented Aug 2, 2017 at 6:00

Jeff Holt · Accepted Answer · 2017-08-02 13:32:27Z

5

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;

edited Aug 2, 2017 at 13:32

answered Aug 2, 2017 at 4:06

Jeff Holt

3,2623 gold badges27 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Selecting the dbms_output buffer from a PL/SQL query?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related