Oracle - Select statement throwing an error

raise application error in oracle
oracle exception when others
cursor exceptions in oracle
raise without exception name oracle
exception handling for insert statement oracle
oracle predefined exceptions
non predefined exceptions in oracle
cursor with exception handling in oracle example

Please check out this link.

You can see the error message:

Note : I intentionally kept roll_no as a character type. I need help with the select statement.

Thanks

Not all your rows will convert to numbers. Unknown and empty string are causing the error.

Errors and Exception Handling, '); > END; > / Your SELECT statement retrieved multiple rows. Consider using a cursor. PL/SQL procedure successfully completed. Exception types. There are  Oracle question 56181: Examine the structure of the EMP_DEPT_VU view:Which SQL statement produces an error?A. SELECT * FROM emp_dept_vu; Statement will not Get 30% Discount on All Your Purchases at PrepAway.com

Not everything converts as in your case a null string if you want as some value. place it in the string in the same format like To_Number('0')

10 Handling PL/SQL Errors, What is the Oracle inbuilt function that will give the error number? For example, in the Oracle Precompilers environment, any database changes made by a failed SQL statement or PL/SQL block are rolled back. Unhandled exceptions can also affect subprograms. If you exit a subprogram successfully, PL/SQL assigns values to OUT parameters.

This worked for me

select to_number(roll_no) from student
WHERE REGEXP_LIKE(roll_no, '^[[:digit:]]+$');

Thank you all

Oracle PL/SQL Exception Handling: Examples to Raise User , What are the complications when an exception occurs in PL SQL? an INTO clause is expected in this SELECT statement told you precisely what the problem is. You can't just "select" inside a stored procedure (or a PL/SQL block) and then have the result magically "returned" from there.

Handling PL/SQL Errors, by any other handler. It must always be written as the last handler in exception block. When you try to insert a duplicate value into a unique column. An internal exception is raised automatically if your PL/SQL program violates an Oracle rule or exceeds a system-dependent limit. PL/SQL predefines some common Oracle errors as exceptions. For example, PL/SQL raises the predefined exception NO_DATA_FOUND if a SELECT INTO statement returns no rows.

Exceptions | DBMS, When 'SELECT' statement that contains INTO clause fetches no rows. ROW_MISMATCH, ORA-06504, When cursor variable data type is  Handle an exception by trapping it with a handler or propagating it to the calling environment. For example, if your SELECT statement returns multiple rows, TimesTen returns an error (exception) at runtime. As the following example shows, you would see TimesTen error 8507, then the associated ORA error message.

Why is Oracle SELECT INTO statement throwing error?, ORA-01422: exact fetch returns more than requested number of rows. select first_name into initiator from person_info;. The error message is  My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts. Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit oracle.com.

Comments
  • Oracle is not mysql, please don't play tag spam. And please post code as text, because that's what it is in reality. We don't want to have to look at a separate picture. thanks
  • Anyway it seems fairly obvious that "Unknown" and "" are not going to be convertible to numbers, so I don't why you would expect TO_NUMBER to work with them. What did you imagine the result would be? What number should "Unknown" be converted to, in your view?
  • I have a db which is screwed up. I don't have write access. And I have similar situation where I have a column which should have been in int, but ended up in archer
  • And? What's your point? You still can't convert arbitrary text into a number. What are you actually trying to achieve?
  • is there any way I can do a cast to that column and get just the numbers in it. I need only numbers present in that column.
  • I need to extract values which are numbers from the column roll_no . is there any way ? And I don't have write access to this db
  • but you have rows where Roll_No is not numeric.. how are you wanting to handle those? If you want to ignore them then put a WHERE clause in your SELECT, e.g. WHERE Is_Numeric( Roll_No) = 1
  • I tried this. it says ORA-00904: "IS_NUMERIC": invalid identifier.
  • create table student ( roll_no varchar(10) ); insert into student values ('10'); insert into student values ('UnKnown'); insert into student values (''); select to_number(roll_no) from student where Is_Numeric( Roll_No) = 1;
  • I think you also need to exclude nulls explicitly, WHERE Roll_No IS NOT NULL AND Is_Numeric(Roll_No) = 1, I am a SQL Server guy not sure IS NOT NULL is valid P-SQL
  • ugh, I personally hate reg exp but glad you were able to move on.