Home » Other » Client Tools » How to create a counter using Oracle SQL Developer?
How to create a counter using Oracle SQL Developer? [message #244225] Tue, 12 June 2007 01:35 Go to next message
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 #244232 is a reply to message #244225] Tue, 12 June 2007 01:54 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Please at least TRY to do your own assignment, before asking us...
Re: How to create a counter using Oracle SQL Developer? [message #244240 is a reply to message #244232] Tue, 12 June 2007 02:27 Go to previous messageGo to next message
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 #244244 is a reply to message #244240] Tue, 12 June 2007 02:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please always post your Oracle version (4 decimals).
Please read and follow How to format your posts.

I don't read the logic but this does not compile: + is the operator to add 2 numbers.

One question: why "PRAGMA SERIALLY_REUSABLE"?

Regards
Michel
Re: How to create a counter using Oracle SQL Developer? [message #244247 is a reply to message #244244] Tue, 12 June 2007 02:50 Go to previous messageGo to next message
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.

Re: How to create a counter using Oracle SQL Developer? [message #244250 is a reply to message #244244] Tue, 12 June 2007 02:53 Go to previous messageGo to next message
libraryportal
Messages: 4
Registered: June 2007
Location: Singapore
Junior Member
Are you doing an assignment something like this?
Re: How to create a counter using Oracle SQL Developer? [message #244252 is a reply to message #244247] Tue, 12 June 2007 02:54 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I know what it means and I already read the doc.
The question is: Why do you use it? Not what does it mean.
Obviously you don't know very well PL/SQL and using this statement is an advanced use.

Regards
Michel

[Updated on: Tue, 12 June 2007 02:54]

Report message to a moderator

Previous Topic: spooling a file with filename and current timestamp
Next Topic: How to avoid prompt for subsitution variable
Goto Forum:
  


Current Time: Thu Mar 28 16:20:23 CDT 2024