How to dynamically create and immediately use a sequence in PL/SQL code

I was recently approached by a developer who showed me a piece of code that raised ORA-06550 exception for “no apparent reason”. Here is a simplified version of that code:

DECLARE
    v_cnt NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_cnt
    FROM user_sequences
    WHERE sequence_name='SEQ';

    IF v_cnt=0 THEN
       EXECUTE IMMEDIATE 'CREATE SEQUENCE seq START WITH 1 INCREMENT BY 1';
    END IF;

    DBMS_OUTPUT.PUT_LINE(seq.NEXTVAL);
END;

The logic behind this PL/SQL block is quite apparent: check if sequence “SEQ” exists; create it if it does not exist, and then (when it definitely exists) call its NEXTVAL attribute.

This code will only work if the sequence already exists before you run this block. If it does not exist, the code will not compile – it will not be executed at all, because the line “DBMS_OUTPUT.PUT_LINE(seq.NEXTVAL);” – references seq object that does not yet exist.

The fix is very simple – if you create the object dynamically, you can only reference it in dynamic SQL (or PL/SQL) in the same block:

DECLARE
    v_cnt NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_cnt
    FROM user_sequences
    WHERE sequence_name='SEQ';

    IF v_cnt=0 THEN
       EXECUTE IMMEDIATE 'CREATE SEQUENCE seq START WITH 1 INCREMENT BY 1';
    END IF;

    EXECUTE IMMEDIATE 'BEGIN DBMS_OUTPUT.PUT_LINE(seq.NEXTVAL); END;';
END;

Note, that our dynamic PL/SQL is a block itself as it runs in its own context. If you try the following line, it will throw an exception:

EXECUTE IMMEDIATE 'DBMS_OUTPUT.PUT_LINE(seq.NEXTVAL);';

ORA-00900: invalid SQL statement ORA-06512: at line 10 ORA-06512: at "SYS.DBMS_SQL", line 1721

A lesson from this mistake is very simple and important so we shall repeat the rule:

If you create an object dynamically, you can only reference this object in dynamic SQL (or PL/SQL) in the same block.

This applies to all kinds of objects. For example, if you create a table in your procedure, you may only select from this table in dynamic SQL. If you dynamically add a column to a table, you can only update it in dynamic SQL, etc.

***

If you find this post useful, please press the LIKE button and subscribe.

My Oracle Group on Facebook:

Also, you may want to join my Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

Suggested Reading:

Would you like to read about many more tricks and puzzles? For more clever tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds”.

Using SQL%ROWCOUNT with Dynamic PL/SQL

Using SQL%ROWCOUNT attribute when executing static or dynamic DML statement is very handy. Situation changes dramatically when you check this attribute after executing dynamic or static PL/SQL command:

Static PL/SQL

BEGIN
    NULL;
    dbms_output.put_line('Rowcount=' || SQL%ROWCOUNT);
END;
/

Result:

Rowcount=

Dynamic PL/SQL

BEGIN
    EXECUTE IMMEDIATE 'BEGIN NULL; END;';
    dbms_output.put_line('Rowcount=' || SQL%ROWCOUNT);
END;
/

Result:

Rowcount=1

Static PL/SQL “has” NULL as SQL%ROWCOUNT value while Dynamic PL/SQL – always “produces” 1, even if that dynamic PL/SQL does affect certain number of records in a table:

Dynamic SQL:

BEGIN
    EXECUTE IMMEDIATE 'DELETE FROM emp WHERE ROWNUM<=2';
    dbms_output.put_line('Rowcount=' || SQL%ROWCOUNT);
    ROLLBACK;
END;
/

Result:

Rowcount=2

Same command in Dynamic PL/SQL:

BEGIN
    EXECUTE IMMEDIATE 'BEGIN DELETE FROM emp WHERE ROWNUM<=2; END;';
    dbms_output.put_line('Rowcount=' || SQL%ROWCOUNT);
    ROLLBACK;
END;
/

Result:

Rowcount=1

Sometimes, we have to use dynamic PL/SQL so getting correct number of affected rows may be critical. Here is a simple but effective solution:

DECLARE
    v_cnt NUMBER;
BEGIN
    EXECUTE IMMEDIATE 'BEGIN 
                           DELETE FROM emp WHERE ROWNUM<=2; 
                           :0:=SQL%ROWCOUNT; 
                       END;'  USING OUT v_cnt;
    dbms_output.put_line('Rowcount=' || v_cnt);
    ROLLBACK;
END;
/

Result:

Rowcount=2

We use bind variable in the OUT mode to get the result of STATIC SQL inside of

DYNAMIC PL/SQL.

 

