MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for October, 2014

Check Constraints

without comments

Oracle Database 12c introduces a SEARCH_CONDITION_VC column to the CDB_, DBA_, ALL_, and USER_CONSTRAINTS views. The SEARCH_CONDITION_VC column is a VARCHAR2 data type equivalent to the search condition in the LONG data type SEARCH_CONDITION column. Unfortunately, Oracle Database 11g and earlier versions requires you to convert the LONG data type to a VARCHAR2 for the equivalent behavior. This post provides you with a function to help you do that in Oracle Database 11g.

While Oracle Database 12c let’s you check the search condition of a CHECK constraint, with this query:

SELECT   uc.constraint_name AS constraint_name
,	 uc.search_condition_vc AS search_condition
FROM     user_constraints uc
WHERE    uc.table_name = UPPER('table_name')
AND	 REGEXP_LIKE(uc.search_condition_vc,'search_key','i');

You need the following GET_SEARCH_CONDITION function to convert the SEARCH_CONDITION column from a LONG data type to a VARCHAR2 data type. It uses the DBMS_SQL package to convert the LONG data type.

CREATE OR REPLACE FUNCTION get_search_condition
( pv_table_name   VARCHAR2
, pv_column_name  VARCHAR2 )
RETURN VARCHAR2 AS
 
  /* Declare local variables. */
  lv_cursor           INTEGER := DBMS_SQL.open_cursor;
  lv_feedback         INTEGER;         -- Acknowledgement of dynamic execution
  lv_length           INTEGER;         -- Length of the input string
  lv_value_length     INTEGER;         -- Length of the output string
  lv_constraint_name  VARCHAR2(30);    -- Constraint name
  lv_return           VARCHAR2(32767); -- Function output
  lv_stmt             VARCHAR2(2000);  -- Dynamic SQL statement
  lv_long             LONG;            -- Dynamic LONG data type.
  lv_string           VARCHAR2(32760); -- Maximum length of LONG data type
 
  FUNCTION return_length 
  ( pv_table_name   VARCHAR2
  , pv_column_name  VARCHAR2 ) RETURN VARCHAR2 IS
 
    /* Declare a target variable,  because of the limit of SELECT-INTO. */
    lv_long_view  LONG;
 
    /* Declare a dynamic cursor. */
    CURSOR c
    ( cv_table_name   VARCHAR2
    , cv_column_name  VARCHAR2 ) IS
      SELECT   uc.search_condition
      FROM     user_constraints uc INNER JOIN user_cons_columns ucc
      ON       uc.table_name = ucc.table_name
      AND      uc.constraint_name = ucc.constraint_name
      WHERE    uc.table_name = UPPER(cv_table_name)
      AND      ucc.column_name = UPPER(cv_column_name)
      AND      uc.constraint_type = 'C';
 
  BEGIN
    /* Open, fetch, and close cursor to capture view text. */
    OPEN c (pv_table_name, pv_column_name);
    FETCH c INTO lv_long_view;
    CLOSE c;
 
    /* Return the output CLOB length value. */
    RETURN LENGTH(lv_long_view);
  END return_length;
 
BEGIN
 
  /* Get the length of the CLOB column value. */
  lv_length := return_length(pv_table_name, pv_column_name);
 
  /* Create dynamic statement. */
  lv_stmt := 'SELECT uc.search_condition'||CHR(10)
          || 'FROM   user_constraints uc INNER JOIN user_cons_columns ucc'||CHR(10)
          || 'ON     uc.table_name = ucc.table_name'||CHR(10)
          || 'AND    uc.constraint_name = ucc.constraint_name'||CHR(10)
          || 'WHERE  uc.table_name = UPPER('''||pv_table_name||''')'||CHR(10)
          || 'AND    ucc.column_name = UPPER('''||pv_column_name||''')'||CHR(10)
          || 'AND    uc.constraint_type = ''C''';
 
  /* Parse and define VARCHAR2 and LONG columns. */
  DBMS_SQL.parse(lv_cursor, lv_stmt, DBMS_SQL.native);
  DBMS_SQL.define_column_long(lv_cursor,1);
 
  /* Only attempt to process the return value when fetched. */
  IF DBMS_SQL.execute_and_fetch(lv_cursor) = 1 THEN
    DBMS_SQL.column_value_long(
        lv_cursor
      , 1
      , lv_length
      , 0
      , lv_string
      , lv_value_length);
  END IF;
 
  /* Check for an open cursor. */
  IF DBMS_SQL.is_open(lv_cursor) THEN
    DBMS_SQL.close_cursor(lv_cursor);
  END IF;
 
  /* Convert the long length string to a maximum size length. */
  lv_return := lv_string;
 
  RETURN lv_return;
