Tom,

I'm planning to develop a web based sqlplus tool using mod_plsql(ORACLE 8.1.7.2 db).

we have somany database instances for development/systemtest and I dont want to create my DB packages in all the boxes to genarate HTML output.

I just want to use one dev instance for all the packages and use some Java stored procedures to connect to different instances(passing connect string , username,password). For testing purpose, I just hard coded these values.

something like 

create or replace and compile java source named "test"     
as                                                         
import java.sql.*;                                         
import java.io.*;                                          
import java.math.*;                                        
import oracle.sql.*;                                       
import oracle.jdbc.driver.*;                               
                                                           
public class test1                                         
{                                                          
 public static void get_connection() throws Exception           
 {                                                         

      DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); 
      Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@dev_host:1521:dev_sid","dev_user","dev_passwd");
               CallableStatement cstmt = conn.prepareCall(                              
                          "declare  nm  owa.vc_arr;"+     //got these things from your site          
                                "vl  owa.vc_arr;"+                       
                                " begin nm(1) := 'WEB';"+                
                                "vl(1) := 'WHATEVER';" +                 
                                "owa.init_cgi_env( nm.count, nm, vl );" +
                                "htp.htmlopen;"+                         
                                "htp.bodyopen;"+                         
                                "htp.print('Print Something');" +        
                                "htp.print('It is printing');"+          
                                "htp.bodyclose;"+                        
                                "htp.htmlclose; "+                       
                                "end;");                                 
        cstmt.execute();                                                   

  }
}

 
I have a procedure in the package like this:

procedure test       
is language java     
name 'test.get_connection()';  

when I tried to execute this pckage in a browser 

with 

It is giving "The document contain no data..." browser error!!

I tried to execute the procedure in SQLPLUS , it is not generating any output!!.

SQL> set serveroutput on
SQL> exec raj_util.test; 
                                            
PL/SQL procedure successfully completed.    
                                            

SQL> exec owa_util.showpage; 
                                                
PL/SQL procedure successfully completed.        

How to execute htp or owa_* packages in java to get HTML outputs?
I like PL/SQL . I'm not good at java servlets/JSP stuff and I don't want to use them.


 

1)The page came up with out images(this is fine, still I can type username & password)


2) After typing username & password, clicked on Login button,
it redirects me to 

> Do you think I should I hack it (owa_util) ?

SQL> select distinct owner from all_source where name = 'OWA_UTIL';

OWNER
------------------------------
SYS

1 row selected.

Anyone want to place bets on Tom's answer?

I am, of course, not speaking for Tom, but I am gonna step out on a limb and say he might be against "hacking" stuff in the sys schema.  Possibly.

- Mark 

Hi Tom,
  
  I'm experimenting with htp procedures.

  I wrote a simple anonymous block which calls htp.print. 

begin
  htp.print('kkk');
end;
/

While trying to run the same  block in an sqlplus session, I get the following error

SQL> begin
  2    htp.print('kkk');
  3  end;
  4  /
begin
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.OWA_UTIL", line 325
ORA-06512: at "SYS.HTP", line 860
ORA-06512: at "SYS.HTP", line 975
ORA-06512: at line 2

But if I try to rerun the same block once again in the same session, I'm not getting the error. It runs without generating any error.

Why So?