Home » SQL & PL/SQL » SQL & PL/SQL » help whit RECORD. (WINDOWNS XP)
| help whit RECORD. [message #343506] |
Wed, 27 August 2008 07:53  |
polo_sport09@hotmail.com
Messages: 3
Registered: August 2008
Location: SWEDEN
|
Junior Member |
|
|
Hello.. my first forum post..
i have create a table
CREATE TABLE table_stat(
TABLESPACE_NAME VARCHAR(50),
BYTES_USED NUMBER,
BYTES_FREE NUMBER,
LARGEST NUMBER,
PERCENT_USED NUMBER(5,3))
I have make a procedure,cursor and it will insert the tablespace statistic to the table i created, table_stat
CREATE OR REPLACE Procedure p_statistik
( namn_in IN varchar2 )
IS
e_test_fel EXCEPTION;
e_testtvo EXCEPTION;
e_test EXCEPTION;
V_TABLESPACE_NAME VARCHAR(50);
V_BYTES_USED NUMBER;
V_BYTES_FREE NUMBER;
V_LARGEST NUMBER;
V_PERCENT_USED NUMBER(5,3);
CURSOR c1 IS
SELECT a.TABLESPACE_NAME,
a.BYTES bytes_used,
b.BYTES bytes_free,
b.largest,
ROUND(((a.BYTES-b.BYTES)/a.BYTES)*100,2) percent_used
FROM
(
SELECT TABLESPACE_NAME,
SUM(BYTES) BYTES
FROM dba_data_files
GROUP BY TABLESPACE_NAME
)
a,
(
SELECT TABLESPACE_NAME,
SUM(BYTES) BYTES ,
MAX(BYTES) largest
FROM dba_free_space
GROUP BY TABLESPACE_NAME
)
b
WHERE a.TABLESPACE_NAME=b.TABLESPACE_NAME
ORDER BY ((a.BYTES-b.BYTES)/a.BYTES) DESC;
cursor c2 is
SELECT a.TABLESPACE_NAME,
a.BYTES bytes_used,
b.BYTES bytes_free,
b.largest,
ROUND(((a.BYTES-b.BYTES)/a.BYTES)*100,2) percent_used
FROM
(
SELECT TABLESPACE_NAME,
SUM(BYTES) BYTES
FROM dba_data_files
GROUP BY TABLESPACE_NAME
)
a,
(
SELECT TABLESPACE_NAME,
SUM(BYTES) BYTES ,
MAX(BYTES) largest
FROM dba_free_space
GROUP BY TABLESPACE_NAME
)
b
WHERE a.TABLESPACE_NAME=b.TABLESPACE_NAME
AND a.tablespace_name = namn_in
ORDER BY ((a.BYTES-b.BYTES)/a.BYTES) DESC;
--**********************************************************************
BEGIN
IF namn_in IS null
THEN
OPEN c1;
LOOP
Fetch c1 into v_TABLESPACE_NAME, v_BYTES_USED,v_BYTES_FREE,v_LARGEST,v_PERCENT_USED;
exit when c1%NOTFOUND;
INSERT INTO table_stat ( DATUM,TABLESPACE_NAME, BYTES_USED,BYTES_FREE,LARGEST,PERCENT_USED)
values (SYSDATE,V_TABLESPACE_NAME, V_BYTES_USED,V_BYTES_FREE,V_LARGEST,V_PERCENT_USED);
END LOOP;
IF 2 >1 THEN
RAISE e_testtvo;
END IF;
close c1;
ELSE
OPEN c2;
LOOP
FETCH c2 INTO v_TABLESPACE_NAME, v_BYTES_USED,v_BYTES_FREE,v_LARGEST,v_PERCENT_USED;
exit when c2%NOTFOUND;
INSERT INTO table_stat ( DATUM,TABLESPACE_NAME, BYTES_USED,BYTES_FREE,LARGEST,PERCENT_USED)
values (SYSDATE,V_TABLESPACE_NAME, V_BYTES_USED,V_BYTES_FREE,V_LARGEST,V_PERCENT_USED);
END LOOP;
IF C2%ROWCOUNT=0 THEN
RAISE e_test_fel;
END IF;
IF C2%ROWCOUNT=1 THEN
RAISE e_test;
END IF;
close c2;
END IF;
EXCEPTION WHEN e_test THEN
DBMS_OUTPUT.PUT_LINE('Du insertar '||c2%ROWCOUNT||'st tablespace in i table_stat');
WHEN e_testtvo THEN
DBMS_OUTPUT.PUT_LINE('Du insertar' ||c1%ROWCOUNT|| 'st tablespace in i table_stat');
WHEN e_test_fel THEN
DBMS_OUTPUT.PUT_LINE('Du insertar' ||c2%ROWCOUNT|| ' st TS, ' || namn_in|| ' existerar EJ');
END;
[B]NOW I NEED TO MAKE A RECORD ON THIS...
B]
--AND I NEED TO CHANGE DATE ON THE TABLE....
its 2008-08-27 14:48:48 on table_stat
i need it to be yyyy-mm-dd just in my table, table_stat
Some1 help me please...first weak as trainee, probationer
[Updated on: Wed, 27 August 2008 07:55] Report message to a moderator |
|
|
|
|
|
|
|
| Re: help whit RECORD. [message #343570 is a reply to message #343506] |
Wed, 27 August 2008 09:45  |
tyler_durden
Messages: 14
Registered: August 2008
Location: http://tinyurl.com/63fmwx
|
Junior Member |
|
|
| Quote: |
--AND I NEED TO CHANGE DATE ON THE TABLE....
its 2008-08-27 14:48:48 on table_stat
i need it to be yyyy-mm-dd just in my table, table_stat
Some1 help me please...first weak as trainee, probationer
|
Oracle uses its own internal format to store dates - fixed-length fields of 7 bytes each.
- You *cannot* change this internal format.
- What you *can* do is - use the TO_CHAR function to format your date and display only the relevant pieces of information.
If you do not want to store the timestamp in the table itself, then use TRUNC(SYSDATE) in your INSERT statement in the procedure.
If you want to display the date in "yyyy-mm-dd" format in your SELECT statement, then use TO_CHAR(datum, 'yyyy-mm-dd').
HTH
tyler_durden
|
|
|
|
| Re: help whit RECORD. [message #343576 is a reply to message #343506] |
Wed, 27 August 2008 10:04 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
The problem is that I can't work out what you want to do.
There is no column that has a time element (Date, Timestamp) in your table.
|
|
|
|
Goto Forum:
Current Time: Thu Jan 02 18:34:37 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
