Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Oracle SQL Developer

Posted on 2014-10-30
14
Medium Priority
?
539 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 78

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 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 78

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 78

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 78

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 49

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 78

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 78

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

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…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to recover a database from a user managed backup
Suggested Courses

916 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