Tuesday, 9 October 2012

Oracle Function to Split String into an Array

This deterministic function creates a general function for converting a comma seperated string into an array.

First of all you need to create a varchar2 array type. I've found it very useful to create a generic varchar2 array I can use anywhere.

Now the function...

CREATE OR REPLACE FUNCTION string_to_array ( p_string IN VARCHAR2, p_seperator IN VARCHAR2 DEFAULT ',' ) RETURN VARCHAR2_ARRAY DETERMINISTIC IS l_string VARCHAR2(4000) := p_seperator||p_string||p_seperator; l_array VARCHAR2_ARRAY := VARCHAR2_ARRAY(); BEGIN IF LENGTH(p_seperator) != 1 THEN RAISE_APPLICATION_ERROR(-20101,'Seperator should only be one character'); END IF; SELECT SUBSTR(l_string, INSTR(l_string,p_seperator,1,LEVEL)+1, INSTR(l_string,p_seperator,1,LEVEL+1) - INSTR(l_string,p_seperator,1,LEVEL)-1 ) token BULK COLLECT INTO l_array FROM DUAL CONNECT BY LEVEL < LENGTH(l_string)-LENGTH(REPLACE(l_string,p_seperator,'')); RETURN(l_array); END; /

String Split...Done.

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.


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


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


Thursday, 30 August 2012

SQL Query to Return All Sundays in the Last Year

This is a tweek to the previous post, which will return the dates of all the Sundays in the last year.

WITH weeks AS ( SELECT SYSDATE-((LEVEL-1)*7) weekDate, TO_NUMBER(TO_CHAR(SYSDATE,'D')) currentDay, TO_NUMBER(TO_CHAR(TO_DATE('01012012','DDMMYYYY'),'D')) targetDay FROM DUAL CONNECT BY LEVEL < 53 ) SELECT w.currentDay, w.targetDay, w.weekDate, w.weekDate-MOD((w.currentDay+7)-w.targetDay,7) previousSunday FROM weeks w
The reference to 01012012 is the key to listing Sundays, (as this date was a Sunday). To list dates for a different day just change this to whichever day you're after.

Wednesday, 29 August 2012

SQL Query to Return Last Particular Day of the Week

This query will return the last date of a specified day of the week. EG The date of the previous Sunday (or whichever day is set).

The date 01012012 in the above example is a Sunday. Change this to a date for whichever day of the week you're aiming for.

WITH params AS ( SELECT SYSDATE currentDate, TO_NUMBER(TO_CHAR(SYSDATE,'D')) currentDay, TO_NUMBER(TO_CHAR(TO_DATE('17082012','DDMMYYYY'),'D')) targetDay FROM DUAL ) SELECT currentDay, targetDay, currentDate-MOD((currentDay+7)-targetDay,7) FROM params;
This example produces the same result as the first, just a bit easier to read and understand.

Done 26-AUG-2012.