| How to create a counter using Oracle SQL Developer? [message #244225] |
Tue, 12 June 2007 01:35  |
libraryportal
Messages: 4
Registered: June 2007
Location: Singapore
|
Junior Member |
|
|
Is there any way to create a counter using Oracle SQL Developer to create the below Two scenario. Meaning it will recorded down the name of user and ID and time and the date they login.
Library portal home
Library portal home statistics shows how many users (outside and within the campus) visit the library portal.
Page Access statistics is recorded on an hourly basis. Users may select the statistics by
yearly (statistics displayed by all months in the selected year)
monthly (statistics displayed by all days in the selected month)
daily (statistics displayed by all hours in the selected day)
Number of Logins
This statistics will keep track of number of user logins into Portal within and outside campus.
Statistics is recorded on an hourly basis. Users may select statistics by
yearly (statistics displayed by all months in the selected year)
monthly (statistics displayed by all days in the selected month)
daily (statistics displayed by all hours in the selected day)
If there any sample coding, it can help me more.
Thank you very much.
|
|
|
|
|
|
| Re: How to create a counter using Oracle SQL Developer? [message #244240 is a reply to message #244232] |
Tue, 12 June 2007 02:27  |
libraryportal
Messages: 4
Registered: June 2007
Location: Singapore
|
Junior Member |
|
|
I tried. I done something like this:
REM SERVEROUTPUT must be set to ON to display output with DBMS_OUTPUT
SET SERVEROUTPUT ON FORMAT WRAPPED
CREATE OR REPLACE PACKAGE BODY LOG_COUNTER AS
PRAGMA SERIALLY_REUSABLE
PROCEDURE INCREMENT_HOME_NON_LOGIN IS
v_cnt NUMBER (6);
BEGIN
SELECT COUNTER INTO v_cnt
FROM PORTAL_HOME_NON_LOGIN_LOG
WHERE TO_CHAR(LOGDATETIME, 'DD-MON-YYYY H24') = TO_CHAR (LOGDATETIME, 'DD-MON-YYYY H24');
DBMS_OUTPUT.PUT_LINE('RECORD FOUND. -- INCREMENT COUNTER '
+ v_cnt);
EXCEPTION
WHEN NO_DATE_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('RECORD NOT FOUND -- INSERTING RECORD ....');
INSERT INTO PORTAL_HOME_NON_LOGIN_LOG
(LOGDATETIME, COUNTER)
VALUES (TO_TIMESTAMP (SYSDATE, 'DD-MON-YYYY HH24'), 1);
COMMIT;
END INCREMENT_HOME_NON_LOGIN;
END LOG_COUNTER
|
|
|
|
|
|
| Re: How to create a counter using Oracle SQL Developer? [message #244247 is a reply to message #244244] |
Tue, 12 June 2007 02:50 |
libraryportal
Messages: 4
Registered: June 2007
Location: Singapore
|
Junior Member |
|
|
The pragma SERIALLY_REUSABLE indicates that the package state is needed only for the duration of one call to the server. An example could be an OCI call to the database or a stored procedure call through a database link. After this call, the storage for the package variables can be reused, reducing the memory overhead for long-running sessions. For more information, see Oracle Database Application Developer's Guide - Fundamentals.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
