Author: Dev

ORA-00913: too many values ORA-00913: too many values Cause: The SQL statement requires two sets of values equal in number. Solution: Check the number of items in each set and change the SQL statement to make them equal. Example: SELECT * FROM COURSE c WHERE c.COURSE_ID IN (SELECT o.* FROM ORDERS o); Output: ORA-00913: too…(Continue Reading)

PLS-00215: String length constraints must be in range (1 .. 32767) PLS-00215: String length constraints must be in range (1 .. 32767) Cause: When a character variable was declared, a length outside the legal range was specified. Solution: Change the length constraint, making sure that it lies in the range 1 .. 32767. Example: declare…(Continue Reading)

ORA-06553: PLS-306: wrong number or types of arguments in call to ORA-06553: PLS-306: wrong number or types of arguments in call to Cause: An Oracle function was referenced with an incorrect number of arguments. Solution: Correct the syntax of the function by entering the required number of arguments. Example: create or replace function TEST(p_id number)…(Continue Reading)

How to list all packages from Oracle database To list all packages from Oracle database you can query: USER_PROCEDURES, ALL_PROCEDURES, DBA_PROCEDURES, USER_OBJECTS. The most used to show the packages in the database are USER_OBJECTS and USER_PROCEDURES. Privileges may be required to query certain tables or views. Examples SELECT * FROM USER_PROCEDURES ; SELECT * FROM…(Continue Reading)

How to list all functions from Oracle database To list all functions from Oracle database you can query: USER_PROCEDURES, ALL_PROCEDURES, DBA_PROCEDURES, USER_OBJECTS. Examples SELECT * FROM USER_PROCEDURES ; SELECT * FROM USER_PROCEDURES WHERE OBJECT_TYPE=’FUNCTION’; SELECT * FROM ALL_PROCEDURES WHERE OBJECT_TYPE=’FUNCTION’; SELECT * FROM DBA_PROCEDURES WHERE OBJECT_TYPE=’FUNCTION’; SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE=’FUNCTION’ AND ORACLE_MAINTAINED=’N’;