There is apex_util.string_to_table – see my answer to this question. Also, prior to the existence of the above function, I once posted a solution here on my blog. Update In later versions of APEX, apex_util.string_to_table is deprecated, and a similar function apex_string.split is preferred.
This works select DBMS_LOB.substr(myColumn, 3000) from myTable
I’ve never known there to be a difference. The Oracle documentation implies that they are synonyms: The function body begins with the keyword IS (or AS) and ends with the keyword END followed by an optional function name.
When you are selecting INTO a variable and there are no records returned you should get a NO DATA FOUND error. I believe the correct way to write the above code would be to wrap the SELECT statement with it’s own BEGIN/EXCEPTION/END block. Example: … v_final_grade NUMBER; v_letter_grade CHAR(1); BEGIN BEGIN SELECT final_grade INTO v_final_grade … Read more
Firstly, BEGIN..END are merely syntactic elements, and have nothing to do with transactions. Secondly, in Oracle all individual DML statements are atomic (i.e. they either succeed in full, or rollback any intermediate changes on the first failure) (unless you use the EXCEPTIONS INTO option, which I won’t go into here). If you wish a group … Read more
You can definitely get Boolean value from a SELECT query, you just can’t use a Boolean data-type. You can represent a Boolean with 1/0. CASE WHEN (10 > 0) THEN 1 ELSE 0 END (It can be used in SELECT QUERY) SELECT CASE WHEN (10 > 0) THEN 1 ELSE 0 END AS MY_BOOLEAN_COLUMN FROM … Read more
SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE IN (‘FUNCTION’,’PROCEDURE’,’PACKAGE’) The column STATUS tells you whether the object is VALID or INVALID. If it is invalid, you have to try a recompile, ORACLE can’t tell you if it will work before.
Your question is difficult to understand, but if you want to select the numbers from 1 to 100, then this should do the trick: Select Rownum r From dual Connect By Rownum <= 100
The difference is- A function must return a value (of any type) by default definition of it, whereas in case of a procedure you need to use parameters like OUT or IN OUT parameters to get the results. You can use a function in a normal SQL where as you cannot use a procedure in … Read more
CURRENT_DATE and CURRENT_TIMESTAMP return the current date and time in the session time zone. SYSDATE and SYSTIMESTAMP return the system date and time – that is, of the system on which the database resides. If your client session isn’t in the same timezone as the server the database is on (or says it isn’t anyway, … Read more