Oracle SQL Developer

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
chimaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
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
slightwv (䄆 Netminder) Commented:
>>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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chimaAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

slightwv (䄆 Netminder) Commented:
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
chimaAuthor Commented:
slightwv, no errors, get;  0 rows deleted
0
chimaAuthor Commented:
I'll try your other suggestions
0
chimaAuthor Commented:
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
slightwv (䄆 Netminder) Commented:
>>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
slightwv (䄆 Netminder) Commented:
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
chimaAuthor Commented:
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
PortletPaulfreelancerCommented:
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
slightwv (䄆 Netminder) Commented:
>>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
chimaAuthor Commented:
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
slightwv (䄆 Netminder) Commented:
>>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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.