Daniel Morgan said:
Please acknowledge that once your code is in packages and you are using
sequences ... the code absolutely can not be compatible with code
written for any other database product: No exceptions. It just will not
be compatible and no quantity of bubble gum, paper clips, or rubber
bands is going to make it compatible.
Acknowledged.
So while I will gladly acknowledge a communication gap I still can't get
past thinking what you guys are proposing is a logical impossibility. If
you disagree, and I suspect you do/will, then please provide an example
of how you could use packages and sequences in Oracle and meet your
stated objective.
Ok.
Requirement: A web-page that displays all of the employees in a given
department.
Solutions that are completely dependent on the database's feature set:
a. Build it with Marvel / HTML DB
b. Build it with mod_plsql and Oracle HTTP Server
Solutions that while still dependent on the database's feature set
(i.e. packages and stored procedures), it uses another technology for
its presentation logic:
a. Build it with mod_perl
b. Build it with JSP
c. Build it with ASP.NET
So, here's an example with ASP.NET (which I actually took and modified
into C# .NET from
http://osi.oracle.com/~tkyte/ResultSets/index.html).
It's untested pseudocode, so probably has minor bugs in the ASP page.
CREATE OR REPLACE
PACKAGE DEPARTMENT AS
TYPE CURSOR_TYPE IS REF CURSOR;
PROCEDURE GET_EMPS (I_DEPTNO IN NUMBER,
O_RESULT_SET OUT CURSOR_TYPE);
END;
/
CREATE OR REPLACE
PACKAGE BODY DEPARTMENT AS
PROCEDURE GET_EMPS (I_DEPTNO IN NUMBER,
O_RESULT_SET OUT CURSOR_TYPE)
AS
BEGIN
OPEN O_RESULT_SET FOR
SELECT EMPNO, ENAME
FROM EMP
WHERE DEPTNO = I_DEPTNO;
END;
END;
employees.aspx:
<%@ Page language="c#" %>
<%@ Import Namespace="System.Data" %>
<html>
<body>
<h2>Query Employees in Department Code</h2>
<form id="Form1" method="post" runat="server">
<asp:textbox id="DeptCode" runat="server"/>
<asp:button id="Submit" runat="server" Text="Run Query"/>
<asp
ataGrid id="MyDataGrid" runat="server" width="800" />
</form>
<script language="c#" runat="server">
void Page_Load(object src, EventArgs e) {
Submit += new System.EventHandler( RunQuery );
}
void RunQuery(object src, EventArgs e) {
OracleConnection conn = new OracleConnection();
OracleCommand cmd = new OracleCommand();
conn.ConnectionString = "data
source="oracle.server;uid=scott;password=tiger";
cmd.Connection = conn;
cmd.CommandText = "SCOTT.DEPARTMENT.GET_EMPS";
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter i_deptno =
cmd.Parameters.Add("I_DEPTNO",
OracleClient.OracleType.Number);
i_deptno.Direction = ParameterDirection.Input;
OracleParameter o_resultset =
cmd.Parameters.Add("O_RESULT_SET",
OracleClient.OracleType.Cursor);
o_resultset.Direction = ParameterDirection.Output;
conn.Open();
i_deptno.Value = Int32.Parse ( DeptCode.Text );
OracleDataReader results = cmd.ExecuteReader();
MyDataSet.DataSource = results;
MyDataSet.DataBind();
}
</script>
</body>
</html>
The whole point of this is that we *didn't* use the Oracle-built in
features of HTML DB or mod_plsql, even though many successful people
CAN do so. It's really a matter of tradeoff of available skills,
future maintenance, and performance. In this case, I don't really
think the ASP.NET approach is any less scalable than Oracle-only
approaches.
The problem that too many Java or .NET folks cause is that they
re-invent database features all the time in the misguided attempt to
be "generic" because they don't want to be tied to their database
economically. This is not appropriate and is usually too drastic a
trade-off.
Anyhow, I can't speak for Joe, but this is my understanding of the
viewpoint.
Cheers
Stu
