Ask Anantha

 If you have a data with Comma, or any other character delimited use the following query to convert it to individual rows. -- Created on 03/07/2024 by ANANTHAN  DECLARE   -- Local variables here   CURSOR c1 IS WITH rws AS(     SELECT 'one,two,three' str     FROM   dual)     SELECT regexp_substr(str, '[^,]+', 1, LEVEL) split_val     FROM   rws     CONNECT BY LEVEL <= length(str) - length(REPLACE(str, ',')) + 1; BEGIN   FOR m1 IN c1 LOOP     dbms_output.put_line(m1.split_val);   END LOOP; END; Output: one two three

Prior to Oracle 11g, we have used PLS_INTEGER data type in PL/SQL programs. In 11g, a new data type SIMPLE_INTEGER has been introduced. It is a sub-type of PLS_INTEGER data type and has the same range as PLS_INTEGER. The basic difference between the two is that SIMPLE_INTEGER is always NOT NULL . When the value of the declared variable is never going to be null then we can declare it with SIMPLE_INTEGER data type. Another major difference is that it never gives numeric overflow error like its parent data type instead it wraps around without giving any error. When we don’t have to worry about null checking and overflow errors, SIMPLE_INTEGER data type is the best to use. Posted by decipherinfosys , More information check Oracle Documentation

