MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for November, 2012

SELECT-INTO variables

without comments

Somebody said, I shouldn’t have buried so much information in an answer to a question posed in a comment on a summary blog page. They suggested I put it in a regular blog post, and here it is with as little editing as possible.

The Ron Quizon user provided this sample code and a “What’s wrong with this PL/SQL program?”

1
2
3
4
5
6
7
8
9
10
11
12
13
DECLARE
   v_name friends.fname%TYPE;
   v_grade friends.id%TYPE;
BEGIN
   SELECT fname, grade
      INTO &ssv_name, v_grade
   FROM friends
   WHERE v_name = fname;
   DBMS_OUTPUT.PUT_LINE(NVL(v_name,'No Name ')||' has an id of '||NVL(v_grade, 0));
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE ('There is no record with '||'id 123');
END;

While this certainly looks like a question from a class on PL/SQL or something from Steven Feuerstein’s PL/SQL question quizzes, I paused before answering it. The give away is the style is what Steven’s advocated for two decades. My guess is that it’s for Steven’s Q&A stuff, which means there’s no harm in answering it because I’m likely not defeating a teacher’s learning objective.

There are two core errors. The first error is an inappropriate assignment target on line #6 and the second is failing to assign a value to the local v_name variable. If you’d taken the time to create the tables and try it, you should generate an error like this:

SQL> /
Enter VALUE FOR ssv_name: Harry
OLD   6:       INTO &ssv_name, v_grade
NEW   6:       INTO Harry, v_grade
      INTO Harry, v_grade
           *
ERROR at line 6:
ORA-06550: line 6, COLUMN 12:
PLS-00201: identifier 'HARRY' must be declared
ORA-06550: line 7, COLUMN 4:
PL/SQL: ORA-00904: : invalid identifier
ORA-06550: line 5, COLUMN 4:
PL/SQL: SQL Statement ignored

