|
|
 | | From: | Patricia Klimek | | Subject: | Format the SQL-Output with HTML | | Date: | 10 Jan 2005 01:20:22 -0800 |
|
|
 | Hey,
I'm working on a project, programming with JSP making a connection to
an oracle server and running a script which output I want to have in
HTML.
The Output is Line by Line, each Line looks like this:
MAVINFO_NOSORBAdmin11036unknownJDBCThinClientINACTIVE
that means there are no blanks between the data. The original script
is following:
set echo off
rem
rem Script: whoson.sql
rem
rem Purpose: Shows who is currently logged into Banner, either through
rem Banner GUI (shows F45RUN.EXE or Windows 95 OraPgm) or sqlplus
rem (shows sqlplus@Edison) or as a process (such as for jobsub),
rem for the current database.
rem
rem Author: Stephen Rea
rem Released: 12/2/97
rem 1/20/00: Added unix process number (PID).
rem 2/28/00: Order by username. Set pagesize 60.
rem 9/11/02: Show module name (which may be Banner form name) if
program
rem name is null.
rem 7/15/03: Increased ouser column length by 1.
rem
set linesize 80 pagesize 60
clear columns
column username format a11
column osuser format a14
column program format a25 trunc
column spid format a6
column terminal format a11
select vs.username,vs.osuser,vp.spid,vs.terminal,
nvl(vs.program,vs.module) program,vs.status
from v$session vs,v$process vp
where vs.username is not null
and vp.addr = vs.paddr
order by vs.username;
clear columns
set pagesize 24
set echo on
NOW, I only can do the SELECT without the SET, COLUMN. That is why I
get a Line without any blanks within. Can I format my Output with HTML
somehow?
What does format a11 mean?: "column username format a11" ?
The Connection to the Oracle DB and the SELECT-Instruction are made
with JSP.
Following the JSP executing the SELECT.
<%@ page import="java.sql.*" %>
<%@ page import="java.io.*" %>
<%@ include file="./connect.jsp" %>
<%
String holen = request.getParameter("scriptauswahlb");
%>
Test
<%= holen %>
<%! public ResultSet rsErstellen(String query) {
try{
stmt = dbconn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery(query);
}catch(Exception b) {b.printStackTrace(); }
return rs;
}
%>
<% String sql1 = "";
String sql = "";
%>
<%
try{
File path = new File("I:\\Blazix\\webfiles\\OracleScript\\" +
holen + ".sql");
BufferedReader in = new BufferedReader(new FileReader(path));
sql = in.readLine();
try{
while(sql != null) {
sql1 = sql1 + sql;
sql = in.readLine();
}
}catch(IOException u) {u.printStackTrace(); }
finally{ in.close(); }
}catch(Exception a){a.printStackTrace(); }
%>
<%= sql1 %>
<% String query = "";
%>
<% query = sql1;
try{
rs = rsErstellen(query);
}catch(Exception z) {z.printStackTrace();}
%>
<%
try{
boolean more = rs.first();
while(more) {
%>
| Ausgabe:<%= rs.getString(1)%> |
<% more = rs.next();
}
rs.close();
stmt.close();
dbconn.close();
}catch(Exception o) {o.printStackTrace(); }
%>
I would really appreciate if someone would have an idea how to resolve
my problem.
Patricia
|
|
| | From: | david.rydzewski at gmail.com | | Subject: | Re: Format the SQL-Output with HTML | | Date: | 10 Jan 2005 19:16:32 -0800 |
|
|
|
Patricia Klimek wrote:
> Hey,
> I'm working on a project, programming with JSP making a connection to
> an oracle server and running a script which output I want to have in
> HTML.
> The Output is Line by Line, each Line looks like this:
>
> MAVINFO_NOSORBAdmin11036unknownJDBCThinClientINACTIVE
>
> that means there are no blanks between the data. The original script
> is following:
>
> set echo off
> rem
> rem Script: whoson.sql
> rem
> rem Purpose: Shows who is currently logged into Banner, either
through
> rem Banner GUI (shows F45RUN.EXE or Windows 95 OraPgm) or sqlplus
> rem (shows sqlplus@Edison) or as a process (such as for jobsub),
> rem for the current database.
> rem
> rem Author: Stephen Rea
> rem Released: 12/2/97
> rem 1/20/00: Added unix process number (PID).
> rem 2/28/00: Order by username. Set pagesize 60.
> rem 9/11/02: Show module name (which may be Banner form name) if
> program
> rem name is null.
> rem 7/15/03: Increased ouser column length by 1.
> rem
> set linesize 80 pagesize 60
> clear columns
> column username format a11
> column osuser format a14
> column program format a25 trunc
> column spid format a6
> column terminal format a11
> select vs.username,vs.osuser,vp.spid,vs.terminal,
> nvl(vs.program,vs.module) program,vs.status
> from v$session vs,v$process vp
> where vs.username is not null
> and vp.addr = vs.paddr
> order by vs.username;
> clear columns
> set pagesize 24
> set echo on
>
> NOW, I only can do the SELECT without the SET, COLUMN. That is why I
> get a Line without any blanks within. Can I format my Output with
HTML
> somehow?
> What does format a11 mean?: "column username format a11" ?
>
> The Connection to the Oracle DB and the SELECT-Instruction are made
> with JSP.
> Following the JSP executing the SELECT.
>
>
>
>
> <%@ page import="java.sql.*" %>
> <%@ page import="java.io.*" %>
> <%@ include file="./connect.jsp" %>
>
> <%
> String holen = request.getParameter("scriptauswahlb");
> %>
> Test
> <%= holen %>
>
> <%! public ResultSet rsErstellen(String query) {
> try{
> stmt = dbconn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
> ResultSet.CONCUR_UPDATABLE);
> rs = stmt.executeQuery(query);
> }catch(Exception b) {b.printStackTrace(); }
> return rs;
> }
> %>
>
> <% String sql1 = "";
> String sql = "";
> %>
>
> <%
> try{
> File path = new File("I:\\Blazix\\webfiles\\OracleScript\\" +
> holen + ".sql");
> BufferedReader in = new BufferedReader(new FileReader(path));
> sql = in.readLine();
> try{
> while(sql != null) {
> sql1 = sql1 + sql;
> sql = in.readLine();
> }
> }catch(IOException u) {u.printStackTrace(); }
> finally{ in.close(); }
> }catch(Exception a){a.printStackTrace(); }
> %>
>
> <%= sql1 %>
>
> <% String query = "";
> %>
>
> <% query = sql1;
> try{
> rs = rsErstellen(query);
> }catch(Exception z) {z.printStackTrace();}
>
> %>
>
> <%
> try{
> boolean more = rs.first();
> while(more) {
> %>
>
> | Ausgabe:<%= rs.getString(1)%> |
>
> <% more = rs.next();
> }
> rs.close();
> stmt.close();
> dbconn.close();
> }catch(Exception o) {o.printStackTrace(); }
> %>
>
>
>
>
>
>
>
>
>
> I would really appreciate if someone would have an idea how to
resolve
> my problem.
>
> Patricia
|
|
|
