Date and hour in stored procedure

[oracle newbie]

create or replace PROCEDURE LIBHOME(ldt zen.logdatetime %TYPE) IS
lv_count NUMBER;
BEGIN
SELECT COUNT(*)
INTO lv_count
FROM zen
WHERE TRUNC(logdateti me) = TRUNC(sysdate);

IF lv_count > 0 THEN

UPDATE zen
SET counter = counter + 1
WHERE TRUNC(logdateti me) = TRUNC(sysdate);
ELSE
INSERT
INTO zen(logdatetime , counter)
VALUES(sysdate, 1);
DBMS_OUTPUT.PUT _LINE('ONE RECORD IS ADDED......!');
END IF;

COMMIT;
END;

lResult of Above Procedure
=============== =============== ===========
LOGDATETIME
10-JUL-07

I have a stored procedure (top) which will stored the date and counter.
When I run the procedure, It will produce the current date and counter will increment by 1.

Is it possible that the LOGDATETIME can display DD-MON-YYYY HH24 like the result (bottom).

Can you advice me on the coding that I should change so that the result can be like below.


Desire Result
=============== =============== ===========
LOGDATETIME
10-JUL-07 15
10-JUL-07 16
10-JUL-07 17
10-JUL-07 18
10-JUL-07 19
10-JUL-07 20


Thank You very much

[debasisdas]

TRY TO USE THIS

[CODE=ORACLE]
INSERT INTO zen(logdatetime , counter) VALUES (TO_CHAR(SYSDAT E,'DD-MM-YY HH24'), 1);[/CODE]

[oracle newbie]

TRY TO USE THIS

[CODE=ORACLE]
INSERT INTO zen(logdatetime , counter) VALUES (TO_CHAR(SYSDAT E,'DD-MM-YY HH24'), 1);[/CODE]

I'm using SQL developer. It return the error (below)

ORA-01830: date format picture ends before converting entire input string
ORA-06512: at "NYP_LIBPORTAL. LIBHOME", line 15
ORA-06512: at line 6


CREATE OR REPLACE PROCEDURE libhome(ldt zen.logdatetime %TYPE) IS
lv_count NUMBER;
BEGIN
SELECT COUNT(*)
INTO lv_count
FROM zen
WHERE TRUNC(logdateti me) = TRUNC(sysdate);

IF lv_count > 0 THEN

UPDATE zen
SET counter = counter + 1
WHERE TRUNC(logdateti me) = TRUNC(sysdate);
ELSE
INSERT
INTO zen(logdatetime , counter)
VALUES(to_char( sysdate, 'DD-MON-YY HH24'), 1);

DBMS_OUTPUT.PUT _LINE('ONE RECORD IS ADDED......!');
END IF;

COMMIT;
END;

[debasisdas]

try this

[CODE=oracle]INSERT INTO zen(logdatetime , counter) VALUES (TO_CHAR(SYSDAT E,'DD-MM-YY HH24:mi:ss'), 1);[/CODE]

if any still error persists post with your table structure.

[oracle newbie]

try this

[CODE=oracle]INSERT INTO zen(logdatetime , counter) VALUES (TO_CHAR(SYSDAT E,'DD-MM-YY HH24:mi:ss'), 1);[/CODE]

if any still error persists post with your table structure.

Column Name          Data Type               Nullable          Column ID
LOGDATETIME        DATE	            Yes	  1	
COUNTER	               NUMBER               Yes	  2

Above is my table structure. The LOGDATETIME is DATE and COUNTER is NUMBER.