END get_search_condition;
/

Then, you can use the following query to view the full search criteria of a CHECK constraint that matches part of a search string:

COLUMN constraint_name   FORMAT A16
COLUMN search_condition  FORMAT A30
SELECT   uc.constraint_name AS constraint_name
,        get_search_condition('table_name','column_name') AS search_condition
FROM     user_constraints uc
WHERE    REGEXP_LIKE(get_search_condition('table_name','column_name'),'check_constraint_search_string','i')
AND      uc.constraint_type = 'C';

Hope this helps those looking at discovering the full search criteria of a CHECK constraint.

Written by maclochlainn

October 21st, 2014 at 12:51 am

iPhoto movie export

without comments

What happens when iPhoto doesn’t export movies? One of two things, you re-install iPhoto and risk losing the movies and photos; or you drop down to the Terminal level and move the files manually before re-installing iPhoto.

Option one is easy, you open iPhoto, choose File from menu, and Export… from the File menu list. When you get to the dialog, change it Kind value to original. If everything is working, you should be able to double click the exported file in a Finder window and launch the program with QuickTime Player.

Screen Shot 2014-10-14 at 7.15.48 PM

It’s important to know how to use option two when you’ve copied the movies from your iPhone, iPad, or iPod, and then deleted them. At this point, all you have is a recovery option from your local MacBook, MacBook Pro, or iMac to a safe folder (or command-line directory) before updating iPhoto.

This is how you perform option two:

  1. Open Finder and navigate to your user’s home directory, left click on Pictures, and then right click on iPhoto Library and choose Open With option from the first floating menu then Terminal on the context (or second) floating menu.
  2. In the Terminal window, a pwd command will show you the following directory for a user with the name someuser:
/Users/someuser/Pictures/iPhoto Library
  1. Again in the Terminal window, type a ls command will show you the following directory structure:
AlbumData.xml		Info.plist		Projects.db
Attachments		Library.data		ThemeCache
Auto Import		Library.iPhoto		Thumbnails
Backup			Library6.iPhoto		iLifeShared
Caches			Masters			iPhotoAux.db
Contents		Modified		iPhotoLock.data
Data			Originals		iPhotoMain.db
Data.noindex		Previews
Database		ProjectDBVersion.plist
  1. In the Terminal window, change directory to the Originals directory with this syntax from the previous directory:
cd Originals
  1. The directory structure can differ because of changes over time. The following is a snapshot example from 2012:
Jun 15, 2012    Jun 21, 2012    Jun 24, 2012	Jul 21, 2012    Sep 3, 2012
Jun 18, 2012    Jun 22, 2012    Jul 4, 2012     Aug 14, 2012    Oct 20, 2012
Jun 19, 2012    Jun 23, 2012    Jul 7, 2012     Aug 24, 2012    Nov 21, 2012
  1. Change to any of the preceding directories, and copy the *.MOV files to new directory. I’ve elected to copy the files to the following directory or the someuser home directory (you can make the directory using the GUI Finder interface):
/Users/someuser/MovieBackup

While the directory structure can differ because of changes in iPhone versions over time. Once you change into one of subdirectories of the Originals directory, you can move all of the movie files from one of the subdirectories with this command to the MovieBackup directory:

cp *.MOV ~someuser/MovieBackup

It’s possible to move all your files by scripting a more elegant Bash file. As always, I hope this helps those who encounter the problem.

Written by maclochlainn

October 14th, 2014 at 7:15 pm