My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

Would you like to read about many more tricks and puzzles?

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds”.

How to Dynamically Generate SELECT Statement with all Table Columns Excluding a Given List

Problem: Dynamically Generate SELECT Statement with all Table Columns Excluding a Given List

Level: Intermediate/Advanced

Let say, we need to dynamically generate a SELECT statement that lists all columns from emp table except for hiredate and comm.

SQL Solution:

SELECT 'SELECT ' || LOWER(LISTAGG(column_name,', ') WITHIN GROUP (ORDER BY column_id)) || ' FROM ' || table_name AS SQL
FROM user_tab_columns
WHERE table_name='EMP'
  AND column_name NOT IN ('COMM', 'HIREDATE')
GROUP BY table_name
/

--Result:
SQL
------------------------------------------------------
SELECT empno, ename, job, mgr, sal, deptno FROM EMP

You can also do it in a number of different ways in PL/SQL.

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

How to DELETE all records from all tables in a schema?

This is rather an exercise than a practical task. Anyway, the approach presented below might be helpful in some practical situations.

Problem Level: Beginner/Intermediate

Step 1: Disable all foreign key constraints.

DECLARE
  CURSOR c IS
  SELECT table_name, constraint_name
  FROM user_constraints
  WHERE constraint_type='R'
   AND status='ENABLED';
BEGIN
  FOR v IN c LOOP
     EXECUTE IMMEDIATE 'ALTER TABLE ' || v.table_name || ' DISABLE CONSTRAINT ' || v.constraint_name;
  END LOOP;
END;
/

Step 2: DELETE from all tables

DECLARE
  CURSOR c IS
  SELECT table_name
  FROM user_tables;
BEGIN
  FOR v IN c LOOP
     EXECUTE IMMEDIATE 'DELETE FROM ' || v.table_name;
  END LOOP;
END;
/

Step 3: COMMIT or ROLLBACK

ROLLBACK; --you can commit here instead if needed

STEP 4: Enable all FOREIGN KEY Constraints

DECLARE
  CURSOR c IS
  SELECT table_name, constraint_name
  FROM user_constraints
  WHERE constraint_type='R'
   AND status='DISABLED';
BEGIN
  FOR v IN c LOOP
     EXECUTE IMMEDIATE 'ALTER TABLE ' || v.table_name || ' ENABLE CONSTRAINT ' || v.constraint_name;
  END LOOP;
END;
/

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

How to create a report showing the number of records in each table of a given schema?

How to create a report showing the number of records in each table of a given schema?
Well, if you have just updated statistics for the entire schema, you can simply query a data dictionary table. We will not consider such case. Let’s assume that statistics is inaccurate, so we need a SQL or a PL/SQL solution.

1) PL/SQL solution
There are many approaches to procedural solution, for ex:
1. Loop over the cursor based on “SELECT table_name FROM user_tables”
2. Count number of rows using “EXECUTE IMMEDIATE” statement.
3. Use dbms_output package to produce the output or populate a collection and select from it to return a cursor to the caller.

2) SQL*Plus script generation
You can write a SQL script that will generate another script that counts number of rows and combines them all together with UNION ALL statement.

Options 1 and 2 have been around for years and are not of any significant interest anymore as Oracle now supports XML with many interesting applications – see the next option for details.

3) Update schema statistics and query user_tables view

SELECT table_name, num_rows 
FROM user_tables

This could be a time consuming process depending on the size of existing tables and number of indexes.

4) Use dbms_xmlgen.getxmltype
dbms_xmlgen package allows to dynamically create and execute numerous SELECT statements and parse the result XML to extract necessary information. Here is the most elegant solution to the problem:

col "Records" for a10

SELECT table_name,                  
       dbms_xmlgen.getxmltype('SELECT COUNT(1) cnt from '|| table_name).extract('/ROWSET/ROW/CNT/text()').getstringval() "Records"
FROM user_tables;

TABLE_NAME                     Records
------------------------------ ---------
DEPT                           4
EMP                            14
BONUS                          0
SALGRADE                       5

Special Note: Sometimes, if your schema has huge tables, this approach may fail if the database does not have enough memory resources available or allocated to process the query. In this case, we may suggest using one of the approaches mentioned above (1-3).

Suggested further reading:

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions. The book is also available on Amazon and in all major book stores.

My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

How to safely grant ALTER SYSTEM KILL SESSION “privilege” to non-DBA users

The title of the post can be a bit misleading as there is no such Oracle privilege “ALTER SYSTEM KILL SESSION”, and there is just “ALTER SESSION” privilege. Nevertheless, it would be nice to have something similar.

