Solved

Oracle SQL Developer

Posted on 2014-10-30
14
511 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
14 Comments
 
LVL 77

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 77

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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
LVL 77

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
 
LVL 77

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 77

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 77

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 77

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

Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

759 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