Convert dbms_output to html and selectively suppress other output-CodePudding

I am using the code below which outputs as intended:

DECLARE
    v_ins_param VARCHAR2(10);
    CURSOR c_ins_param IS
      SELECT status
      FROM   v$instance;      
BEGIN
    OPEN c_ins_param;

    LOOP
        FETCH c_ins_param INTO v_ins_param;

        EXIT WHEN c_ins_param%NOTFOUND;

        --
        --  
        --  
        IF v_ins_param = 'OPEN' THEN
        
            DBMS_OUTPUT.PUT_LINE('
' || CHR(35)  || '' || CHR(35)  ||'' || CHR(35)  || '
Great! Your database is up.
' || CHR(35)  || '' || CHR(35)  ||'' || CHR(35)  || '            ');

    ELSIF v_ins_param = 'MOUNTED' THEN
            DBMS_OUTPUT.PUT_LINE('
' || CHR(35)  || '' || CHR(35)  ||'' || CHR(35)  || '
Database is only mounted.
' || CHR(35)  || '' || CHR(35)  ||'' || CHR(35)  || '
');

        ELSE
            DBMS_OUTPUT.PUT_LINE('
' || CHR(35)  || '' || CHR(35)  ||'' || CHR(35)  || '
Database is neither mounted or open.
' || CHR(35)  || '' || CHR(35)  ||'' || CHR(35)  || '
            ');
        END IF;
    END LOOP;

    CLOSE c_ins_param;
END;

/

This is the "kind" of output it'll give you:

###
Great! Your database is up.
###

Ultimately this will be a series of anonymous block "typed" scripts like this one which I want to output to a single file.

I am now trying to get the output to just give the text information only, in a html output. I saw this on another page and adapted it for my code to see how it would / could work:

spool c:mag.html
DECLARE
  CURSOR c1 IS
    SELECT * FROM dept;
BEGIN
  DBMS_OUTPUT.PUT_LINE('< pre >');
  DBMS_OUTPUT.PUT_LINE('< h1 >Report on Databases</h1>');
  FOR mag IN c1 
  LOOP
    EXIT WHEN c1%notfound;
      DBMS_OUTPUT.PUT_LINE('< b >Department Name[/b] =' || mag.dname);
  END LOOP;
END;
/

However the way of doing this, using:

mag.dname

is not applicable in my 'non for loop' example.

I feel like there must be a more appropriate way of achieving the output. I have tried:

set markup html on

And it does not just returned impact the output but ALL of the code.

Has anyone tried this before and know perhaps how I might get the html output for the returned data only?

CodePudding user response：

Here's an example - I store the following in a script called (say) scr.sql

set termout off
set feedback off
set serverout on
begin
  for i in ( 
    select 
      case 
        when status = 'OPEN' then '<p>Database is open</p>'
        when status = 'MOUNTED' then '<p>Database is mounted</p>'
        else '<p>Database is not happy</p>'
      end status_output
    from v$instance
  )
  loop
    dbms_output.put_line(i.status_output);
  end loop;
end;
.

spool /tmp/status.html
/
spool off

and then run this from SQLPlus as

SQL> @scr.sql

and the only thing my resultant status.html is

<p>Database is open</p>