UTL_FILE procedure has been enhanced in Oracle 9i and since then it provides a procedure fgetattr to return file size. PL/SQL Evangelist Steven Feuerstein has come up with this function that returns file length. CREATE OR REPLACE FUNCTION flength (    location_in   IN   VARCHAR2,    file_in       IN   VARCHAR2 )    RETURN PLS_INTEGER IS     TYPE fgetattr_t IS RECORD (       fexists       BOOLEAN,       file_length   PLS_INTEGER,       block_size    PLS_INTEGER    );    fgetattr_rec   fgetattr_t; BEGIN    UTL_FILE.fgetattr (       location         => location_in,       filename         => file_in,       fexists          => fgetattr_rec.fexi...

Question: How to find any value in any column in a schema? I have been searching for this answer for long, and stumbled upon a beautiful response in Oracle Forums and tried the block and stunning to find the output much easily. This saved my day as I was searching for occurence of a value in entire schema. Thanks for pollywog for the posting. I will reproduce the anonymous block here for my readers: declare aCount pls_integer; begin for c in (select table_name, column_name  from all_tab_columns  where owner = 'OWNER'  and data_type = 'DATATYPE') loop execute immediate 'select count(*) from '||c.table_name||' where '||c.column_name||' = ''value_to_find'' ' into aCount; if aCount > 0 then dbms_output.put_line('found value_to_find in table '||c.table_name||', column '||c.column_name); end if; end loop; end; Just modify the following in the block: 1. OWNER (The schema in which find is requ...

Article and Script Courtesy :  Niels Teusink The Oracle  wrap  utility can be used to obfuscate PL/SQL code, to ensure it can't be easily read. Pete Finnigan described ( pdf ) the wrapping process for Oracle 9g, but for 10g and 11g it still remains a bit of a mystery. I decided to release my Python unwrapping utility (supports 10g and 11g). The unwrapping steps for 10g are nicely described in the  Oracle Hacker's Handbook , but the actual substitution table needed to decode the package is omitted. Nobody (as far as I know) has published it. A lot of people seem to know how to do it though, there is even an  online unwrapper  available (and I'm sure everyone seriously involved in Oracle security knows how to do it). A Russian-made closed source tool is also available, but tends to upset virus scanners. So to save everyone a couple of hours of figuring it out, here it is:  unwrap.py It's easy to use (I've used the wrapped procedure from  th...

Today I was answering one of the queries, this question seriously made my interest. The question was "I have 15 million records in my table. What do you suggest a best method for unloading them from Oracle table to a flat file". Seriously till that time I had never done this. The need had not come. I clearly know two ways of doing this: Using SQL Plus and SPOOL command Using UTL_FILE built-in package UTL_FILE vs SPOOL I know that SPOOL command will be faster in execution than UTL_FILE package , but also I reminded myself that this may not be the case always. SPOOL commands operates in the client machine and depending upon the network traffic it might take time for the SPOOL operation than UTL_FILE.  SO I ADVICE TO TEST BOTH OPTIONS IN YOUR ENVIRONMENT BEFORE FINALISING ON ONE OF THE ABOVE. I will soon publish routines for this purpose. Your patience is appreciated. More resources for your inquisitive mind A little surfing did a wonderful job in putting this article up. In ask...

I chose to write on this topic in order to increase the readability of programs and to maintain a standard way of validating values from within PL/SQL blocks, whether be it Anonymous blocks or Stored Procedures or Stored Functions or Packages. There has been always need for writing efficient piece of code before delivering the final one. Validating NULL Values NVL Function This function is boon to programmers. I always chose to use this function because of its simplicity. In Oracle one must always remember that while validating with a NULL value the result you are going to get is also a NULL. So in order to avoid this it is better to use NVL() function. Syntax: NVL(variable1, variable2) Example: SELECT NVL(NULL, 'This is the output') null_test FROM dual; Result: null_test This is the output Both the parameters are required for NVL function to perform. You can use this function in WHERE clause so that you are not going to omit any NULL values from your query. NVL2 Function NVL2 ...

Q: How to enter a single quotation mark in Oracle? Ans: Although this may be a undervalued question, I got many a search for my blog with this question. This is where I wanted to address this question elaborately or rather in multiple ways. Method 1 The most simple and most used way is to use a single quotation mark with two single quotation marks in both sides. SELECT 'test single quote''' from dual; The output of the above statement would be: test single quote' Simply stating you require an additional single quote character to print a single quote character. That is if you put two single quote characters Oracle will print one. The first one acts like an escape character. This is the simplest way to print single quotation marks in Oracle. But it will get complex when you have to print a set of quotation marks instead of just one. In this situation the following method works fine. But it requires some more typing labour. Method 2 I like this method personally becaus...

An internal exception is raised implicitly whenever your PL/SQL program violates an Oracle rule or exceeds a system-dependent limit. Every Oracle error has a number, but exceptions must be handled by name. So, PL/SQL predefines some common Oracle errors as exceptions. For example, PL/SQL raises the predefined exception NO_DATA_FOUND if a SELECT INTO statement returns no rows. To handle other Oracle errors, you can use the OTHERS handler. The functions SQLCODE and SQLERRM are especially useful in the OTHERS handler because they return the Oracle error code and message text. Alternatively, you can use the pragma EXCEPTION_INIT to associate exception names with Oracle error codes. PL/SQL declares predefined exceptions globally in package STANDARD , which defines the PL/SQL environment. So, you need not declare them yourself. You can write handlers for predefined exceptions using the names shown in the list below. Also shown are the corresponding Oracle error codes and S...

If your DML statement encounters an exception, it immediately rolls back any changes already made by that statement, and propagates an exception out to the calling block. Sometimes that's just what you want. Sometimes, however, you'd like to continue past such errors, and apply your DML logic to as many rows as possible. DBMS_ERRLOG, a package introduced in Oracle10g Release 2 , allows you to do precisely that. Here is a quick review of the way this package works.  First you need to create an error log table where the errors will be inserted, and while issuing any DML statements use a clause newly introduced in 10g, LOG ERRORS. 1. Create an error log table for the table against which you will execute DML statements: BEGIN   DBMS_ERRLOG.create_error_log (dml_table_name => 'EMP'); END; Oracle then creates a table named ERR$_EMP that contains error-related columns as well as VARCHAR2 columns for each of your table's columns (where it...

In release 8.1, a new SQL function, TO_LOB, copies data from a LONG column in a table to a LOB column. The datatype of the LONG and LOB must correspond for a successful copy. For example, LONG RAW data must be copied to BLOB data, and LONG data must be copied to CLOB data. In the next example we show how to migrate a table with one LONG to a CLOB datatype. Create the LOB Tablespace CREATE TABLESPACE lob1 DATAFILE '/lh4/lob1.dbf' SIZE 2048064K REUSE EXTENT MANAGEMENT LOCAL UNIFORM SIZE 50M PERMANENT ONLINE; Disable temporarily all Foreign Keys set feed off; spool gen_dis_cons.sql; SELECT 'ALTER TABLE ' table_name ' DISABLE CONSTRAINT ' constraint_name ';' FROM user_constraints WHERE UPPER(constraint_name) like 'FK_%' / spool off; set feed on; @gen_dis_cons.sql; Convert LONG to LOB in temporary Table Create a temporary table with converted BLOB field. CREATE TABLE lob_tmp TABLESPACE tab AS SELECT id, TO_LOB(bdata) bdata FROM document; Drop and Rena...

Oracle offers several possibilities to process file from within PL/SQL. The most used package is UTL_FILE, but with the disadvantage that the read-buffer is limited to 1023 bytes. If you want to read huge chunks of files you can use the DBMS_LOB package, even for the processing of plain ASCII files. There are two solutions to read a file with DBMS_LOB The file is treaded as a large binary object LOB. The whole file is read and saved in a table column of the data type LOB and then processed. The file is read and processed directly from the filesystem location. This Tip shows exactly this case. Example: Suppose we want to read a big file word by word directly from PL/SQL without saving the whole file in a table column. The words in the file are separated with a blank. For simplicity we assume, that there is exactly one blank between the words and the file is a stream with a newline at the end of the file. First we have to create an ORACLE directory as the schema owner. Do not ad...

Test Your PL/SQL Knowledge This puzzler has come from Steven Feuerstein for the month of February 2008. So I thought to reproduce the puzzler with its answer: The employees table in the production Oracle Database 10g Release 2 instance of the MassiveGlobalCorp company contains 2.5 million rows.   Below are three different blocks of code, each of which fetch all rows from this table and then "do stuff" with each fetched record. Which will run much slower than the other two, and why?   a . DECLARE   CURSOR employees_cur IS SELECT * FROM employees; BEGIN     FOR employee_rec IN employees_cur LOOP       do_stuff ( employee_rec );    END LOOP ; END;   b . DECLARE   CURSOR employees_cur IS SELECT * FROM employees;    l_employee    employees%ROWTYPE ; BEGIN    OPEN employees_cur ;    LOOP       FETCH employees_cur...

This tip comes from Zlatko Sirotic , Software Developer at Istra Informaticki Inzenjering d.o.o., in Pula , Croatia . Suppose you've got a function that is based on a dynamically generated query that returns a ref cursor variable. Now suppose you want to use this ref cursor variable in your procedure, but you don't know the record structure. So how do you make a "FETCH l_ref_cur INTO record_variable " when you don't know the record variable structure. Because ref cursors do not (directly) support description, the solution is quite complicated and requires that the function (or package) returns not only a ref cursor variable but a (dynamically) generated query, too. I am going to use "a good old" DBMS_SQL package and its PARSE and DESCRIBE_COLUMNS procedures in order to make an unknown record variable. 1. Make the "generic" package. First I am going to make a " dyn_fetch " package in which the " describe_columns ...