Very often developers want to have ability to kill their own sessions, i.e. sessions started by their personal database users. Apparently it is not safe to let a non-DBA user to have a right to execute the “ALTER SYSTEM” command, so what can be done?

A sys user may create a stored procedure (sp_kill_dev_session) that will only allow killing sessions started by a given user or a set of given users. For example, we can check that the session was started by SCOTT and only in that case allow it to be killed.

CREATE OR REPLACE PROCEDURE sys.sp_kill_dev_session(p_sid NUMBER, p_serial NUMBER)
AS
    v_user VARCHAR2(30);
BEGIN
    SELECT MAX(username)
    INTO v_user
    FROM v$session
    WHERE sid = p_sid
      AND serial# = p_serial;

    IF v_user IN ('SCOTT') THEN --the list can be extended
         EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || p_sid || ',' || p_serial || '''';
    ELSIF v_user IS NULL THEN
         RAISE_APPLICATION_ERROR(-20001,'Session has Expired or Invalid sid/serial Arguments Passed');
    ELSE
         RAISE_APPLICATION_ERROR(-20002,'Unauthorized Attempt to Kill a Non-Dev Session has been Blocked.');
    END IF;
END sp_kill_dev_session;
/

Procedure created.

Now, all we need to do is to grant EXECUTE privilege to SCOTT:

SQL> GRANT EXECUTE ON sp_kill_dev_session TO scott;

Grant succeeded.

Here is how scott can call the procedure from SQL*PLUS:

SQL> exec sys.sp_kill_dev_session(14, 26043)
BEGIN sys.sp_kill_dev_session(14, 26043); END;

*
ERROR at line 1:
ORA-20001: Session has Expired or Invalid sid/serial Arguments Passed
ORA-06512: at "SYS.SP_KILL_DEV_SESSION", line 14
ORA-06512: at line 1

SQL> exec sys.sp_kill_dev_session(14, 26043)

PL/SQL procedure successfully completed.

SQL> exec sys.sp_kill_dev_session(18,19218)
BEGIN sys.sp_kill_dev_session(18,19218); END;

*
ERROR at line 1:
ORA-20002: Unauthorized Attempt to Kill a Non-Dev Session has been Blocked.
ORA-06512: at "SYS.SP_KILL_DEV_SESSION", line 16
ORA-06512: at line 1

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

How to bind IN and OUT parameters in PL/SQL

Suppose that you need to execute dynamic PL/SQL, a stored procedure, with one in and one out parameter:

CREATE OR REPLACE PROCEDURE scott.get_emp_count(p_deptno IN NUMBER, p_count OUT NUMBER)
AS
BEGIN
    SELECT COUNT(*) INTO p_count
    FROM emp
    WHERE deptno=p_deptno;
END get_emp_count;

Now we want to call it in SQL*Plus as follows:

DECLARE
   v_sql VARCHAR2(2000);
   v_count NUMBER;
   v_deptno NUMBER:=10;
BEGIN
   v_sql:='CALL get_emp_count(:0, :1)';
   EXECUTE IMMEDIATE v_sql USING v_deptno, v_count;

   DBMS_OUTPUT.PUT_LINE(v_count);
END;
/
*
ERROR at line 1:
ORA-06536: IN bind variable bound to an OUT position
ORA-06512: at line 7

To make it work we need to use OUT keyword before v_count variable:

DECLARE
   v_sql VARCHAR2(2000);
   v_count NUMBER;
   v_deptno NUMBER:=10;
BEGIN
   v_sql:='CALL get_emp_count(:0, :1)';
   EXECUTE IMMEDIATE v_sql USING v_deptno, OUT v_count;

   DBMS_OUTPUT.PUT_LINE(v_count);
END;
/
3

PL/SQL procedure successfully completed.

Bind parameter mode is similar to a parameter mode in a stored procedure – IN is the default and we don’t have to use it, though we can:

  1  DECLARE
  2     v_sql VARCHAR2(2000);
  3     v_count NUMBER;
  4     v_deptno NUMBER:=10;
  5  BEGIN
  6     v_sql:='CALL get_emp_count(:0, :1)';
  7     EXECUTE IMMEDIATE v_sql USING IN v_deptno, OUT v_count;
  8     DBMS_OUTPUT.PUT_LINE(v_count);
  9* END;
SQL> /
3

PL/SQL procedure successfully completed.

This technique is useful when you don’t know the name of the stored procedure to be executed with EXECUTE IMMEDIATE statement in advance, though you know the parameters.

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

Privileges needed to create spatial index in dynamic SQL

Executing DDL commands explicitly or with EXECUTE IMMEDIATE statement usually require special privileges granted directly (not as a part of a ROLE). With creating tables and indexes, things are really simple, you just need to grant CREATE TABLE privilege and that’s it.

When you need to create a special index, such as SPATIAL index, it is not obvious which privileges need to be granted.

Symptoms of the problem:

SQL> BEGIN
  2      EXECUTE IMMEDIATE 'CREATE INDEX idx_poi_geometry ON poi_geometry(geom) INDEXTYPE IS MDSYS.SPATIAL_INDEX';
  3  END;
  4  /
BEGIN
*
ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-13249: internal error in Spatial index: [mdidxrbd]
ORA-13249: Error in Spatial index: index build failed
ORA-13249: Error in R-tree: [mdrcrtscrt]
ORA-13231: failed to create index table [MDRT_2942E$] during R-tree creation
ORA-13249: Stmt-Execute Failure: CREATE TABLE "SCOTT".MDRT_2942E$
(NODE_ID NUMBER, NODE_LEVEL NUMBER, INFO BLOB)  LOB (INFO) STORE AS (NOCACHE)
PCTFREE 2
ORA-29400: data cartridge error
ORA-01950: no privileges on tablespace 'USERS'
ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 10
ORA-06512: at line 2


Elapsed: 00:00:00.07

Note, that even though the statement failed, Oracle created an object, so before retrying you need to drop it:

SQL> DROP INDEX idx_poi_geometry;

Index dropped.

From the error message, we can conclude that there is something wrong with the tablespace privileges.
And this is not something dynamic SQL specific as the plain DDL command fails with the same error:

SQL> CREATE INDEX idx_poi_geometry ON poi_geometry(geom) INDEXTYPE IS MDSYS.SPATIAL_INDEX;
CREATE INDEX idx_poi_geometry ON poi_geometry(geom) INDEXTYPE IS MDSYS.SPATIAL_INDEX
*
ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-13249: internal error in Spatial index: [mdidxrbd]
ORA-13249: Error in Spatial index: index build failed
ORA-13249: Error in R-tree: [mdrcrtscrt]
ORA-13231: failed to create index table [MDRT_29430$] during R-tree creation
ORA-13249: Stmt-Execute Failure: CREATE TABLE "SCOTT".MDRT_29430$
(NODE_ID NUMBER, NODE_LEVEL NUMBER, INFO BLOB)  LOB (INFO) STORE AS (NOCACHE)
PCTFREE 2
ORA-29400: data cartridge error
ORA-01950: no privileges on tablespace 'USERS'
ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 10

After granting UNLIMITED TABLESPACE privilege:

SQL> DROP INDEX idx_poi_geometry;

Index dropped.

Elapsed: 00:00:00.04
SQL> CREATE INDEX idx_poi_geometry ON poi_geometry(geom) INDEXTYPE IS MDSYS.SPATIAL_INDEX;

Index created.

Elapsed: 00:00:05.98

Dynamic SQL also works:

SQL> DROP INDEX idx_poi_geometry;

Index dropped.

Elapsed: 00:00:00.36
SQL> BEGIN
  2      EXECUTE IMMEDIATE 'CREATE INDEX idx_poi_geometry ON poi_geometry(geom) INDEXTYPE IS MDSYS.SPATIAL_INDEX';
  3  END;
  4  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:06.08

A tricky part comes when you try to execute this dynamic SQL wrapped into a packaged procedure call made by a different user:

SQL> BEGIN
  2      EXECUTE IMMEDIATE 'BEGIN scott.pkg_indexes.create_spatial_index; END;';
  3  END;
  4  /
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-13249:
internal error in Spatial index: [mdidxrbd]
ORA-13249: Error in Spatial index:
index build failed
ORA-13249: Error in R-tree: [mdrcrtscrt]
ORA-13231: failed to
create index table [MDRT_29480$] during R-tree creation
ORA-13249: Stmt-Execute
Failure: CREATE TABLE "SCOTT".MDRT_29480$ (NODE_ID NUMBER, NODE_LEVEL
NUMBER, INFO BLOB)  LOB (INFO) STORE AS (NOCACHE)  PCTFREE 2
ORA-29400:
data cartridge error
ORA-01031: in

PL/SQL procedure successfully completed.

Now it’s time to grant more privileges (as a SYSTEM USER):

SQL> GRANT CREATE TABLE, CREATE SEQUENCE TO scott
  2  /

Grant succeeded.

Now let’s execute last dynamic PL/SQL again (the packaged procedure create_spatial_index includes drop index command, so we should not worry about it):

SQL> BEGIN
  2      EXECUTE IMMEDIATE 'BEGIN scott.pkg_indexes.create_spatial_index; END;';
  3  END;
  4  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:39.67

Summary:

To successfully create a spatial index in dynamic SQL (or dynamic PL/SQL), the owner of the index may need the following privileges granted explicitly:

• UNLIMITED TABLESPACE
• CREATE TABLE
• CREATE SEQUENCE

Passing comma delimtied string to a stored procedure

It would be nice if we could always avoid dynamic SQL in our PL/SQL. The execution path would be mostly known upfront, it would be so easy debug and troubleshoot the code, etc. However, there are times when it seems almost impossible to write SQL queries in PL/SQL without relying on dynamic SQL functionality. One of the great examples is passing multiple ids separated by some delimiter (mostly by a comma).

Let’s consider a stored procedure with the following signature:

PROCEDURE sp_get_empployees(p_dept_ids VARCHAR2, p_result OUT SYS_REFCURSOR)

We need to retrieve all employees who work in one of the departments with department numbers listed in p_dept_ids line.

The query may look something like this:

SELECT empno, ename, job, deptno
FROM emp
WHERE deptno IN ([list of ids])
ORDER BY deptno, ename

Let’s first review a “traditional” approach based on dynamic SQL:

CREATE OR REPLACE PROCEDURE sp_get_empployees(p_dept_ids VARCHAR2, p_result OUT SYS_REFCURSOR)
AS
  v_sql VARCHAR2(2000);
BEGIN
  v_sql:='SELECT empno, ename, job, deptno
	  FROM emp
	  WHERE deptno IN (' || p_dept_ids || ')
	  ORDER BY deptno, ename';
  OPEN p_result FOR v_sql;

END sp_get_empployees;
/

If you need to test this procedure in SQL*Plus, here is how to do it:

SQL> var c refcursor
SQL> exec sp_get_empployees('10,20', :c)

PL/SQL procedure successfully completed.

SQL> print c

     EMPNO ENAME      JOB           DEPTNO
---------- ---------- --------- ----------
      7782 CLARK      MANAGER           10
      7839 KING       PRESIDENT         10
      7934 MILLER     CLERK             10
      7876 ADAMS      CLERK             20
      7902 FORD       ANALYST           20
      7566 JONES      MANAGER           20
      7788 SCOTT      ANALYST           20
      7369 SMITH      CLERK             20

8 rows selected.

Everything seems to look good at the first glance, but there are some issues.

What if p_dept_ids value is NULL (empty) which could mean that we don’t want any employees to be returned back?

Passing NULL will lead to an error:

SQL> exec sp_get_empployees('', :c)
BEGIN sp_get_empployees('', :c); END;

*
ERROR at line 1:
ORA-00936: missing expression
ORA-06512: at "SCOTT.SP_GET_EMPPLOYEES", line 9
ORA-06512: at line 1

Yes, we could add a validation of the input parameter, and this is not a bad idea in general. The main purpose of this post is to show you a workaround that would not rely on the dynamic SQL at all and also it won’t use the input parameter validation.
We are going to leverage the power of regular expressions to split the comma delimited line:

CREATE OR REPLACE PROCEDURE sp_get_empployees2(p_dept_ids VARCHAR2, p_result OUT SYS_REFCURSOR)
AS
BEGIN
  OPEN p_result FOR 
  SELECT empno, ename, job, deptno
  FROM emp
  WHERE deptno IN (SELECT REGEXP_SUBSTR(p_dept_ids, '[^,]+', 1, LEVEL) deptno
                   FROM dual
                   CONNECT BY LEVEL <= LENGTH(p_dept_ids) - LENGTH(REPLACE(p_dept_ids,','))+1 
                  )
  ORDER BY deptno, ename;
END sp_get_empployees2;
/

Here is the test:

SQL> exec sp_get_empployees2('10,20', :c)

PL/SQL procedure successfully completed.

SQL> print c

     EMPNO ENAME      JOB           DEPTNO
---------- ---------- --------- ----------
      7782 CLARK      MANAGER           10
      7839 KING       PRESIDENT         10
      7934 MILLER     CLERK             10
      7876 ADAMS      CLERK             20
      7902 FORD       ANALYST           20
      7566 JONES      MANAGER           20
      7788 SCOTT      ANALYST           20
      7369 SMITH      CLERK             20

8 rows selected.

SQL> exec sp_get_empployees2('', :c)

PL/SQL procedure successfully completed.

SQL> print c

no rows selected

In subsequent posts I will show some more workarounds to the presented solution.

If you want to learn how to come up with numerous workarounds on your own, check my book “Oracle SQL Tricks and Workarounds” for instructions.