Thursday 20 September 2012

PLS-00201: identifier 'NVL2' must be declared

Having just tried to use NVL2 inside PLSQL I have discovered it isn't there! This is one of those functions that only exist for SQL not PLSQL. Like DECODE.

NVL2 is quite simple and easily rewritten with IF or CASE statements but these can sometimes make code messy. NVL2 in SQL can just be concatinated within a string and is one command. (Not always a good reason for using it but in some cases...)

Anyway, to get around the problem in these few circumstances, you can create your own function.

CREATE OR REPLACE FUNCTION NVL2 ( p_value IN VARCHAR2, p_newValueIfNotNull IN VARCHAR2, p_newValueIfNull IN VARCHAR2 ) RETURN VARCHAR2 DETERMINISTIC IS BEGIN IF p_value IS NOT NULL THEN RETURN(p_newValueIfNotNull); ELSE RETURN(p_newValueIfNull); END IF; END; /

The function will be used when called from PLSQL, the built-in will be used when called from SQL.

Sorted

Monday 17 September 2012

Diagnose ORA-02292 Errors When Deleting Records in Oracle

Working on an undocumented system and got the error “ORA-02292: integrity constraint (…) violated - child record found” when trying to delete records? This solution provides an anonymous procedure for running in SQL*Plus, Developer, etc, to get the child relationships for a specific table, so you can easily understand the constraints preventing the record being deleted.

The procedure needs two parameters, the start table and the rowid of the record. The following code can be prefixed to the procedure in the script to set up these variables.

VAR vRowId VARCHAR2(20) VAR vSchema VARCHAR2(30) VAR vStartTable VARCHAR2(30) BEGIN :vSchema := USER; :vStartTable := 'MY_TABLE'; SELECT ROWID INTO :vRowId FROM my_table WHERE my_table_id = 5333; END; /
Adjust the query or just add the rowid reference directly, as required. The procedure then uses these values to produce the output, which will be specifically for the record with the rowid selected. Relationships with no data will not be displayed, so you can track down the cause of the ORA-02292 error.

DECLARE --//------------------------------ c_schema CONSTANT VARCHAR2(30) := :vSchema; c_startTable CONSTANT VARCHAR2(30) := :vStartTable; c_startRowId CONSTANT VARCHAR2(20) := :vRowId; --//------------------------------ TYPE t_consColumns IS TABLE OF user_cons_columns.column_name%TYPE; TYPE t_results IS TABLE OF ROWID; TYPE t_processed IS TABLE OF PLS_INTEGER INDEX BY VARCHAR2(50); g_processed t_processed; --//------------------------------ FUNCTION getChildRowids ( p_parentTable IN VARCHAR2, p_childTable IN VARCHAR2, p_parentColumns IN t_consColumns, p_childColumns IN t_consColumns, p_rowid IN ROWID ) RETURN t_results IS l_query VARCHAR2(4000); l_results t_results; BEGIN l_query := 'SELECT c.ROWID' || ' FROM '||c_schema||'.'||p_parentTable||' p,' || c_schema||'.'||p_childTable ||' c' ||' WHERE p.ROWID = '''||p_rowid||''''; FOR i IN 1..p_parentColumns.COUNT LOOP l_query := l_query||' AND p.'||p_parentColumns(i)||' = c.'||p_childColumns(i); END LOOP; EXECUTE IMMEDIATE l_query BULK COLLECT INTO l_results; RETURN(l_results); END getChildRowids; --//------------------------------ FUNCTION getConstraintColumns ( p_constraintName IN VARCHAR2 ) RETURN t_consColumns IS l_columnNames t_consColumns; BEGIN SELECT column_name BULK COLLECT INTO l_columnNames FROM dba_cons_columns WHERE owner = c_schema AND constraint_name = p_constraintName; RETURN(l_columnNames); END getConstraintColumns; --//------------------------------ FUNCTION alreadyProcessed ( p_tableName IN VARCHAR2, p_rowid IN ROWID ) RETURN BOOLEAN IS l_return BOOLEAN := FALSE; BEGIN IF g_processed(p_tableName||'.'||p_rowid)=1 THEN NULL; END IF; RETURN(TRUE); EXCEPTION WHEN NO_DATA_FOUND THEN g_processed(p_tableName||'.'||p_rowid) := 1; RETURN(FALSE); END; --//------------------------------ PROCEDURE processTable ( p_tableName IN VARCHAR2, p_rowid IN ROWID ) IS l_parentColumns t_consColumns; l_childColumns t_consColumns; l_results t_results; BEGIN IF NOT alreadyProcessed(p_tableName,p_rowid) THEN FOR c IN ( SELECT parent.constraint_name parentConstraint, child.constraint_name childConstraint, child.table_name childTable FROM dba_constraints parent, dba_constraints child WHERE parent.table_name = p_tableName AND parent.constraint_name = child.r_constraint_name AND child.r_owner = c_schema AND parent.owner = c_schema ) LOOP l_parentColumns := getConstraintColumns(c.parentConstraint); l_childColumns := getConstraintColumns(c.childConstraint); l_results := getChildRowids(p_tableName, c.childTable, l_parentColumns, l_childColumns, p_rowid); FOR r IN 1..l_results.COUNT LOOP DBMS_OUTPUT.PUT_LINE('Found child records'); DBMS_OUTPUT.PUT_LINE('- for: '||p_tableName||' ['||p_rowid||']'); DBMS_OUTPUT.PUT_LINE('- at: '||c.childTable||' ['||l_results(r)||']'); DBMS_OUTPUT.PUT_LINE('- via: '||c.childConstraint||' -> '||c.parentConstraint); processTable(c.childTable, l_results(r)); END LOOP; END LOOP; END IF; END processTable; --//------------------------------ BEGIN DBMS_OUTPUT.PUT_LINE('Processing '||c_startTable||' ['||c_startRowId||']'); processTable(c_startTable, c_startRowId); END; /

The main sources of data are the views dba_constraints and dba_cons_columns. The procedure uses these to build a dynamic query to check for related data in the child tables. If data is found it outputs the result, then checks down that branch for further child tables.

The procedure only checks one schema, so relationships between schemas will not be included in the search.

Child Records Found

Tuesday 11 September 2012

Oracle Procedure to Write a CLOB to a File

I've seen several ways to do this but this way works nicely for me. (Using Oracle 11g)

CREATE OR REPLACE PROCEDURE clob_to_file ( p_directory IN VARCHAR2, p_filename IN VARCHAR2, p_clob IN CLOB ) IS c_chunk CONSTANT PLS_INTEGER := 32767; l_fHandler UTL_FILE.FILE_TYPE; l_pos PLS_INTEGER := 1; BEGIN l_fHandler := UTL_FILE.FOPEN(p_directory, p_filename, 'W', c_chunk); WHILE l_pos < DBMS_LOB.GETLENGTH(p_clob) LOOP UTL_FILE.PUT(l_fHandler, DBMS_LOB.SUBSTR(p_clob, c_chunk, l_pos)); l_pos := l_pos + c_chunk; UTL_FILE.FFLUSH(l_fHandler); END LOOP; UTL_FILE.FCLOSE(l_fHandler); END; /
The UTL_FILE package writes the clob in 32767 byte chunks. This has a limitation that each 32K chunk has to have a carriage return (I've not come across an output without one, so not captured and processed the error here but you could easily), otherwise you get the following error:

ORA-29285: file write error ORA-06512: at "SYS.UTL_FILE", line 183 ORA-06512: at "SYS.UTL_FILE", line 1169 ORA-06512: at "MYSCHEMA.CLOB_TO_FILE", line 16 ORA-06512: at line 8

Written.