SQL*Plus: how to query using strings

Question

I'm trying to execute the next command in SQL*Plus:

DELETE FROM SOLUTION WHERE NAME LIKE 'Autotest %';

It doesn't anything. I guess the problem is in the apostrophe characters but the next query works:

SELECT ID FROM SOLUTION WHERE NAME LIKE 'Autotest %';

So I have no idea of what's happening.

What do you mean by "it doesn't do anything"? Does it error? Affect no rows? Or after running it there are still rows starting with "Autotest "? IIRC in Sql Plus you may need to commit after data manipulation commands before their effect can be fully felt. https://stackoverflow.com/questions/9541013/oracle-what-statements-need-to-be-committed — cf_en, Jul 26 '17 at 13:26
Did you commit transaction? I guess that you deleted rows and checked if they exists from another session. — Lukasz Szozda, Jul 26 '17 at 13:27
@ChrisFlynn When I execute the command it gets stuck and the only thing I can do is cancel the operation with Ctrl + C — Lorenzo Lerate, Jul 26 '17 at 13:29
@lad2025 I can't commit the transaction because I'm forced to cancel the command — Lorenzo Lerate, Jul 26 '17 at 13:30
Please show printscreens before and after delete. Maybe your session is blocked by another one. — Lukasz Szozda, Jul 26 '17 at 13:31
@lad2025 The table was blocked, because I couldn't execute any DELETE command in that specific table even `DELETE FROM SOLUTION`. I closed all the sessions and it's working properly. Thanks! — Lorenzo Lerate, Jul 26 '17 at 13:55

score 2 · Answer 1 · answered Jul 26 '17 at 13:37

2

Did you close your connection and commit the changes in SQL Developer? Consider using = instead of LIKE for queries.

answered Jul 26 '17 at 13:37

Gabsii

score 1 · Accepted Answer · answered Jul 26 '17 at 16:22

When I execute the command it gets stuck and the only thing I can do is cancel the operation with Ctrl + C

This clearly indicates that your session is blocked. You could check it using:

SELECT *
FROM v$session s
WHERE blocking_session IS NOT NULL;

After DELETE you should commit transaction(first check your setting show autocommit). More SET AUTOCOMMIT

2 Answers2