Solved

Oracle SQL Developer

Posted on 2014-10-30
14
499 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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Syntax: How to force case sensitive query? 2 44
Loops and updating in SQL Query 9 52
SQL2016 to ORACLE11G linked-server 6 28
Procedure syntax 5 38
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…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

840 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