Execute is sql*plus syntax .. try wrapping your call in begin .. end like this: begin temp_proc; end; (Although Jeffrey says this doesn’t work in APEX .. but you’re trying to get this to run in SQLDeveloper .. try the ‘Run’ menu there.)
All the metadata about the columns in Oracle Database is accessible using one of the following views. user_tab_cols; — For all tables owned by the user all_tab_cols ; — For all tables accessible to the user dba_tab_cols; — For all tables in the Database. So, if you are looking for a column like ADD_TMS in … Read more
ALL_TAB_COLUMNS should be queryable from PL/SQL. DESC is a SQL*Plus command. SQL> desc all_tab_columns; Name Null? Type —————————————– ——– —————————- OWNER NOT NULL VARCHAR2(30) TABLE_NAME NOT NULL VARCHAR2(30) COLUMN_NAME NOT NULL VARCHAR2(30) DATA_TYPE VARCHAR2(106) DATA_TYPE_MOD VARCHAR2(3) DATA_TYPE_OWNER VARCHAR2(30) DATA_LENGTH NOT NULL NUMBER DATA_PRECISION NUMBER DATA_SCALE NUMBER NULLABLE VARCHAR2(1) COLUMN_ID NUMBER DEFAULT_LENGTH NUMBER DATA_DEFAULT LONG NUM_DISTINCT … Read more
A procedure does not have a return value, whereas a function has. Example: CREATE OR REPLACE PROCEDURE my_proc (p_name IN VARCHAR2 := ‘John’) as begin … end CREATE OR REPLACE FUNCTION my_func (p_name IN VARCHAR2 := ‘John’) return varchar2 as begin … end Notice how the function has a return clause between the parameter list … Read more
You can do this in Oracle 12.1 or above: declare rc sys_refcursor; begin open rc for select * from dual; dbms_sql.return_result(rc); end; I don’t have DBVisualizer to test with, but that should probably be your starting point. For more details, see Implicit Result Sets in the Oracle 12.1 New Features Guide, Oracle Base etc. For … Read more
Yes. You just have to use the RAISE_APPLICATION_ERROR function. If you also want to name your exception, you’ll need to use the EXCEPTION_INIT pragma in order to associate the error number to the named exception. Something like SQL> ed Wrote file afiedt.buf 1 declare 2 ex_custom EXCEPTION; 3 PRAGMA EXCEPTION_INIT( ex_custom, -20001 ); 4 begin … Read more
You can simply handle the NO_DATA_FOUND exception by setting your variable to NULL. This way, only one query is required. v_column my_table.column%TYPE; BEGIN BEGIN select column into v_column from my_table where …; EXCEPTION WHEN NO_DATA_FOUND THEN v_column := NULL; END; … use v_column here END;
The Apache ExceptionUtils provide the following method: Throwable getRootCause(Throwable throwable) as well as String getRootCauseMessage(Throwable th)
Register an out parameter for the stored procedure, and set the value based on @@ROWCOUNT if using SQL Server. Use SQL%ROWCOUNT if you are using Oracle. Mind that if you have multiple INSERT/UPDATE/DELETE, you’ll need a variable to store the result from @@ROWCOUNT for each operation.
alter table MYTABLE modify (MYCOLUMN null); In Oracle, not null constraints are created automatically when not null is specified for a column. Likewise, they are dropped automatically when the column is changed to allow nulls. Clarifying the revised question: This solution only applies to constraints created for “not null” columns. If you specify “Primary Key” … Read more