Html sql queries

Toad for Oracle
1

Hello,

I have a sql report that runs just fine in sqlplus from my client. But when I run it in Toad it does not produce the html format as expected:

title center '<b><big> Inactive User Report ' skip 2
column department_name format a20 heading "Department"
column last_name format a30 heading "Last Name"
column first_name format a20 heading "First Name"
column salary format 9999999 heading "Salary"

set markup html on spool on entmap off

spool l2k_user_rpt.html

<my query>

spool off

I've tried executing it 3 ways in toad ... as a script (F5), from script runner , and the execute via SQLPLus. None of these work properly. Either they produce no output or they don't produce html output. Is this a Toad limitation or is there a way to get this to work in Toad?

2

Welcome back to the Toad forums.

First, would be good to understand what

"does not produce the html as expected"

means. Did html file get created or not? if created, what exactly wasn't right about it? No title? incorrect formatting? etc.

That said, here's a couple things to note...

  • TITLE should be TTITLE?
  • Probably most important is that any non-pathed files get created in the Toad (install) directory. Running your code in the Script Runner probably creates the file in that exe's directory (haven't tested).

I ran your SQL*Plus code (edited with TTITLE, my own query, different spool path, etc.) in SQL*Plus mode within my Toad 17.1 Editor and it ran fine. Hope the above helps.

3

Thanks Gary for replying. By "does not produce html as expected" I meant that I get normal sqlplus output not html output. I fixed the TTITLE but that was a cut/paste issue. I am now trying to upgrade to 17.1 but need to get a license issue fixed. If that doesn't work I'll open a support ticket.

4

Sounds like a plan... keep the forum posted

5

Gary please confirm when you say it ran fine that you get html output. For example:

set heading on
set markup html on spool on entmap off
ttitle center ' Inactive User Report ' skip 2
column username format a30 heading "User Name"
SELECT a.username
FROM dba_users a
WHERE oracle_maintained != 'Y'
AND (last_login < (sysdate-365) OR (last_login is null and created < (sysdate - 90)))
and rownum < 3
;
Produces something like:

image
image741×694 10.3 KB

8

Update - problem solved: It ended up that Toad was trying to use an instant client I had installed on my machine instead of the full client (even though the full client was first in my path). After changing the path (View -> Toad Options -> Executables -> SQLPlus) to the the sqlplus.exe of the full client the Execute Via SQLPlus was able to generate the html output.

1 Like