Solved

Oracle SQL Developer

Posted on 2014-10-30
14
484 Views
Last Modified: 2014-10-30
Hello, I get the following error (and btw; done little sql programming, but want to do more);
Error starting at line 1 in command:
DELETE IP_ADDRESS FROM TIGR_WEB_IP_BLACK_LIST
Error at Command Line:1 Column:18
Error report:
SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 -  "SQL command not properly ended"
*Cause:    
*Action:

By executing this statement;
DELETE IP_ADDRESS FROM TIGR_WEB_IP_BLACK_LIST
WHERE WEB_IP_BLACK_LIST_ID = '1'
AND CREATION_DATE_TIME = '30-OCT-14';

Comments appreciated.
Thanks
0
Comment
Question by:chima
  • 7
  • 6
14 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40414582
What are you wanting to delete?  You delete 'rows' from a table not a column.

For correct syntax is:
DELETE FROM TIGR_WEB_IP_BLACK_LIST
WHERE WEB_IP_BLACK_LIST_ID = '1'
AND CREATION_DATE_TIME = to_date('30-OCT-14',DD-MON-YY');

Notice I also added a TO_DATE call.  You should get in the habit of explicit data type conversions and not rely on Oracle to try and figure them out for you.
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 40414586
>>AND CREATION_DATE_TIME = to_date('30-OCT-14',DD-MON-YY');

Should have also pointed out that dates in Oracle have a time stamp built in so if creation_date_time has the time piece populated you might actually want:

AND
(
  CREATION_DATE_TIME >= to_date('30-OCT-14',DD-MON-YY')
    and
  CREATION_DATE_TIME < to_date('30-OCT-14',DD-MON-YY')+1
);

If there is no index on CREATION_DATE_TIME then:
AND trunc(CREATION_DATE_TIME) = to_date('30-OCT-14',DD-MON-YY')
0
 

Author Comment

by:chima
ID: 40414601
slightwv, thanks, with your corrections, I get this;
Error starting at line 1 in command:
DELETE FROM TIGR_WEB_IP_BLACK_LIST
WHERE WEB_IP_BLACK_LIST_ID = '1'
AND CREATION_DATE_TIME = to_date('30-OCT-14',DD-MON-YY');
Error at Command Line:3 Column:54
Error report:
SQL Error: ORA-01756: quoted string not properly terminated
01756. 00000 -  "quoted string not properly terminated"
*Cause:    
*Action:
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40414604
Error is self explanatory.  I had a typo.  What looks like it should have an extra quote?

AND CREATION_DATE_TIME = to_date('30-OCT-14','DD-MON-YY');

Same for the rest of my examples.  I copied/pasted them.
0
 

Author Comment

by:chima
ID: 40414611
slightwv, no errors, get;  0 rows deleted
0
 

Author Comment

by:chima
ID: 40414613
I'll try your other suggestions
0
 

Author Comment

by:chima
ID: 40414618
slightwv, this worked;
DELETE FROM TIGR_WEB_IP_BLACK_LIST
WHERE WEB_IP_BLACK_LIST_ID = '1'
AND
(
  CREATION_DATE_TIME >= to_date('30-OCT-14','DD-MON-YY')
    and
  CREATION_DATE_TIME < to_date('30-OCT-14','DD-MON-YY')+1
);
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40414622
>>slightwv, no errors, get;  0 rows deleted

Figured that.  The column probably has the time portion populated.

You still haven't answered what you are really trying to do.

What did you think "DELETE IP_ADDRESS FROM" was going to do?

The addition of the IP_ADDRESS confuses me.  I just want to make sure you really want to delete rows and aren't trying to set a column to a NULL value, drop a column or something else.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40414634
Since you are just starting out, I would also suggest you familiarize yourself with the online documentation.

If has all the commands and syntax and many times has examples.

DELETE is here:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_8005.htm#SQLRF01505

On that page, click the 'Examples' link and you get here:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_8005.htm#i2143613
0
 

Author Closing Comment

by:chima
ID: 40414649
I would not have thought that it needed so much.  I had found an example at http://www.techonthenet.com/sql/delete.php.  Obviously I need a better source.  Thank you so much.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40414662
no points please

IF this was intended to change just the IP_ADDRESS column
DELETE IP_ADDRESS FROM TIGR_WEB_IP_BLACK_LIST
WHERE WEB_IP_BLACK_LIST_ID = '1'
AND CREATION_DATE_TIME = '30-OCT-14';
rather than deleting the rows, then UPDATE & SET would be used. like this:


UPDATE TIGR_WEB_IP_BLACK_LIST
SET IP_ADDRESS = NULL
WHERE WEB_IP_BLACK_LIST_ID = '1'
AND
(
  CREATION_DATE_TIME >= to_date('30-OCT-14','DD-MON-YY')
    and
  CREATION_DATE_TIME < to_date('30-OCT-14','DD-MON-YY')+1
);

UPDATE documentation is here:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_10008.htm#SQLRF01708
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40414668
>>Obviously I need a better source.

That source looked OK to me.  I didn't see where it had "DELETE <SOMETHING> FROM" syntax.

I'm still not sure what you were thinking that would do.

>>I would not have thought that it needed so much.

It didn't really.  Dates in Oracle confuse a LOT of people.  They don't account for the time piece being in the column automatically and possible index usage.  That comes with time.

The top Expert on the site has a really good article on dates and times for several databases.

It is worth reading:
http://www.experts-exchange.com/Database/Miscellaneous/A_1499-DATE-and-TIME-don%27t-be-scared-and-do-it-right-the-first-time.html

>>Thank you so much.

No problem.  Glad to help.  That is why we are here!
0
 

Author Comment

by:chima
ID: 40414697
slightwv,
You still haven't answered what you are really trying to do.
>>> I'm testing a login function, that will black list the IP after numerous tries at logging in.

What did you think "DELETE IP_ADDRESS FROM" was going to do?
>>> I have some knowledge of sql, and I should have caught my error.  I just need to do more sql statement structures/programming.
I'm now needing an INSERT statement.  I'll look at the links you provided.  If I can get it, then I will submit another questions.
I actually used SQL Developer to manually insert my IP address into the "white list"  When my IP is in this list, my IP will not be black listed.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40414757
>>I'm now needing an INSERT statement.

Learn the 'correct' way now.  Specify the column names on the insert side:
insert into table(col1, col2, col3) values('a','b','c');

This is 'valid' syntax if the table has 3 columns but has issues:
insert into table values('a','b','c');

The issues is someday when a column is added to the table.  The table now has 4 columns and the insert only only accounts for 3 of them and it will generate an error.

If you provide the columns the values are supposed to go into, it will always work (until someone drops one of the columns).
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now