Archive for the ‘pl/sql’ Category
REGEXP_LIKE Behavior
Often, the biggest problem with regular expressions is that those who use them sometimes don’t use them correctly. A great example occurs in the Oracle Database with the REGEXP_LIKE function. For example, some developer use the following to validate whether a string is a number but it only validates whether the first character is a number.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | DECLARE lv_input VARCHAR2(100); BEGIN /* Assign input value. */ lv_input := '&input'; /* Check for numeric string. */ IF REGEXP_LIKE(lv_input,'[[:digit:]]') THEN dbms_output.put_line('It''s a number.'); ELSE dbms_output.put_line('It''s a string.'); END IF; END; / |
When they test numbers it appears to works, it even appears to work when the test string start with number, but it fails with any string that starts with a character. That’s because the REGEXP_LIKE function on line 8 only checks the first character, but the following checks all the characters in the string.
8 | IF REGEXP_LIKE(lv_inputs(i),'[[:digit:]]{'||LENGTH(lv_inputs(i))||'}') THEN |
You can also fix it with the following non-Posix solution:
8 | IF REGEXP_LIKE(lv_input,'[[0-9]]') THEN |
You can add a collection to the program and use it to test single-digit, double-digit, and string with a leading integer. Save the program as test.sql and you can test three conditions with one call.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | DECLARE /* Declare the local collection type. */ TYPE inputs IS TABLE OF VARCHAR2(100); /* Declare a local variable of the collection type. */ lv_inputs INPUTS; BEGIN /* Assign the inputs to the collection variable. */ lv_inputs := inputs('&1','&2','&3'); /* Read through the collection and print whether it's an number or string. */ FOR i IN 1..lv_inputs.COUNT LOOP IF REGEXP_LIKE(lv_inputs(i),'[[:digit:]]{'||LENGTH(lv_inputs(i))||',}') THEN dbms_output.put_line('It''s a number.'); ELSE dbms_output.put_line('It''s a string.'); END IF; END LOOP; END; / |
You can run the test.sql program like this:
SQL> @test.sql 1 12 1a |
It prints:
It's a number. It's a number. It's a string. |
As always, I hope this helps those looking for a solution.
Using CALIBRATE_IO
Using Oracle’s Resource Manager requires you to understand the IO dynamics. The first step requires you to run the CALIBRATE_IO procedure from the DBMS_RESOURCE_MANAGER package.
Oracle provides some great examples about how to use the CALIBRATE_IO procedure of the DBMS_RESOURCE_MANAGER package in the Oracle Database Database PL/SQL Packages and Types Reference. The CALIBRATE_IO procedure returns the best answer when you provide a valid number of files, which you can capture by querying the V$ASM_DISK view.
The following code queries the view and assigns the value to a session level variable:
CLEAR BREAKS CLEAR COLUMNS CLEAR COMPUTES VARIABLE files NUMBER BEGIN SELECT COUNT(DISTINCT name) disks INTO :files FROM v$asm_disk; END; / |
When you have the number of files, you can calibrate the IO with the following anonymous block. The query should always work but just in case the NVL function on line 9 assigns the default number of files.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | DECLARE lv_num_physical_disks BINARY_INTEGER; — v$asm_disk lv_max_latency BINARY_INTEGER := 10; lv_max_iops BINARY_INTEGER; lv_max_mbps BINARY_INTEGER; lv_actual_latency BINARY_INTEGER; BEGIN /* Assign actual files to anonymous block variable. */ lv_num_physical_disks := NVL(:files,2); /* Run the calibrate_io procedure. */ DBMS_RESOURCE_MANAGER.CALIBRATE_IO( num_physical_disks => lv_num_physical_disks , max_latency => lv_max_latency , max_iops => lv_max_iops , max_mbps => lv_max_mbps , actual_latency => lv_actual_latency); END; / |
You can query the results like this:
SELECT max_iops , max_mbps , max_pmbps , latency , num_physical_disks FROM dba_rsrc_io_calibrate; |
It should show results like these:
MAX_IOPS MAX_MBPS MAX_PMBPS LATENCY NUM_PHYSICAL_DISKS
-------- -------- --------- ------- ------------------
8894 443 294 9 18 |
Hope this helps those using the CALIBRATE_IO procedure of the DBMS_RESOURCE_MANAGER package.
Use an object in a query?
Using an Oracle object type’s instance in a query is a powerful capability. Unfortunately, Oracle’s SQL syntax doesn’t make it immediately obvious how to do it. Most get far enough to put it in a runtime view (a subquery in the FROM clause), but then they get errors like this:
SELECT instance.get_type() * ERROR AT line 4: ORA-00904: "INSTANCE"."GET_TYPE": invalid identifier |
The problem is how Oracle treats runtime views, which appears to me as a casting error. Somewhat like the ORDER BY clause irregularity that I noted in July, the trick is complete versus incomplete syntax. The following query fails and generates the foregoing error:
1 2 3 4 | SELECT instance.get_type() AS object_type , instance.to_string() AS object_content FROM (SELECT dependent()AS instance FROM dual); |
If you add a table alias, or name, to the runtime view on line 4, it works fine:
1 2 3 4 | SELECT cte.instance.get_type() AS object_type , cte.instance.to_string() AS object_content FROM (SELECT dependent() AS instance FROM dual) cte; |
That is the trick. You use an alias for the query, which assigns the alias like a table reference. The reference lets you access instance methods in the scope of a query. Different columns in the query’s SELECT-list may return different results from different methods from the same instance of the object type.
You can also raise an exception if you forget the open and close parentheses for a method call to a UDT, which differs from how Oracle treats no argument functions and procedures. That type of error would look like this:
SELECT cte.instance.get_type AS object_type * ERROR AT line 1: ORA-00904: : invalid identifier |
It is an invalid identifier because there’s no public variable get_type, and a method is only found by using the parenthesis and a list of parameters where they’re required.
The object source code is visible by clicking on the expandable label below.
Setup Object Types ↓
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 | DROP TYPE dependent_t; DROP TYPE base_t; SET SERVEROUTPUT ON SIZE UNLIMITED -- Create an object. CREATE OR REPLACE TYPE base_t IS OBJECT ( TYPE VARCHAR2(20) , CONSTRUCTOR FUNCTION base_t RETURN SELF AS RESULT , MEMBER FUNCTION get_type RETURN VARCHAR2 , MEMBER FUNCTION to_string RETURN VARCHAR2) INSTANTIABLE NOT FINAL; / -- Create an object body. CREATE OR REPLACE TYPE BODY base_t IS CONSTRUCTOR FUNCTION base_t RETURN SELF AS RESULT IS BEGIN RETURN; END base_t; MEMBER FUNCTION get_type RETURN VARCHAR2 IS BEGIN RETURN self.TYPE; END; MEMBER FUNCTION to_string RETURN VARCHAR2 IS BEGIN RETURN self.TYPE; END to_string; END; / -- Create a subtype. CREATE OR REPLACE TYPE dependent UNDER base_t ( child VARCHAR2(40) , CONSTRUCTOR FUNCTION dependent RETURN SELF AS RESULT , CONSTRUCTOR FUNCTION dependent ( child VARCHAR2 ) RETURN SELF AS RESULT , OVERRIDING MEMBER FUNCTION get_type RETURN VARCHAR2 , OVERRIDING MEMBER FUNCTION to_string RETURN VARCHAR2) INSTANTIABLE NOT FINAL; / -- Create a subtype body. CREATE OR REPLACE TYPE BODY dependent IS CONSTRUCTOR FUNCTION dependent RETURN SELF AS RESULT IS BEGIN /* Assign subtype name to type. */ self.TYPE := 'DEPENDENT'; RETURN; END dependent; CONSTRUCTOR FUNCTION dependent ( child VARCHAR2 ) RETURN SELF AS RESULT IS lv_dependent DEPENDENT := dependent(); BEGIN /* Assign default constructor to self instance. */ self := lv_dependent; /* Assign parameters to object instance. */ self.TYPE := 'DEPENDENT'; self.child := child; RETURN; END dependent; OVERRIDING MEMBER FUNCTION get_type RETURN VARCHAR2 IS BEGIN RETURN self.TYPE; END; OVERRIDING MEMBER FUNCTION to_string RETURN VARCHAR2 IS BEGIN RETURN self.TYPE; END to_string; END; / |
As always, I hope this helps those solving problems.
Oracle Cleanup a Schema
Back in January 2014, I wrote a script to cleanup an Oracle student schema. It worked well until I started using APEX 4 in my student schema. You create the following 75 objects when you create an APEX 4 schema.
OBJECT TYPE TOTAL ------------ ------- TABLE 17 INDEX 28 SEQUENCE 5 TRIGGER 14 LOB 9 FUNCTION 2 |
Here’s the modified script that ignores the objects created automatically by Oracle APEX when you create a student workspace:
BEGIN FOR i IN (SELECT object_name , object_type , last_ddl_time FROM user_objects WHERE object_name NOT IN ('APEX$_WS_WEBPG_SECTION_HISTORY','APEX$_WS_WEBPG_SECTIONS_T1' ,'APEX$_WS_WEBPG_SECTIONS_PK','APEX$_WS_WEBPG_SECTIONS' ,'APEX$_WS_WEBPG_SECHIST_IDX1','APEX$_WS_TAGS_T1' ,'APEX$_WS_TAGS_PK','APEX$_WS_TAGS_IDX2','APEX$_WS_TAGS_IDX1' ,'APEX$_WS_TAGS','APEX$_WS_ROWS_T1','APEX$_WS_ROWS_PK' ,'APEX$_WS_ROWS_IDX','APEX$_WS_ROWS','APEX$_WS_NOTES_T1' ,'APEX$_WS_NOTES_PK','APEX$_WS_NOTES_IDX2','APEX$_WS_NOTES_IDX1' ,'APEX$_WS_NOTES','APEX$_WS_LINKS_T1','APEX$_WS_LINKS_PK' ,'APEX$_WS_LINKS_IDX2','APEX$_WS_LINKS_IDX1','APEX$_WS_LINKS' ,'APEX$_WS_HISTORY_IDX','APEX$_WS_HISTORY','APEX$_WS_FILES_T1' ,'APEX$_WS_FILES_PK','APEX$_WS_FILES_IDX2','APEX$_WS_FILES_IDX1' ,'APEX$_WS_FILES','APEX$_ACL_T1','APEX$_ACL_PK','APEX$_ACL_IDX1' ,'APEX$_ACL','CUSTOM_AUTH','CUSTOM_HASH','DEPT','EMP' ,'UPDATE_ORDER_TOTAL') AND NOT ((object_name LIKE 'DEMO%' OR object_name LIKE 'INSERT_DEMO%' OR object_name LIKE 'BI_DEMO%') AND object_type IN ('TABLE','INDEX','SEQUENCE','TRIGGER')) AND NOT (object_name LIKE 'SYS_LOB%' AND object_type = 'LOB') AND NOT (object_name LIKE 'SYS_C%' AND object_type = 'INDEX') ORDER BY object_type DESC) LOOP /* Drop types in descending order. */ IF i.object_type = 'TYPE' THEN /* Drop type and force operation because dependencies may exist. Oracle 12c also fails to remove object types with dependents in pluggable databases (at least in release 12.1). Type evolution works in container database schemas. */ EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name||' FORCE'; /* Drop table tables in descending order. */ ELSIF i.object_type = 'TABLE' THEN /* Drop table with cascading constraints to ensure foreign key constraints don't prevent the action. */ EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name||' CASCADE CONSTRAINTS PURGE'; /* Oracle 12c ONLY: Purge the recyclebin to dispose of system-generated sequence values because dropping the table doesn't automatically remove them from the active session. CRITICAL: Remark out the following when working in Oracle Database 11g. */ EXECUTE IMMEDIATE 'PURGE RECYCLEBIN'; ELSIF i.object_type = 'LOB' OR i.object_type = 'INDEX' THEN /* A system generated LOB column or INDEX will cause a failure in a generic drop of a table because it is listed in the cursor but removed by the drop of its table. This NULL block ensures there is no attempt to drop an implicit LOB data type or index because the dropping the table takes care of it. */ NULL; ELSE dbms_output.put_line('DROP '||i.object_type||' '||i.object_name||';'); /* Drop any other objects, like sequences, functions, procedures, and packages. */ EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name; END IF; END LOOP; END; / |
As always, I hope this helps others.
Functions disallow NDS
My students asked if you could embed an OFFSET x ROWS FETCH NEXT y ROWS ONLY clause in a SQL Server T-SQL user-defined function. The answer is no, it isn’t Oracle (yes, you can do that in Oracle Database 12c with an NDS statement). There’s an example in Chapter 2 of my Oracle Database 12c PL/SQL Programming book if you’re interested. I also demonstrate a different approach to SQL Server T-SQL table functions in this older post. However, an attempt to add the clause to a SQL Server T-SQL function, like this:
CREATE FUNCTION studentdb.getBatch (@rows AS INT ,@offset AS INT) RETURNS @output TABLE ( marvel_id INT , avenger_name VARCHAR(30) , first_name VARCHAR(20) , last_name VARCHAR(20)) AS BEGIN /* Insert the results into the table variable. */ INSERT @output SELECT marvel_id , avenger_name , first_name , last_name FROM studentdb.marvel OFFSET (@offset - 1) ROWS FETCH NEXT @rows ROWS ONLY; /* Return the table variable from the function. */ RETURN; END; |
Throws the following errors trying to compile the function:
Msg 102, Level 15, State 1, Procedure getBatch, Line 16 Incorrect syntax near '@offset'. Msg 153, Level 15, State 2, Procedure getBatch, Line 16 Invalid usage of the option NEXT in the FETCH statement. |
If you have a strong background in Oracle and can sort through the dynamic SQL syntax for T-SQL, you might try re-writing the function to use the EXEC SP_EXECUTESQL @var; command. That rewrite that attempts to use NDS (Native Dynamic SQL) would look like this:
CREATE FUNCTION studentdb.getBatch (@rows AS INT ,@offset AS INT) RETURNS @output TABLE ( marvel_id INT , avenger_name VARCHAR(30) , first_name VARCHAR(20) , last_name VARCHAR(20)) AS BEGIN DECLARE /* Declare a variable for a dynamic SQL statement. */ @stmt VARCHAR(400); /* Assign the SQL statement to a variable. */ SET @stmt = N'SELECT marvel_id ' + N', avenger_name ' + N', first_name ' + N', last_name ' + N'FROM studentdb.marvel ' + N'OFFSET ' + (@offset - 1) + N' ' + N'ROWS FETCH NEXT ' + @rows + N' ROWS ONLY;'; BEGIN /* Insert the results into the table variable. */ INSERT @output EXEC sp_executesql @stmt; END; /* Return the table variable from the function. */ RETURN; END; |
Throws the following exception because you can’t use dynamic dispatch inside a T-SQL function:
Msg 443, Level 16, State 14, Procedure getBatch, Line 23 Invalid use of a side-effecting operator 'INSERT EXEC' within a function. |
On the other hand you can rewrite the statement with a BETWEEN operator and it works somewhat like an OFFSET and FETCH operation. That refactored function would be written as follows:
CREATE FUNCTION studentdb.getBatch (@rowa AS INT ,@rowb AS INT) RETURNS @output TABLE ( marvel_id INT , avenger_name VARCHAR(30) , first_name VARCHAR(20) , last_name VARCHAR(20)) AS BEGIN /* Insert the results into the table variable. */ INSERT @output SELECT marvel_id , avenger_name , first_name , last_name FROM studentdb.marvel WHERE marvel_id BETWEEN @rowa AND @rowb; /* Return the table variable from the function. */ RETURN; END; |
It doesn’t raise an exception. You can call the table function like this:
SELECT * FROM getBatch(2,3); |
It returns the two rows for Iron Man and Black Widow. As always, I hope this helps.
If you want to create the test case, here’s the script you need:
SELECT 'Conditionally drop studentdb.marvel table.' AS "Statement"; IF OBJECT_ID('studentdb.marvel','U') IS NOT NULL DROP TABLE studentdb.marvel; SELECT 'Create studentdb.marvel table.' AS "Statement"; CREATE TABLE studentdb.marvel ( marvel_id INT NOT NULL IDENTITY(1,1) CONSTRAINT marvel_pk PRIMARY KEY , avenger_name VARCHAR(30) NOT NULL , first_name VARCHAR(20) NOT NULL , last_name VARCHAR(20) NOT NULL); /* Insert the rows. */ INSERT INTO studentdb.marvel (avenger_name, first_name, last_name) VALUES ('Hulk','Bruce','Banner'); INSERT INTO studentdb.marvel (avenger_name, first_name, last_name) VALUES ('Iron Man','Tony','Stark'); INSERT INTO studentdb.marvel (avenger_name, first_name, last_name) VALUES ('Black Widow','Natasha','Romanoff'); INSERT INTO studentdb.marvel (avenger_name, first_name, last_name) VALUES ('Thor','Thor','Odinsson'); INSERT INTO studentdb.marvel (avenger_name, first_name, last_name) VALUES ('Captain America','Steve','Rogers'); INSERT INTO studentdb.marvel (avenger_name, first_name, last_name) VALUES ('Hawkeye','Clint','Barton'); INSERT INTO studentdb.marvel (avenger_name, first_name, last_name) VALUES ('Winter Soldier','Bucky','Barnes'); INSERT INTO studentdb.marvel (avenger_name, first_name, last_name) VALUES ('Iron Patriot','James','Rhodey'); /* Query the contents of the MARVEL table. */ SELECT * FROM studentdb.marvel; |
Filtering String Dates
A question came up about how to verify dates from a string without throwing a casting error because of a non-conforming date. You can throw a number of exceptions, and I wrote a function to filter bad string formats like the DD-MON-RR or DD-MON-YYYY.
The first one is for a day between 1 and the last day of month, which is:
ORA-01847: day of month must be between 1 and last day of month |
An incorrect string for a month, raises the following error:
ORA-01843: not a valid month |
A date format mask longer than a DD-MON-RR or DD-MON-YYYY raises the following exception:
ORA-01830: date format picture ends before converting entire input string |
The verify_date function checks for non-conforming DD-MON-RR and DD-MON-YYYY date masks, and substitutes a SYSDATE value for a bad date entry:
CREATE OR REPLACE FUNCTION verify_date ( pv_date_in VARCHAR2) RETURN DATE IS /* Local return variable. */ lv_date DATE; BEGIN /* Check for a DD-MON-RR or DD-MON-YYYY string. */ IF REGEXP_LIKE(pv_date_in,'^[0-9]{2,2}-[ADFJMNOS][ACEOPU][BCGLNPRTVY]-([0-9]{2,2}|[0-9]{4,4})$') THEN /* Case statement checks for 28 or 29, 30, or 31 day month. */ CASE /* Valid 31 day month date value. */ WHEN SUBSTR(pv_date_in,4,3) IN ('JAN','MAR','MAY','JUL','AUG','OCT','DEC') AND TO_NUMBER(SUBSTR(pv_date_in,1,2)) BETWEEN 1 AND 31 THEN lv_date := pv_date_in; /* Valid 30 day month date value. */ WHEN SUBSTR(pv_date_in,4,3) IN ('APR','JUN','SEP','NOV') AND TO_NUMBER(SUBSTR(pv_date_in,1,2)) BETWEEN 1 AND 30 THEN lv_date := pv_date_in; /* Valid 28 or 29 day month date value. */ WHEN SUBSTR(pv_date_in,4,3) = 'FEB' THEN /* Verify 2-digit or 4-digit year. */ IF (LENGTH(pv_date_in) = 9 AND MOD(TO_NUMBER(SUBSTR(pv_date_in,8,2)) + 2000,4) = 0 OR LENGTH(pv_date_in) = 11 AND MOD(TO_NUMBER(SUBSTR(pv_date_in,8,4)),4) = 0) AND TO_NUMBER(SUBSTR(pv_date_in,1,2)) BETWEEN 1 AND 29 THEN lv_date := pv_date_in; ELSE /* Not a leap year. */ IF TO_NUMBER(SUBSTR(pv_date_in,1,2)) BETWEEN 1 AND 28 THEN lv_date := pv_date_in; ELSE lv_date := SYSDATE; END IF; END IF; ELSE /* Assign a default date. */ lv_date := SYSDATE; END CASE; ELSE /* Assign a default date. */ lv_date := SYSDATE; END IF; /* Return date. */ RETURN lv_date; END; / |
You can check valid dates with a DD-MON-RR format:
SELECT verify_date('28-FEB-10') AS "Non-Leap Year" , verify_date('29-FEB-12') AS "Leap Year" , verify_date('31-MAR-14') AS "31-Day Year" , verify_date('30-APR-14') AS "30-Day Year" FROM dual; |
You can check valid dates with a DD-MON-YYYY format:
SELECT verify_date('28-FEB-2010') AS "Non-Leap Year" , verify_date('29-FEB-2012') AS "Leap Year" , verify_date('31-MAR-2014') AS "31-Day Year" , verify_date('30-APR-2014') AS "30-Day Year" FROM dual; |
They both return:
Non-Leap Leap YEAR 31-DAY YEAR 30-DAY YEAR ----------- --------- ----------- ----------- 28-FEB-10 29-FEB-12 31-MAR-14 30-APR-14 |
You can check badly formatted dates with the following query:
SELECT verify_date('28-FEB-2010') AS "Non-Leap Year" , verify_date('29-FEB-2012') AS "Leap Year" , verify_date('31-MAR-2014') AS "31-Day Year" , verify_date('30-APR-2014') AS "30-Day Year" FROM dual; |
You can screen for an alphanumeric string with the following expression:
SELECT 'Valid alphanumeric string literal' AS "Statement" FROM dual WHERE REGEXP_LIKE('Some Mythical String $200','([:alnum:]|[:punct:]|[:space:])*'); |
You can screen for a numeric literal as a string with the following expression:
SELECT 'Valid numeric literal' AS "Statement" FROM dual WHERE REGEXP_LIKE('123.00','([:digit:]|[:punct:])'); |
As always, I hope this helps those who need this type of solution.
Querying an Object Type
I demonstrated a number of SQL approaches to reading object types in Appendix B of the Oracle Database 12c PL/SQL Programming book. For example, the easiest one to construct and return the results from a TO_STRING member function uses the TREAT function:
SELECT TREAT(base_t() AS base_t).to_string() AS "Text" FROM dual; |
However, it seems that I could have provided one more. Here’s an example of how you can test the construction of an object type and how you can return its attributes with a query. It’s important to note that there’s a natural problem with this syntax when you increment a sequence inside the object type. The problem is that it double increments the counter for the sequence.
SELECT * FROM TABLE(SELECT CAST(COLLECT(base_t()) AS base_t_tab) FROM dual); |
The syntax for the COLLECT function requires that you put it inside a SELECT-list. Then, the CAST function converts a single instance of the BASE_T object type to a one element BASE_T_TAB collection. Finally, the TABLE function returns a single row from the BASE_T_TAB collection.
You can find a more complete article covering column substitutability and object types and subtypes on the ToadWorld site. I think it helps clear up how you can effectively write PL/SQL types and subtypes for persistent object type columns.
Finding Direct Indexes
If you’re not using Toad DBA Suite, it’s sometimes hard to find solutions. Somebody wanted to know how to find indexes that aren’t indirect. Indirect indexes are those created for a primary key because a primary key column or set of columns are both not null and uniquely constrained. Likewise, you create a unique index when you can create a unique constraint. You can’t drop a unique index for a primary key without dropping the primary key or unique constraint that indirectly created it.
The following query returns indexes with one or more columns that are created by a CREATE INDEX statement on a target table. It excludes unique indexes created by a primary key constraint, and it returns the relative position of columns in an index:
COLUMN sequence_name FORMAT A22 HEADING "Sequence Name" COLUMN column_position FORMAT 999 HEADING "Column|Position" COLUMN column_name FORMAT A22 HEADING "Column|Name" SELECT uin.index_name , uic.column_position , uic.column_name FROM user_indexes uin INNER JOIN user_ind_columns uic ON uin.index_name = uic.index_name AND uin.table_name = uic.table_name WHERE uin.table_name = UPPER('&&table_name') AND NOT uin.index_name IN (SELECT constraint_name FROM user_constraints WHERE table_name = UPPER('&&table_name')) ORDER BY uin.index_name , uic.column_position; |
It can be rewritten into a function, which can then drop indexes based on a table name:
CREATE OR REPLACE FUNCTION drop_indexes_on ( pv_table_name VARCHAR2 ) RETURN NUMBER IS /* A return value. */ lv_return NUMBER := 0; /* A query to return only directly created indexes. */ CURSOR find_indexes_on ( cv_table_name VARCHAR2 ) IS SELECT DISTINCT ui.index_name FROM user_indexes ui INNER JOIN user_ind_columns uic ON ui.index_name = uic.index_name AND ui.table_name = uic.table_name WHERE ui.table_name = UPPER(cv_table_name) AND NOT ui.index_name IN (SELECT constraint_name FROM user_constraints WHERE table_name = UPPER(cv_table_name)); /* Declare function autonomous. */ PRAGMA AUTONOMOUS_TRANSACTION; BEGIN /* Drop the indexes on a table. */ FOR i IN find_indexes_on(pv_table_name) LOOP EXECUTE IMMEDIATE 'DROP INDEX '||i.index_name; lv_return := 1; END LOOP; RETURN lv_return; END drop_indexes_on; / |
You can call the drop_on_indexes_on function like this:
SELECT drop_indexes_on(UPPER('address_lab')) FROM dual; |
Hope this helps those who need to work with dropping indexes.
Parsing DBMS_OUTPUT
Testing with DBMS_OUTPUT.PUT_LINE is always a bit of a problem when you have strings longer than 80 characters in length, which occurs more frequently with Oracle Database 12c. An example of managing output occurs when you want to print a string with embedded line breaks. My solution is the following parse_rows procedure:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 | CREATE OR REPLACE PROCEDURE parse_rows ( pv_text VARCHAR2 ) IS /* Declare parsing indexes. */ lv_start NUMBER := 1; lv_end NUMBER := 1; lv_length NUMBER; BEGIN /* Assign an end value based on parsing line return or length. */ IF INSTR(pv_text,CHR(10),lv_start) = 0 THEN lv_end := LENGTH(pv_text) + 1; ELSE lv_end := INSTR(pv_text,CHR(10),lv_start); END IF; /* Assign a length value to the parsed string. */ lv_length := lv_end - lv_start; /* Print first line. */ DBMS_OUTPUT.put_line(SUBSTR(pv_text,lv_start,lv_length)); /* Print the rows of a multiple line string. */ WHILE (lv_end < LENGTH(pv_text)) LOOP /* Assign a new start value. */ lv_start := lv_end + 1; /* Assign a new end value. */ IF INSTR(pv_text,CHR(10),lv_start + 1) = 0 THEN lv_end := LENGTH(pv_text) + 1; ELSE lv_end := INSTR(pv_text,CHR(10),lv_start + 1); END IF; /* Assign a new length. */ lv_length := lv_end - lv_start; /* Print the individual rows. */ DBMS_OUTPUT.put_line(SUBSTR(pv_text,lv_start,lv_length)); END LOOP; END; / |
Here’s the example of a test program for the procedure:
1 2 3 4 5 6 7 | DECLARE /* Declare text. */ lv_text VARCHAR2(200) := 'This is too much'||CHR(10)||'information'||CHR(10)||'on one line.'; BEGIN parse_rows(lv_text); END; / |
It prints the following:
1 2 3 | This is too much information on one line. |
Hope this benefits those looking for quick solution.
Oracle 12c VARCHAR2?
The Oracle Database 12c documentation says you can set the maximum size of a VARCHAR2 to 32,767 bytes. That’s true except when you’re trying to map a large Java string to a VARCHAR2. It fails when the physical size of the Java string is greater than 4,000 bytes with an ORA-01002 or fetch out of sequence error.
SELECT read_text_file('C:\Data\loader\Hobbit1.txt')
*
ERROR at line 1:
ORA-24345: A Truncation or null fetch error occurred
ERROR:
ORA-01002: fetch out of sequence |
You need to grant privileges before you can test this code. You can grant privileges by connecting as the SYS user of a CDB (or non-multitenant database) or as the ADMIN user of a PDB with the AS SYSDBA clause. Then, you run the following command to grant external file access to the JVM inside Oracle Database 12c:
BEGIN DBMS_JAVA.GRANT_PERMISSION('IMPORTER' ,'SYS:java.io.FilePermission' ,'C:\Data\Loader\Hobbit1.txt' ,'read'); END; / |
The IMPORTER is a PDB user name, or a non-multitenant database user name. Please note that permissions must be granted on each physical file.
Here’s the code that raises the error when the external file is greater than 4,000 bytes:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 | CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "ReadFile" AS // Java library imports. import java.io.File; import java.io.BufferedReader; import java.io.FileNotFoundException; import java.io.IOException; import java.io.FileReader; import java.security.AccessControlException; // Class definition. public class ReadFile { // Define class variables. private static File file; private static FileReader inTextFile; private static BufferedReader inTextReader; private static StringBuffer output = new StringBuffer(); private static String outLine, outText; // Define readText() method. public static String readText(String fromFile) throws AccessControlException, IOException { // Read file. try { // Initialize File. file = new File(fromFile); // Check for valid file. if (file.exists()) { // Assign file to a stream. inTextFile = new FileReader(file); inTextReader = new BufferedReader(inTextFile); // Read character-by-character. while ((outLine = inTextReader.readLine()) != null) { output.append(outLine + "\n"); } // Assing the StringBuffer to a String. outText = output.toString(); // Close File. inTextFile.close(); } else { outText = new String("Empty"); }} catch (IOException e) { outText = new String(""); return outText; } return outText; }} / |
The PL/SQL wrapper should look like this:
1 2 3 4 5 | CREATE OR REPLACE FUNCTION read_text_file (from_file VARCHAR2) RETURN VARCHAR2 IS LANGUAGE JAVA NAME 'ReadFile.readText(java.lang.String) return java.lang.String'; / |
Then, you can query it like so:
SELECT read_text_file('C:\Data\loader\Hobbit1.txt') FROM dual; |
You can avoid the error by returning the Java file size as a CLOB data type with the following Java source code.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 | CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "ReadFile" AS // Java library imports. import java.io.File; import java.io.BufferedReader; import java.io.FileNotFoundException; import java.io.IOException; import java.io.FileReader; import java.security.AccessControlException; import java.sql.*; import oracle.sql.driver.*; import oracle.sql.*; // Class definition. public class ReadFile { // Define class variables. private static int i; private static File file; private static FileReader inTextFile; private static BufferedReader inTextReader; private static StringBuffer output = new StringBuffer(); private static String outLine, outText; private static CLOB outCLOB; // Define readText() method. public static oracle.sql.CLOB readText(String fromFile) throws AccessControlException, IOException, SQLException { // Read file. try { // Initialize File. file = new File(fromFile); // Check for valid file. if (file.exists()) { // Assign file to a stream. inTextFile = new FileReader(file); inTextReader = new BufferedReader(inTextFile); // Read character-by-character. while ((outLine = inTextReader.readLine()) != null) { output.append(outLine + "\n"); } // Assing the StringBuffer to a String. outText = output.toString(); // Declare an Oracle connection. Connection conn = DriverManager.getConnection("jdbc:default:connection:"); // Transfer the String to CLOB. outCLOB = CLOB.createTemporary((oracle.jdbc.OracleConnectionWrapper) conn, true, CLOB.DURATION_SESSION); i = outCLOB.setString(1,outText); // Close File. inTextFile.close(); } else { i = outCLOB.setString(1,"Empty"); }} catch (IOException e) { i = outCLOB.setString(1,""); return outCLOB; } return outCLOB; }} / |
The wrapper changes to return a CLOB and map a java.sql.CLOB as the return type of the Java library. This command works:
1 2 3 4 5 | CREATE OR REPLACE FUNCTION read_clob_file (from_file VARCHAR2) RETURN CLOB IS LANGUAGE JAVA NAME 'ReadFile.readText(java.lang.String) return oracle.sql.CLOB'; / |
You now query the PL/SQL wrapper with this type of effective SQL*Plus command-line command:
COLUMN atext FORMAT A60 HEADING "Text" COLUMN asize FORMAT 99,999 HEADING "Size" SELECT read_clob_file('C:\Data\loader\Hobbit1.txt') AS AText , LENGTH(read_clob_file('C:\Data\loader\Hobbit1.txt')) AS ASize FROM dual; |
As always, I hope this helps somebody.
Subscribe via RSS