Adam,
>1) No. Not true. I have a public website that has over 5k oracle
>users. 1 user account is used for the public (ie. people who do not
>log in). They all share the same session. When you look at active
>sessions in oracle there is only 1 entry for the public account.
>Unless that account is connecting from a different client toad/cf. CF
>passes the same username/password to oracle for the public account,
>oracle has no way to determine that different requests are different
>sessions, its all based on the client used to connect (dataDirect
>JDBC) and the username/password.
Sorry, should have been more clear: At the end of his mail, James
indicated that he had put page 1 and page 2 on two different servers - i
didn't mean that oracle created a different session for each page or
query. In the majority of cases (most people don't change the
username/password in their data source per *web* user) your web
application uses a single session in the db (per connection if you use
connection pooling). But if you have several web servers connecting to
the same db, then each has its own session.
As an aside, you *can* create two (or more) sessions for the same
user/password using the same client from the same machine - open two
command prompts on your machine, use sqlplus to connect to the same
schema as the same user with the same password, then open a third
command prompt, connect as sys or system and issue:
SELECT SID, username, machine, program FROM v$session WHERE schemaname =
'[[SCHEMANAME]]';
you will see two sessions with different sids, but all else will be
equal.
>
>2) Well thats kinda what I'm wondering. I think the commit occurs
>directly following the </cfstoredproc> tag, because Oracle
>automatically commits when _any_ stored procedure finishes without
>exception. Regardless if CF made the procedure call or an Oracle Forms
>app did. I don't think the <cftransaction> tag can modify the
>procedure call to tell oracle not to commit. Even if it could, would
>it be able to chain that to all procedures that were internal to the
>initial procedure call?
Without cftransaction, the commit DOES happen right after
</cfstoredproc>, but that's because coldfusion is telling oracle to
commit. Try this:
session one:
[EMAIL PROTECTED]> truncate table t reuse storage;
Table truncated.
[EMAIL PROTECTED]> declare
2 newid number;
3 begin
4 newid := NULL;
5 t_insert('this is a test',newid);
6 end;
7 /
PL/SQL procedure successfully completed.
session two:
[EMAIL PROTECTED]> select * from t;
no rows selected
back in session one:
[EMAIL PROTECTED]> commit;
Commit complete.
back in session 2:
[EMAIL PROTECTED]> /
ID NAME
---------- --------------------------------------------------
1092 this is a test
i.e. oracle does not automatically commit a stored proc any more than it
does a "regular" block of sql - cf does (on both counts), unless you use
cftransaction.
As for internal procedures, same difference: they're all part of one
transaction unless one of them called commit (or issued a ddl statement
like "alter table...", which causes a commit)- which is something you
should NEVER do within a stored proc, since it means you will not be
able to reuse that proc in a longer or different transaction tomorrow.
Bottom line: it is up to the client to decide when to commit because the
client knows when he is "done". Some people use commits in stored procs
along with nested procs by creating an outer "unit of work" procedure
which just manages the transaction - it calls the nested procs and then
issues a commit at the end - but there's no need to do that in cf:
either call a single proc by itself and cf will commit afterwards or
call several procs in a cftransaction and cf will commit after the
transaction: cf manages the "unit of work" for you.
/t
>
>-Adam
>
>On 6/8/05, RADEMAKERS Tanguy <[EMAIL PROTECTED]> wrote:
>> Adam,
>>
>> 1) Even with the same username/password, oracle treats it as two
>> sessions. If you have TOAD you can verify this with the
>session browser.
>>
>> 2) stored procs don't change the transaction handling of
>oracle (unless,
>> or course, you manually commit within the body of the proc) - ie cf
>> handles cfstoredproc just like cfquery: by default it will
>commit after
>> each </cfstoredprocedure> unless you use <cftransaction>.
>>
>> /t
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Find out how CFTicket can increase your company's customer support
efficiency by 100%
http://www.houseoffusion.com/banners/view.cfm?bannerid=49
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:209016
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
