By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,197 Members | 972 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,197 IT Pros & Developers. It's quick & easy.

Running a query manually when using web modules

P: n/a
Hi. I'm implementing a web deployment using 9i. I have a table for STOCK,
which has one or more PURCHASES, which have one or more PURCHASE_ITEMS.

When a purchase item is INSERTED, I'd like the web module component to
update the relevant grandparent record of STOCK, and when UPDATED, to
remember the previous level and adjust accordingly.

I'm sure that this can probably be done in PL/SQL, but don't have a clue
how - all books and tutorials I have found take a 'PHP-style' approach or
use servlets and don't seem to use web modules.

I am mostly familiar with Design Editor. Can anyone help? Thanks.
Jul 19 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
"none" <no**@none.com> wrote in message news:<f4******************************@news.terane ws.com>...
Hi. I'm implementing a web deployment using 9i. I have a table for STOCK,
which has one or more PURCHASES, which have one or more PURCHASE_ITEMS.

When a purchase item is INSERTED, I'd like the web module component to
update the relevant grandparent record of STOCK, and when UPDATED, to
remember the previous level and adjust accordingly.

I'm sure that this can probably be done in PL/SQL, but don't have a clue
how - all books and tutorials I have found take a 'PHP-style' approach or
use servlets and don't seem to use web modules.

I am mostly familiar with Design Editor. Can anyone help? Thanks.


What do you mean by web modules, is this a tool or product?

You can certainly create PL/SQL code in your database to execute your
logic. And I assume you can call a PL/SQL procedure from PHP. I know
you can do it from a servlet using a JDBC connection. So I'm not sure
what your issue is, unless you do not understand PL/SQL. Perhaps you
could provide more detail.

Dave
Jul 19 '05 #2

P: n/a
We are using Design Editor in 9i to create modules. We then generate the
modules as 'web modules'. Editor generates all the server code to carry out
simple transactions like insert, update etc.

Someone else's page on web modules is
http://www.iherve.com/oracle/wg_title.htm

What I think we need is a way to get a web module to take fields in the
insert/update of a purchased item (which would be something like '50 watches
of make W123'), run a query to get current stock for W123, and then update
the current stock by adding purchased stock to current stock. We're not
using PHP or servlets, but the Oracle web server. The entire thing is
created from within Oracle itself. I do not understand PL/SQL, although I
have had a look at some tutorials they do not mention anything about
integrating a transaction into a module created by Oracle.

"Dave" <da**********@yahoo.com> wrote in message
news:5e**************************@posting.google.c om...
"none" <no**@none.com> wrote in message

news:<f4******************************@news.terane ws.com>...
Hi. I'm implementing a web deployment using 9i. I have a table for STOCK, which has one or more PURCHASES, which have one or more PURCHASE_ITEMS.

When a purchase item is INSERTED, I'd like the web module component to
update the relevant grandparent record of STOCK, and when UPDATED, to
remember the previous level and adjust accordingly.

I'm sure that this can probably be done in PL/SQL, but don't have a clue
how - all books and tutorials I have found take a 'PHP-style' approach or use servlets and don't seem to use web modules.

I am mostly familiar with Design Editor. Can anyone help? Thanks.


What do you mean by web modules, is this a tool or product?

You can certainly create PL/SQL code in your database to execute your
logic. And I assume you can call a PL/SQL procedure from PHP. I know
you can do it from a servlet using a JDBC connection. So I'm not sure
what your issue is, unless you do not understand PL/SQL. Perhaps you
could provide more detail.

Dave

Jul 19 '05 #3

P: n/a
"none" <no**@none.com> wrote in message news:<ba******************************@news.terane ws.com>...

Ah ok. I learned something today. Never heard of design editor before.
:)

I don't know what you table names and columns are, so I made some
assumptions...

Looks to me like you have two options.

1.) PL/SQL procedure, which you will have to figure out how to call
from your web module...which would look something like this....

create or replace procedure UpdateStock(p_product_number number, p_qty
number)
is
begin
UPDATE STOCK SET STOCK_LEVEL = STOCK_LEVEL + p_qty
WHERE PRODUCT_NUMBER = p_product_number;
commit;
end;
/

2.) Create a trigger on the purchase items table which will be
completely transparent to your application code. Whenever you insert
into the table, it will update the stock levels within the same
Transaction context. This may be easier for you to implement, but it
may depend if you as a developer are allowed to touch the database
schema. The :NEW is special syntax in the trigger representing the new
column values being inserted into the PURCHASE_ITEM table.

create trigger UpdateStock
AFTER INSERT
on PURCHASE_ITEM
for each row
begin
UPDATE STOCK SET STOCK_LEVEL = STOCK_LEVEL + :NEW.PURCHASE_QTY
WHERE PRODUCT_NUMBER = :NEW.PURCHASED_PRODUCT_NUMBER;
end;
/

Also, for a great new Oracle web development tool/environment. Check
out Oracle HTML DB. It is very cool. But knowing PL/SQL is probably
important for creating very custom solutions.

Dave
We are using Design Editor in 9i to create modules. We then generate the
modules as 'web modules'. Editor generates all the server code to carry out
simple transactions like insert, update etc.

Someone else's page on web modules is
http://www.iherve.com/oracle/wg_title.htm

What I think we need is a way to get a web module to take fields in the
insert/update of a purchased item (which would be something like '50 watches
of make W123'), run a query to get current stock for W123, and then update
the current stock by adding purchased stock to current stock. We're not
using PHP or servlets, but the Oracle web server. The entire thing is
created from within Oracle itself. I do not understand PL/SQL, although I
have had a look at some tutorials they do not mention anything about
integrating a transaction into a module created by Oracle.

"Dave" <da**********@yahoo.com> wrote in message
news:5e**************************@posting.google.c om...
"none" <no**@none.com> wrote in message

news:<f4******************************@news.terane ws.com>...
Hi. I'm implementing a web deployment using 9i. I have a table for STOCK, which has one or more PURCHASES, which have one or more PURCHASE_ITEMS.

When a purchase item is INSERTED, I'd like the web module component to
update the relevant grandparent record of STOCK, and when UPDATED, to
remember the previous level and adjust accordingly.

I'm sure that this can probably be done in PL/SQL, but don't have a clue
how - all books and tutorials I have found take a 'PHP-style' approach or use servlets and don't seem to use web modules.

I am mostly familiar with Design Editor. Can anyone help? Thanks.


What do you mean by web modules, is this a tool or product?

You can certainly create PL/SQL code in your database to execute your
logic. And I assume you can call a PL/SQL procedure from PHP. I know
you can do it from a servlet using a JDBC connection. So I'm not sure
what your issue is, unless you do not understand PL/SQL. Perhaps you
could provide more detail.

Dave

Jul 19 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.