I think your answer is very useful, however, I like to extend the question a lillte bit. I just wonder if it is possible to call Oracle Store Procedured or even connect to Oracle database if I use ASP on Windows and call to Oracle on UNIX. I am familiar with java, it can be done easily, but I do not know about ASP.

Thanks,

Hi,

I talk to our ASP developers about how bind variables are important. Do you happen to have some examples of using bind variables in ASP?

Here is an example using Oracle's OO4O.
This uses one of our databases so the sql will need to be changed to work elsewhere.
It also contains a lot of debug output as a demo.


< %
Response.Buffer = true
Response.Expires = -255
Response.Expiresabsolute = Now() - 2
Response.AddHeader "cache-control","no-store"
Response.CacheControl = "no-cache"


'what are we looking for?
dim v_acctid
v_acctid = trim(request("acctid")) 'posted into us
dim src_str
src_str = "select a.acct_no acct_no, a.tax_type tax_type, a.tax_year tax_year, a.name1 name1 "
src_str = src_str + " from tax.accounts a "
src_str = src_str + "where a.acct_id = :acctidparm" 'NB no semicolon


'Do our Database thing
dim varOraSession
dim varOraDatabase
dim varOraDynaset

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' In Global.asa under Sub Application_OnStart put:
' Application("tax_OO4O_DB_Name") = "DB alias"
' Application("tax_OO4O_User/PW") = "username/password"
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

'From Global.asa:
response.write("<br>" + "New Connection:" + "<br>")
response.write(Application("tax_OO4O_DB_Name") + "<br>")
response.write(Application("tax_OO4O_User/PW") + "<br>")

'Create Database Objects
Set varOraSession = Server.CreateObject("OracleInProcServer.XOraSession")
Set varOraDatabase = varOraSession.OpenDatabase(Application("tax_OO4O_DB_Name"), Application("tax_OO4O_User/PW"), 0)

response.write("<br>" + "From Oracle Connection:" + "<br>")
response.write("OO4O Version: " + varOraSession.OIPVersionNumber + "<br>")
response.write("DatabaseName: " + varOraDatabase.DatabaseName + "<br>")
response.write("Oracle Version: " + varOraDatabase.RDBMSVersion + "<br>")

response.write("<br>" + "Searching For: " + "<br>")
response.write(src_str + "<br>")

'bind acctidparm to v_acctid and create result set
varOraDatabase.Parameters.Add "acctidparm", 0, 1
varOraDatabase.Parameters("acctidparm").Value = v_acctid
Set varOraDynaset = varOraDatabase.CreateDynaset(src_str, &H4) '&H4 = Read Only

'get the data
varOraDynaset.Refresh

'Show it
response.write("<br>Result:<br>")
do while not varOraDynaset.EOF
response.write("Acct_no: " + varOraDynaset.Fields("acct_no") + ", ")
varOraDynaset.MoveNext
loop


Set varOraSession = nothing
Set varOraDatabase = nothing
Set varOraDynaset = nothing
v_acctid = ""
src_str = ""
% >

Not looking to start a flame war or offend anyone, etc., but why use ASP/COM/VB/etc in the first place? Something like mod_plsql provides an extremely elegant and easy interface. The following code builds on Chuck's OO4O sample from above... I used the all_objects view as follows to create the 'accounts' table:

create table accounts
(
acct_no number,
tax_type varchar2(18),
tax_year number,
name1 varchar2(30)
);

alter table accounts add constraint accounts_pk primary key (acct_no);

alter table accounts nologging;

insert /*+ append */ into accounts
select object_id, object_type, object_id, object_name from all_objects;

commit;

alter table accounts logging;

create or replace package demo as
procedure print_account_info(p_acct_no in accounts.acct_no%type);
end;

create or replace package body demo as
procedure print_account_info(p_acct_no in accounts.acct_no%type) is
begin
htp.htmlOpen;
htp.headOpen;
htp.title('Print Account Information Sample');
htp.headClose;
htp.bodyOpen;
htp.tableOpen('BORDER=3');
for c_acct_info in (select acct_no,
tax_type,
tax_year,
name1
from accounts
where acct_no = p_acct_no)
loop
htp.tableHeader('Account No');
htp.tableHeader('Tax Type');
htp.tableHeader('Tax Year');
htp.tableHeader('Name');
htp.tableRowOpen;
htp.tableData(to_char(c_acct_info.acct_no));
htp.tableData(c_acct_info.tax_type);
htp.tableData(to_char(c_acct_info.tax_year));
htp.tableData(c_acct_info.name1);
htp.tableRowClose;
end loop;
htp.tableClose;
htp.bodyClose;
htp.htmlClose;
end;
end;

Perhaps it is because I am not a developer by trade, but code like that seems easier to read at the very least, and, for the topic at hand, eliminates the 'how do I use binds' question. Plus there are *many* other benefits to using mod_plsql.

Again, not looking for a flame war, and perhaps this is not the best forum for such a discussion, but just curious about why so many people seem to use VB-type development tools/environments and "do it on the client"...

- Mark

We are a Windows + Front Page + ASP shop for web stuff.
Not much I can do about that.

I work on internal programming and DB design.
Creating web pages is not my main job.(Thank God!)
I don't even use OO4O in the production pages (all 4 of them) that I am responsible for. ;)
I just wrote that while playing around with OO4O.

How do you get the code from the package to display on a page?
Chuck

Hi Chuck,

> We are a Windows + Front Page + ASP shop for web stuff.

That is the essence of my question I guess. I just wonder how it is that companies make the decision to go that direction.

> How do you get the code from the package to display on a page?

When you install and configure mod_plsql you can configure things known as Data Access Descriptors - they are very easy and intuitive. Most of the configuaration is done for you during the install.

A sample URL for the example I posted would be:

</code> http://testserver/pls/webdemo/demo.list_account_info?p_acct_no=12345 <code>

The '/pls' is a virtual path defined on the Oracle webserver that maps to mod_plsql. It is automatically configured during the install. 'webdemo' is the DAD (Data Access Descriptor), 'demo' is the package, and 'list_account_info' is (surprise) the procedure within the package body.

I have not played with OO4O much at all, though I have tinkered with it a tiny bit. I am a "production" DBA, so most of my day is not spent coding...

Thanks,

Mark

hi,
regarding the code available to return REF cursors from procedure to VB ,i am facing a problem as the VB code always gives me Error that "parameter not defined".i am using VB 6 and Oracle 9i.i am using the code exactly as was given in the attached code.it was writen in the code that ODBS version should support REF cursors.how will i ensure this,waiting for your prompt reply.
thanks

Could you please give me an example for how to call an ASP page from a stored procedure using UTL_HTTP