Home » SQL & PL/SQL » SQL & PL/SQL » help whit RECORD. (WINDOWNS XP)
help whit RECORD. [message #343506] Wed, 27 August 2008 07:53 Go to next message
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 #343538 is a reply to message #343506] Wed, 27 August 2008 08:38 Go to previous messageGo to next message
polo_sport09@hotmail.com
Messages: 3
Registered: August 2008
Location: SWEDEN
Junior Member
Some1? Sad
Re: help whit RECORD. [message #343540 is a reply to message #343538] Wed, 27 August 2008 08:41 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
Read and follow the posting guidelines, then edit your post so that it adheres to those guidelines.
Re: help whit RECORD. [message #343570 is a reply to message #343506] Wed, 27 August 2008 09:45 Go to previous messageGo to next message
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 Go to previous message
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.
Previous Topic: query help
Next Topic: UTL_FILE - buffer overflow, limit of 2000 bytes
Goto Forum:
  


Current Time: Fri Dec 09 03:54:30 CST 2016

Total time taken to generate the page: 0.07164 seconds