Also, at this point if you couldn’t see the error by quick inspection, it should be transparent to you. However, I don’t believe in playing games. Here’s the answer you need:

  • The SELECT-INTO statement is an left-to-right assignment operator in SQL (available in all dialects), and the right operand (variable) or list of operands (variables) must be identifier(s). “Identifiers are words. They can be reserved words, predefined identifiers, quoted identifiers, user-defined variables, subroutines, or user-defined types. (Oracle Database 11g PL/SQL Programming on page #51).” In this case, as the right operand(s), they are user-defined variables.
  • The & (ampersand) preceding ssv_name makes that a substitution placeholder or target, which is a SQL*Plus prompt for a value. The value provided at run-time is assigned to the SQL*Plus placeholder as a string literal during the preparing phase. That phase precedes the anonymous block parse, fetch, and execute cycle. Therefore, you raise a parsing error while running the anonymous block unless the &ssv_name input value is a valid locally declared variable or defined session bind variable name.
  • Assuming you input a valid identifier, the next problem is that the WHERE clause uses an equality comparison operator against the v_name local variable, which holds a null value. That means the SELECT-INTO always fails on a no data found error unless you add an assignment statement for the v_name variable.

Anyway, I hope spelling it out in a formal blog post was helpful to someone in the future. If so, leave a comment and let me know.

Written by maclochlainn

November 29th, 2012 at 7:09 pm

MySQL Database Triggers

with 3 comments

One of the students wanted an equivalent example to an Oracle DML trigger sample that replaces a white space in a last name with a dash for an INSERT statement. Apparently, the MySQL trigger example in the Oracle Database 11g and MySQL 5.6 Developer Handbook was a bit long. I have to agree with that because the MySQL DML trigger demonstrated cursors and loops in the trigger code.

Triggers can be statement- or row-level actions. Although some databases let you define statement-level triggers, MySQL doesn’t support them. MySQL only supports row-level triggers. Row-level triggers support critical or non-critical behaviors. Critical behavior means the trigger observes an insert, update, or delete that must be stopped, which means it raises an error. Non-critical behavior means the trigger observes a DML statement and logs it or implements a change during the context of the DML activity.

The first example shows you non-critical behavior. It observes an attempt to enter a two-part last name, and replaces the white space with a dash (you can find help on MySQL Regular Expressions in this other post). This means the trigger ensures compliance on how names are entered in the database, which should be protected in the web form (through JQuery or JavaScript) and the database.

DELIMITER $$
DROP TRIGGER IF EXISTS contact_insert$$
 
CREATE TRIGGER contact_insert
BEFORE INSERT ON contact
FOR EACH ROW
BEGIN
  IF new.last_name REGEXP '^.* .*$' THEN
    SET new.last_name := REPLACE(new.last_name,' ','-');
  END IF;
END;
$$
 
DELIMITER ;

The problem with implementing a non-critical trigger is that the database performs the work but clerks entering the data don’t learn the business rule. A critical trigger simply disallows non-conforming data entry. The next program shows a critical behavior with an UPDATE statement row-level trigger. After all, won’t a data entry clerk update the entry with a white space after the INSERT statement didn’t?

Yes, that was a rhetorical question. Spelling out the business rule in the UPDATE statement row-level trigger should educate the persistent errant behavior. While letting the INSERT statement row-level trigger manage the behavior probably saves time for most end-users who make a casual mistake.

DELIMITER $$
DROP TRIGGER IF EXISTS contact_update$$
 
CREATE TRIGGER contact_update
BEFORE UPDATE ON contact
FOR EACH ROW
BEGIN
  IF new.last_name REGEXP '^.* .*$' THEN
    SIGNAL SQLSTATE '42000';
  END IF;
END;
$$
 
DELIMITER ;

Somebody wanted to know why I choose SQLSTATE 42000. That’s because it signals an error in the SQL statement, and that’s the closest existing SQLSTATE to the actual behavior that exists. Moreover, the error identified by the critical trigger signals non-compliance with the application’s SQL standards that protects the data.

Hopefully, this helps somebody looking for a MySQL database trigger example that raises an exception. The example works with MySQL 5.5 forward because the critical trigger uses the SIGNAL feature, which was introduced in MySQL 5.5. Another article shows you how to leverage MyISAM tables to create a logging mechanism for critical event triggers, and you click this link to the MySQL Triggers with Logging blog entry.

Written by maclochlainn

November 22nd, 2012 at 11:32 pm

Reset sequence START WITH

with 3 comments

Two things contributed to this post. One was a student question about the difference between the MAX_VALUE of a sequence and the actual sequence value. The other was a comment on an earlier post addressing an NDS approach to resetting sequences.

The student wanted to understand why there were gaps in the sequence, since they created it with the default values, like this:

CREATE SEQUENCE sequence_name;

A default sequence in an Oracle database sets the cache value to 20, which means you get gaps every time a new set is allocated. This Ask Tom question and answer holds that information.

The blog comment was on an NDS statement post. I wasn’t quite sure what the comment wanted because there really wasn’t a question or a complete code example. Having demonstrated how to dynamically drop and recreate a new sequence without a gap in a comment reply, I thought that was adequate.

Having pointed the student to the Ask Tom column and my answer, he wanted to know how to create a stored procedure to reset sequences dynamically. It took me a couple weeks to get back to this but here’s the procedure. The tricky element of the procedure is the “no binding values allowed” restriction placed on NDS statements that process DDL statements.

The procedure uses two local procedures and two local functinons. The local find_sequence finds a sequence name in the schema, and find_next_sequence function returns the next value. The local create_sequence and drop_sequence procedures respectively isolate the logic for dynamic drops and creates of the sequence.

Warning: The restriction with this design assumes that the table name and sequence name are linked by using the table name and an _ID suffix.

-- Declare an autonomous procedure to drop sequences.
CREATE OR REPLACE PROCEDURE reset_sequence
( pv_table_name     VARCHAR2
, pv_sequence_name  VARCHAR2
, pv_cache_value    NUMBER DEFAULT 0 ) IS
 
  -- Declares stubs to remove forward reference limits.
  PROCEDURE create_sequence ( pv_sequence_name  VARCHAR2, pv_cache_value  NUMBER );
  PROCEDURE drop_sequence ( pv_sequence_name  VARCHAR2 );
  FUNCTION find_sequence ( pv_sequence_name  VARCHAR2 ) RETURN VARCHAR2;
  FUNCTION find_next_sequence ( pv_table_name  VARCHAR2 ) RETURN VARCHAR2;
 
  -- Drops sequence.
  PROCEDURE drop_sequence
  ( pv_sequence_name  VARCHAR2 ) IS
    -- Declare local variable(s).
    lv_statement      VARCHAR2(200);
    lv_sequence_name  VARCHAR2(30);
  BEGIN
 
    /*  Conditionally drop any sequence using a local function to find a valid
        sequence name before attempting to drop it. */  
    IF find_sequence(DBMS_ASSERT.SIMPLE_SQL_NAME(pv_sequence_name)) > 0 THEN  
 
      /* Dynamically drop sequence, which requires concatenating the sanitized 
         sequence name because you can't bind values on a DDL statement. */
      lv_statement := 'DROP sequence '||DBMS_ASSERT.SIMPLE_SQL_NAME(pv_sequence_name);
 
      -- Execute statement immediately.
      EXECUTE IMMEDIATE lv_statement;
 
    END IF;      
  END drop_sequence;
 
    -- Drops sequence.
  PROCEDURE create_sequence
  ( pv_sequence_name  VARCHAR2
  , pv_cache_value    NUMBER ) IS
    -- Declare local variable(s).
    lv_statement      VARCHAR2(200);
    lv_next_sequence  NUMBER;
  BEGIN
 
    -- Assign the next sequence value if one exists.
    lv_next_sequence := find_next_sequence(pv_table_name);
 
    -- Check whether a cache value has been provided.
    IF pv_cache_value > 0 THEN
 
      /* Dynamically create a sequence statement with a new start value that is one
         greater than the highest value in the table that uses the sequence. */
      lv_statement := 'CREATE SEQUENCE '||DBMS_ASSERT.SIMPLE_SQL_NAME(pv_sequence_name)||CHR(10)
                   ||       'MINVALUE 1 NOMAXVALUE INCREMENT BY 1'||CHR(10)
                   ||       'START WITH '||lv_next_sequence||' CACHE '||pv_cache_value||' NOORDER NOCYCLE';
 
    ELSE
 
      /* Dynamically create a sequence statement with a new start value that is one
         greater than the highest value in the table that uses the sequence. */
      lv_statement := 'CREATE SEQUENCE '||DBMS_ASSERT.SIMPLE_SQL_NAME(pv_sequence_name)||CHR(10)
                   ||       ' MINVALUE 1 NOMAXVALUE'||CHR(10)
                   ||       'INCREMENT BY 1 START WITH '||lv_next_sequence||' NOCACHE NOORDER NOCYCLE';
 
    END IF;
 
    -- Execute create sequence statement.
    EXECUTE IMMEDIATE lv_statement;
 
  END create_sequence;
 
  -- Finds whether a sequence exists.
  FUNCTION find_sequence
  ( pv_sequence_name  VARCHAR2 ) RETURN VARCHAR2 IS
    -- Declare local return variable(s).
    lv_statement      VARCHAR2(200);
    lv_sequence_name  VARCHAR2(30);
    lv_return_value   NUMBER := 0;
 
    -- Declare local system reference cursor.
    lv_cursor  SYS_REFCURSOR;
 
  BEGIN
    -- Dynamically find sequence.
    lv_statement := 'SELECT   sequence_name'||CHR(10)
                 || 'FROM     user_sequences'||CHR(10)
                 || 'WHERE    sequence_name = :seq_name';
 
    OPEN lv_cursor FOR lv_statement USING DBMS_ASSERT.SIMPLE_SQL_NAME(pv_sequence_name);
    FETCH lv_cursor INTO lv_sequence_name;
    CLOSE lv_cursor;
 
    -- Convert valid sequence name to positive integer, which represents truth.
    lv_return_value := LENGTH(lv_sequence_name);
 
    -- Return value.
    RETURN lv_return_value;
  EXCEPTION
    -- Reached when DBMS_ASSERT check fails.
    WHEN OTHERS THEN
      RETURN lv_return_value;
  END find_sequence;
 
  -- Finds the next sequence value.
  FUNCTION find_next_sequence
  ( pv_table_name  VARCHAR2 ) RETURN VARCHAR2 IS
    -- Declare local return variable(s).
    lv_statement      VARCHAR2(200);
    lv_sequence_value  NUMBER;
    lv_return_value    NUMBER := 1;
 
    -- Declare local system reference cursor.
    lv_cursor  SYS_REFCURSOR;
 
  BEGIN
    -- Dynamically find sequence.
    lv_statement := 'SELECT   MAX('||DBMS_ASSERT.SIMPLE_SQL_NAME(pv_table_name)||'_ID) + 1'||CHR(10)
                 || 'FROM    '||DBMS_ASSERT.SIMPLE_SQL_NAME(pv_table_name);
 
    OPEN lv_cursor FOR lv_statement;
    FETCH lv_cursor INTO lv_sequence_value;
    CLOSE lv_cursor;
 
    -- Assign the return value from the NDS statement to a local return variable.
    lv_return_value := lv_sequence_value;
 
    -- Return value.
    RETURN lv_return_value;
  EXCEPTION
    -- Reached when DBMS_ASSERT check fails.
    WHEN OTHERS THEN
      RETURN lv_return_value;
  END find_next_sequence;
 
  -- Set precompiler instruction to run in a discrete process.
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
 
  -- Conditionally drop sequence.
  drop_sequence(DBMS_ASSERT.SIMPLE_SQL_NAME(pv_sequence_name));
 
  -- Create sequence.
  create_sequence(DBMS_ASSERT.SIMPLE_SQL_NAME(pv_sequence_name), pv_cache_value);
 
END;
/

You can test this procedure with the following script, which includes COMMIT statements. Those are requires because the stand alone procedure runs independently of the current session as an autonomous procedure.

-- Conditionally drop existing tables and sequences.
BEGIN
  FOR i IN (SELECT object_name, object_type FROM user_objects WHERE object_name IN ('SAMPLE','SAMPLE_S')) LOOP
    IF i.object_type = 'TABLE' THEN
      EXECUTE IMMEDIATE 'DROP TABLE '||i.object_name||' CASCADE CONSTRAINTS';
      dbms_output.put_line(i.object_name);
    ELSIF i.object_type = 'SEQUENCE' THEN
      EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.object_name;   
      dbms_output.put_line(i.object_name);
    END IF;
  END LOOP;
END;
/
 
-- Create test table.
CREATE TABLE sample
(sample_id  NUMBER);
 
-- Create test table.
CREATE SEQUENCE sample_s;
 
-- Insert a seeding row.
INSERT INTO sample VALUES (10);
 
-- Commit the values because the procedure is autonomous.
COMMIT;
 
-- Reset the sequence value.
EXECUTE reset_sequence('SAMPLE','SAMPLE_S');
 
-- Insert the next sequence value.
INSERT INTO sample VALUES (sample_s.NEXTVAL);
 
-- Commit the values because the procedure is autonomous.
COMMIT;
 
-- Query the result to ensure the numbers are sequential.
SELECT * FROM sample;
 
EXECUTE reset_sequence('SAMPLE','SAMPLE_S',500);
 
-- Insert the next sequence value.
INSERT INTO sample VALUES (sample_s.NEXTVAL);
 
-- Query the result to ensure the numbers are sequential.
SELECT * FROM sample;

Hope this helps anybody interested in automating the process of resetting a START WITH value of an Oracle sequence.

Written by maclochlainn

November 22nd, 2012 at 2:03 pm