Home » SQL & PL/SQL » SQL & PL/SQL » what is the error here?
what is the error here? [message #362959] Thu, 04 December 2008 12:28 Go to next message
nandac
Messages: 41
Registered: July 2006
Location: jersey city, usa
Member
i'm new to plsql. need to know what is wrong here :

logic is : create temp table, do insert into temp table from existing table, update temp table and select from temp table.


set linesize 200
set feedback off
set verify off
set termout off

set markup html on spool on
spool /tmp/space_report_&1..html

create global temporary table tablespace_rep
(
ts_name char(50),
status char(50),
size float,
used float,
free float,
pctfree float,
maxspaceused float
)

insert into tablespace_rep
SELECT D.TABLESPACE_NAME,D.STATUS,
ROUND((A.BYTES/1024/1024),2) "Size(MB)",
ROUND(((A.BYTES-DECODE(F.BYTES,NULL,0,F.BYTES))/1024/1024),2) "Used(MB)",
ROUND(((A.BYTES/1024/1024)-(A.BYTES-DECODE(F.BYTES,NULL,0,F.BYTES))/1024/1024),2) "Free(MB)",
ROUND((((A.BYTES/1024/1024)-(A.BYTES-DECODE(F.BYTES,NULL,0,F.BYTES))/1024/1024)/
(A.BYTES/1024/1024))*100.0,2) "%Free"
FROM SYS.DBA_TABLESPACES D,SYS.SM$TS_AVAIL A,SYS.SM$TS_FREE F
WHERE D.TABLESPACE_NAME = A.TABLESPACE_NAME
AND F.TABLESPACE_NAME(+)=D.TABLESPACE_NAME
order by 6

update tablespace_rep set maxspaceused = (select max(DAILY_GROWTH) from TS_DAILY_GROWTH where TABLESPACE_NAME = t
s_name) from tablespace_rep

select ts_name "TABLESPACE_NAME", status "Status", size "Size(MB)", used "Used(MB)", free "Free(MB)", pctfree "%F
ree", (free * 90/100)/maxspaceused "Days to fill 90%" from tablespace_rep order by 7
/
spool off
exit


I'm getting the error : select ts_name "TABLESPACE_NAME", status "Status", size "Size(MB)", used "Used(MB)", free "Free(MB)", pctfree "%Free", (free * 90/100)/maxspaceused from tablespace_rep order by 7
*
ERROR at line 1:
ORA-00936: missing expression

Report message to a moderator

Re: what is the error here? [message #362960 is a reply to message #362959] Thu, 04 December 2008 12:32 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
For one, you don't have semicolons ending any of your statements, therefore they run as a single statement.

Report message to a moderator

Re: what is the error here? [message #362962 is a reply to message #362959] Thu, 04 December 2008 12:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and use code tags.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel

Report message to a moderator

Re: what is the error here? [message #362964 is a reply to message #362960] Thu, 04 December 2008 12:42 Go to previous messageGo to next message
nandac
Messages: 41
Registered: July 2006
Location: jersey city, usa
Member
>For one, you don't have semicolons ending any of your >statements, therefore they run as a single statement.

so is there a problem if it runs as a single statement?

Report message to a moderator

Re: what is the error here? [message #362965 is a reply to message #362964] Thu, 04 December 2008 12:43 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
Yes, because then it is not valid syntax.

Report message to a moderator

Re: what is the error here? [message #362966 is a reply to message #362959] Thu, 04 December 2008 12:49 Go to previous messageGo to next message
nandac
Messages: 41
Registered: July 2006
Location: jersey city, usa
Member
i put a semi-colon at the end of each sql statement. now i am getting more errors - like :

ERROR at line 5:
ORA-00904: : invalid identifier

ERROR at line 1:
ORA-00942: table or view does not exist

ERROR at line 1:
ORA-00933: SQL command not properly ended

ERROR at line 1:
ORA-00936: missing expression

ERROR at line 1:
ORA-00936: missing expression

Report message to a moderator

Re: what is the error here? [message #362968 is a reply to message #362959] Thu, 04 December 2008 13:04 Go to previous messageGo to next message
knicely87
Messages: 25
Registered: December 2008
Location: Pittsburgh, PA
Junior Member
SIZE and PCTFREE are Oracle reserved words and can not be used as column names in your table.

Report message to a moderator

Re: what is the error here? [message #362972 is a reply to message #362966] Thu, 04 December 2008 13:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Use SQL*Plus and copy and paste the whole session including the statements.
Before read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and use code tags.
Use the "Preview Message" button to verify.

Regards
Michel

Report message to a moderator

Re: what is the error here? [message #363080 is a reply to message #362959] Fri, 05 December 2008 01:04 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
nandac wrote on Thu, 04 December 2008 19:28
i'm new to plsql. need to know what is wrong here :

logic is : create temp table, do insert into temp table from existing table, update temp table and select from temp table.


What's wrong here is that you use SQLServer logic in an Oracle database.
The way this should be done is in a single SQL.

Report message to a moderator

Re: what is the error here? [message #363133 is a reply to message #363080] Fri, 05 December 2008 03:13 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I think your query sould look something like this:
select tablespace_name
      ,status
      ,ts_size
      ,ts_used
      ,ts_free
      ,ts_pct_free
      ,ts_maxspaceused
      ,(ts_free * 90/100)/ts_maxspaceused Days_to_fill_90_pct
from (select d.TABLESPACE_NAME
            ,d.status 
            ,ROUND((A.BYTES/(1024*1024)),2) ts_size
            ,ROUND(((A.BYTES-NVL(F.BYTES,0))/(1024*1024)),2) ts_Used
            ,ROUND(((A.BYTES/1024/1024)-(A.BYTES-NVL(F.BYTES,0))/(1024*1024)),2) ts_Free
            ,ROUND((((A.BYTES/1024/1024)-(A.BYTES-NVL(F.BYTES,0))/(1024*1024))/(A.BYTES/(1024*1024)))*100.0,2) ts_PCT_Free
            ,(select max(DAILY_GROWTH) from TS_DAILY_GROWTH where TABLESPACE_NAME = d.tablespace_name) ts_maxspaceused            
      FROM   SYS.DBA_TABLESPACES D
            ,SYS.SM$TS_AVAIL A
            ,SYS.SM$TS_FREE F
      WHERE D.TABLESPACE_NAME = A.TABLESPACE_NAME
      AND F.TABLESPACE_NAME(+)=D.TABLESPACE_NAME);

Report message to a moderator

Previous Topic: How Can I use Sum() Over (Partion By) in Where clause.
Next Topic: passing control to sql script
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Jul 14 10:36:13 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.