MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Querying an Object Type

without comments

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.

Written by maclochlainn

November 25th, 2014 at 12:33 am

Finding Direct Indexes

without comments

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   ui.index_name
,        uic.column_position
,        uic.column_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('&&table_name')
AND NOT  ui.index_name IN (SELECT constraint_name
                           FROM   user_constraints
                           WHERE  table_name = UPPER('&&table_name'))
ORDER BY ui.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.

Written by maclochlainn

November 23rd, 2014 at 8:42 pm

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

Using MySQL Workbench

with 2 comments

I’ve been setting up a simplified lab environment to let my students learn use in class. This added content will show them how to do reverse engineering with MySQL Workbench.

It’s a complete Fedora image with MySQL and Oracle Database 11g for the course. The uncompressed image is 14GB and the compressed image is 5.3GB. I chose Fedora because it’s the smallest open source image that supports both environments, and Fedora is the closest to Red Hat and Oracle Unbreakable Linux. I’m inclined to make the instance available generally but haven’t figured out the best way to do that.

Here are the new instructions I’m adding and if you have any input leave it as a comment. ;-)

You connect as the student user, which puts you in the /home/student directory. Once connected to the Fedora OS, you open a Terminal session by clicking on Activities in the upper right hand corner, and then you type terminal in the search box. When you’re in the Terminal session, use the following command to verify that the apply_mysql_lab1.sql file is correct:

cat Data/cit225/mysql/lab1/apply_mysql_lab1.sql

It should display the following commands:

\. /home/student/Data/cit225/mysql/lib/cleanup.sql
\. /home/student/Data/cit225/mysql/lib/create_mysql_store_ri.sql
\. /home/student/Data/cit225/mysql/lib/seed_mysql_store_ri.sql

You can run the apply_mysql_lab1.sql to create the tables in the studentdb database, and seed them with data. Assuming you’re in the same /home/student directory, you connect to the studentdb database with the following syntax:

mysql -ustudent -pstudent studentdb

or, more securely:

mysql -ustudent studentdb -p

Having connected to the studentdb database, you can run the following command:

\. /home/student/Data/cit225/mysql/lab1/apply_mysql_lab1.sql

It creates a Video store image and seeds it with some basic data. You can use the show command to see the tables you’ve created, like:

show tables;

It displays:

+---------------------+
| Tables_in_studentdb |
+---------------------+
| address             |
| common_lookup       |
| contact             |
| contacts            |
| current_rental      |
| item                |
| member              |
| rental              |
| rental_item         |
| street_address      |
| system_user         |
| telephone           |
+---------------------+
12 rows in set (0.00 sec)

After successfully creating and seeding the studentdb database, you can run MySQL Workbench by launching it from the search field (recommended). Alternatively, you can open it from a terminal session with the following command. Unfortunately, a command-line launch links the terminal and the MySQL Workbench processes and closing the terminal will close the MySQL Workbench.

mysql-workbench

Here are the instructions for the lab with MySQL Workbench:

FedoraMySQLWorkbenchHome

  1. The first displayed page of MySQL Workbench is the home page (click on it or any of the others to see the full size image). Click the symbol to the right of the MySQL Workbench title.

MySQL_Lab3_01

  1. After clicking the the symbol, it launches the Startup New Connection dialog. Enter a name for your new connection. I recommend you enter MySQLConnection.

MySQL_Lab3_02

  1. Click the Test Connection button to see if it works.

MySQL_Lab3_03

  1. When you click the Test Connection button, MySQL Workbench prompts you for a password. After entering a password and verifying the connection, click the OK button to test the connection.

MySQL_Lab3_04

  1. Click the OK button to continue.

MySQL_Lab3_05

  1. Click the gray highlighted MySQLConnection connection icon below the MySQL Connection title to launch the MySQL Workbench application.

MySQL_Lab3_03

  1. When you click the gray highlighted MySQLConnection connection icon, MySQL Workbench prompts you for a password. After entering a password and verifying the connection, click the OK button to connect to the MySQL Workbench application.

MySQL_Lab3_06

  1. The MySQL Workbench launches in the default view.

MySQL_Lab3_07

  1. Click on the Database menu option and then the Reverse Engineering… option, as shown in the illustration.

MySQL_Lab3_08

  1. This displays the Set Parameters for Connecting to a DBMS dialog. Click the Stored Connection list of values. Choose the MySQLConnection (if you used my suggestion) or the one you created from the list of values of the Stored Connection element. Click the Next button to continue.

MySQL_Lab3_09

  1. Enter the password and click the OK button to connect to the MySQL database.

MySQL_Lab3_10

  1. This is an in-progress display, it runs waiting for the password and until the step of the wizard completes.

MySQL_Lab3_11

  1. This dialog displays when the MySQL Workbench application connects to the database, retrieves a schema (database) list from the database management system, and checks the common server configuration issues. Click the Next button to move to the next step of the wizard.

MySQL_Lab3_12

  1. This Select Schemas to Reverse Engineer dialog displays any available schemas. You check the schemas that you want. Click the Next button to move to the next step of the wizard.

MySQL_Lab3_09

  1. Enter the password and click the OK button to connect to the MySQL database to retrieve objects from the database management system.

MySQL_Lab3_14

  1. This dialog displays when the MySQL Workbench application retrieves objects from the database. Click the Next button to move to the next step of the wizard.

MySQL_Lab3_15

  1. This dialog displays when the MySQL Workbench application retrieves objects from the schemata and checks the result. Click the Next button to move to the next step of the wizard.

MySQL_Lab3_15

  1. This dialog displays the tables, views, and routines to import. Click the Execute button to move to the next step of the wizard.

MySQL_Lab3_16

  1. This dialog displays the tables, views, and routines to import. Click the Execute button to move to the next step of the wizard.

MySQL_Lab3_17

  1. This dialog displays shows the reverse engineering objects and puts them in the object image. Click the Next button to move to the next step of the wizard.

MySQL_Lab3_18

  1. This dialog displays a summary of reverse engineering objects. Click the Close button to move to the next step of the wizard.

MySQL_Lab3_19

  1. The MySQL Model dialog displays after you complete the reverse engineering process. Click the EER Diagram icon or EER Diagram tab to see visual depiction of the database objects.

MySQL_Lab3_20

  1. The EER Diagram is equivalent to the EER Diagram tab. The Navigator displays the tables as blue rectangles. You can scroll through the Canvas Panel to work with the display of tables and views.

MySQL_Lab3_21

  1. The next image shows EER Diagram with the tables moved for display purposes. Sometimes there are too many relationship lines, MySQL Workbench lets you split the lines so they don’t clutter the diagram.

MySQL_Lab3_22

  1. The next image shows you the Property tab of EER Diagram. You should see that the drawSplit checkbox is checked, which suppresses the bottom-most relationship line from displaying in the EER from display on the Canvas Panel.

You can now save the MySQL Workbench file in the Lab 3 directory.

Written by maclochlainn

September 25th, 2014 at 5:10 pm

Fedora PostgreSQL Install

without comments

Somebody asked how to put PostgreSQL on my Fedora image with Oracle Database 11g and MySQL. It’s fairly simple. You can check for the current download at yum.postgresql.org and then download it like this as the root user:

yum localinstall http://yum.postgresql.org/9.3/fedora/fedora-20-x86_64/pgdg-fedora93-9.3-1.noarch.rpm

You should see the following output when the download is successful, don’t forget to type y to complete the download:

Loaded plugins: langpacks, refresh-packagekit
pgdg-fedora93-9.3-1.noarch.rpm                              | 5.1 kB  00:00     
Examining /var/tmp/yum-root-2EPf_J/pgdg-fedora93-9.3-1.noarch.rpm: pgdg-fedora93-9.3-1.noarch
Marking /var/tmp/yum-root-2EPf_J/pgdg-fedora93-9.3-1.noarch.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package pgdg-fedora93.noarch 0:9.3-1 will be installed
--> Finished Dependency Resolution
 
Dependencies Resolved
 
================================================================================
 Package           Arch       Version     Repository                       Size
================================================================================
Installing:
 pgdg-fedora93     noarch     9.3-1       /pgdg-fedora93-9.3-1.noarch     2.1 k
 
Transaction Summary
================================================================================
Install  1 Package
 
Total size: 2.1 k
Installed size: 2.1 k
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction (shutdown inhibited)
  Installing : pgdg-fedora93-9.3-1.noarch                                   1/1 
  Verifying  : pgdg-fedora93-9.3-1.noarch                                   1/1 
 
Installed:
  pgdg-fedora93.noarch 0:9.3-1                                                  
 
Complete!

After downloading the packages, you install with the following command:

yum install postgresql93-server

You should see the following output when the installation is successful, don’t forget to type y to complete the installation:

Loaded plugins: langpacks, refresh-packagekit
pgdg93                                                      | 3.6 kB  00:00     
(1/2): pgdg93/20/x86_64/group_gz                            |  332 B  00:00     
(2/2): pgdg93/20/x86_64/primary_db                          |  84 kB  00:00     
Resolving Dependencies
--> Running transaction check
---> Package postgresql93-server.x86_64 0:9.3.5-1PGDG.f20 will be installed
--> Processing Dependency: postgresql93-libs(x86-64) = 9.3.5-1PGDG.f20 for package: postgresql93-server-9.3.5-1PGDG.f20.x86_64
--> Processing Dependency: postgresql93(x86-64) = 9.3.5-1PGDG.f20 for package: postgresql93-server-9.3.5-1PGDG.f20.x86_64
--> Processing Dependency: postgresql93 = 9.3.5-1PGDG.f20 for package: postgresql93-server-9.3.5-1PGDG.f20.x86_64
--> Processing Dependency: libpq.so.5()(64bit) for package: postgresql93-server-9.3.5-1PGDG.f20.x86_64
--> Running transaction check
---> Package postgresql93.x86_64 0:9.3.5-1PGDG.f20 will be installed
---> Package postgresql93-libs.x86_64 0:9.3.5-1PGDG.f20 will be installed
--> Finished Dependency Resolution
 
Dependencies Resolved
 
================================================================================
 Package                  Arch        Version                 Repository   Size
================================================================================
Installing:
 postgresql93-server      x86_64      9.3.5-1PGDG.f20         pgdg93      3.6 M
Installing for dependencies:
 postgresql93             x86_64      9.3.5-1PGDG.f20         pgdg93      1.0 M
 postgresql93-libs        x86_64      9.3.5-1PGDG.f20         pgdg93      203 k
 
Transaction Summary
================================================================================
Install  1 Package (+2 Dependent packages)
 
Total download size: 4.8 M
Installed size: 22 M
Is this ok [y/d/N]: y
Downloading packages:
(1/3): postgresql93-libs-9.3.5-1PGDG.f20.x86_64.rpm         | 203 kB  00:00     
(2/3): postgresql93-9.3.5-1PGDG.f20.x86_64.rpm              | 1.0 MB  00:01     
(3/3): postgresql93-server-9.3.5-1PGDG.f20.x86_64.rpm       | 3.6 MB  00:02     
--------------------------------------------------------------------------------
Total                                              1.6 MB/s | 4.8 MB  00:02     
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction (shutdown inhibited)
  Installing : postgresql93-libs-9.3.5-1PGDG.f20.x86_64                     1/3 
  Installing : postgresql93-9.3.5-1PGDG.f20.x86_64                          2/3 
  Installing : postgresql93-server-9.3.5-1PGDG.f20.x86_64                   3/3 
  Verifying  : postgresql93-server-9.3.5-1PGDG.f20.x86_64                   1/3 
  Verifying  : postgresql93-9.3.5-1PGDG.f20.x86_64                          2/3 
  Verifying  : postgresql93-libs-9.3.5-1PGDG.f20.x86_64                     3/3 
 
Installed:
  postgresql93-server.x86_64 0:9.3.5-1PGDG.f20                                  
 
Dependency Installed:
  postgresql93.x86_64 0:9.3.5-1PGDG.f20                                         
  postgresql93-libs.x86_64 0:9.3.5-1PGDG.f20                                    
 
Complete!

You can confirm the installation with the following command:

rpm -qa | grep postgres

It returns:

postgresql93-9.3.5-1PGDG.f20.x86_64
postgresql93-server-9.3.5-1PGDG.f20.x86_64
postgresql93-libs-9.3.5-1PGDG.f20.x86_64

You’ve now installed PostgreSQL. Hope this helps those timid about adding new software.

Written by maclochlainn

September 24th, 2014 at 2:43 am

Add Color to VIM?

without comments

In Fedora 20, documents have no color coding when you edit them with vi or vim. That’s because Fedora installs vim-minimal by default. You can check what’s running with the following command at a shell prompt in the terminal:

rpm -qa | grep vim

It should print the following to console:

vim-minimal-7.4.179-1.fc20.x86_64

You can download and install vim with the enhanced version by using the following syntax:

sudo yum install vim-enhanced
Loaded plugins: langpacks, refresh-packagekit
Resolving Dependencies
--> Running transaction check
---> Package vim-enhanced.x86_64 2:7.4.417-1.fc20 will be installed
--> Processing Dependency: vim-common = 2:7.4.417-1.fc20 for package: 2:vim-enhanced-7.4.417-1.fc20.x86_64
--> Processing Dependency: libgpm.so.2()(64bit) for package: 2:vim-enhanced-7.4.417-1.fc20.x86_64
--> Running transaction check
---> Package gpm-libs.x86_64 0:1.20.7-3.fc20 will be installed
---> Package vim-common.x86_64 2:7.4.417-1.fc20 will be installed
--> Processing Dependency: vim-filesystem for package: 2:vim-common-7.4.417-1.fc20.x86_64
--> Running transaction check
---> Package vim-filesystem.x86_64 2:7.4.417-1.fc20 will be installed
--> Finished Dependency Resolution
 
Dependencies Resolved
 
================================================================================
 Package              Arch         Version                  Repository     Size
================================================================================
Installing:
 vim-enhanced         x86_64       2:7.4.417-1.fc20         updates       1.0 M
Installing for dependencies:
 gpm-libs             x86_64       1.20.7-3.fc20            fedora         32 k
 vim-common           x86_64       2:7.4.417-1.fc20         updates       5.9 M
 vim-filesystem       x86_64       2:7.4.417-1.fc20         updates        11 k
 
Transaction Summary
================================================================================
Install  1 Package (+3 Dependent packages)
 
Total download size: 7.0 M
Installed size: 23 M
Is this ok [y/d/N]: y
Downloading packages:
(1/4): gpm-libs-1.20.7-3.fc20.x86_64.rpm                    |  32 kB  00:00     
(2/4): vim-enhanced-7.4.417-1.fc20.x86_64.rpm               | 1.0 MB  00:03     
(3/4): vim-filesystem-7.4.417-1.fc20.x86_64.rpm             |  11 kB  00:03     
(4/4): vim-common-7.4.417-1.fc20.x86_64.rpm                 | 5.9 MB  00:05     
--------------------------------------------------------------------------------
Total                                              1.3 MB/s | 7.0 MB  00:05     
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : 2:vim-filesystem-7.4.417-1.fc20.x86_64                       1/4 
  Installing : 2:vim-common-7.4.417-1.fc20.x86_64                           2/4 
  Installing : gpm-libs-1.20.7-3.fc20.x86_64                                3/4 
  Installing : 2:vim-enhanced-7.4.417-1.fc20.x86_64                         4/4 
  Verifying  : gpm-libs-1.20.7-3.fc20.x86_64                                1/4 
  Verifying  : 2:vim-common-7.4.417-1.fc20.x86_64                           2/4 
  Verifying  : 2:vim-enhanced-7.4.417-1.fc20.x86_64                         3/4 
  Verifying  : 2:vim-filesystem-7.4.417-1.fc20.x86_64                       4/4 
 
Installed:
  vim-enhanced.x86_64 2:7.4.417-1.fc20                                          
 
Dependency Installed:
  gpm-libs.x86_64 0:1.20.7-3.fc20          vim-common.x86_64 2:7.4.417-1.fc20  
  vim-filesystem.x86_64 2:7.4.417-1.fc20  
 
Complete!

You can now type vim to edit files in color but vi will still be in black and white.

Written by maclochlainn

September 13th, 2014 at 2:06 am

Posted in Fedora,Linux,vim

MySQL Workbench on Fedora

with one comment

The early release of Fedora 20 disallowed installation of MySQL Workbench but the current version allows it. Almost like Tom Cruise’s Edge of Tomorrow without the drama. All you need to do is follow my earlier instructions for installing MySQL on Fedora 20. I’d check your kernel to know whether it’s supported. You can check that with this command:

<shell> uname -r

My Fedora is at the following version:

3.14.8-200.fc20.x86_64

Then, you can install MySQL Workbench with yum, like this:

<shell> sudo yum install mysql-workbench

It generates the following log file, and if you have Oracle 11g XE installed you can ignore the mime-type error:

Loaded plugins: langpacks, refresh-packagekit
Resolving Dependencies
--> Running transaction check
---> Package mysql-workbench-community.x86_64 0:6.1.7-1.fc20 will be installed
--> Processing Dependency: libzip.so.2()(64bit) for package: mysql-workbench-community-6.1.7-1.fc20.x86_64
--> Processing Dependency: libvsqlitepp.so.3()(64bit) for package: mysql-workbench-community-6.1.7-1.fc20.x86_64
--> Processing Dependency: libtinyxml.so.0()(64bit) for package: mysql-workbench-community-6.1.7-1.fc20.x86_64
--> Processing Dependency: liblua-5.1.so()(64bit) for package: mysql-workbench-community-6.1.7-1.fc20.x86_64
--> Processing Dependency: libgtkmm-2.4.so.1()(64bit) for package: mysql-workbench-community-6.1.7-1.fc20.x86_64
--> Processing Dependency: libgdkmm-2.4.so.1()(64bit) for package: mysql-workbench-community-6.1.7-1.fc20.x86_64
--> Processing Dependency: libctemplate.so.2()(64bit) for package: mysql-workbench-community-6.1.7-1.fc20.x86_64
--> Running transaction check
---> Package compat-lua-libs.x86_64 0:5.1.5-1.fc20 will be installed
---> Package ctemplate.x86_64 0:2.2-5.fc20 will be installed
---> Package gtkmm24.x86_64 0:2.24.4-2.fc20 will be installed
---> Package libzip.x86_64 0:0.11.2-1.fc20 will be installed
---> Package tinyxml.x86_64 0:2.6.2-4.fc20 will be installed
---> Package vsqlite++.x86_64 0:0.3.13-3.fc20 will be installed
--> Finished Dependency Resolution
 
Dependencies Resolved
 
================================================================================
 Package                    Arch    Version        Repository              Size
================================================================================
Installing:
 mysql-workbench-community  x86_64  6.1.7-1.fc20   mysql-tools-community   24 M
Installing for dependencies:
 compat-lua-libs            x86_64  5.1.5-1.fc20   updates                158 k
 ctemplate                  x86_64  2.2-5.fc20     fedora                 174 k
 gtkmm24                    x86_64  2.24.4-2.fc20  fedora                 748 k
 libzip                     x86_64  0.11.2-1.fc20  updates                 59 k
 tinyxml                    x86_64  2.6.2-4.fc20   updates                 49 k
 vsqlite++                  x86_64  0.3.13-3.fc20  updates                 58 k
 
Transaction Summary
================================================================================
Install  1 Package (+6 Dependent packages)
 
Total download size: 26 M
Installed size: 119 M
Is this ok [y/d/N]: y
Downloading packages:
(1/7): compat-lua-libs-5.1.5-1.fc20.x86_64.rpm              | 158 kB  00:01     
(2/7): ctemplate-2.2-5.fc20.x86_64.rpm                      | 174 kB  00:01     
(3/7): tinyxml-2.6.2-4.fc20.x86_64.rpm                      |  49 kB  00:00     
(4/7): gtkmm24-2.24.4-2.fc20.x86_64.rpm                     | 748 kB  00:01     
(5/7): vsqlite++-0.3.13-3.fc20.x86_64.rpm                   |  58 kB  00:00     
(6/7): libzip-0.11.2-1.fc20.x86_64.rpm                      |  59 kB  00:02     
(7/7): mysql-workbench-community-6.1.7-1.fc20.x86_64.rpm    |  24 MB  00:08     
--------------------------------------------------------------------------------
Total                                              2.9 MB/s |  26 MB  00:08     
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : gtkmm24-2.24.4-2.fc20.x86_64                                 1/7 
  Installing : libzip-0.11.2-1.fc20.x86_64                                  2/7 
  Installing : vsqlite++-0.3.13-3.fc20.x86_64                               3/7 
  Installing : ctemplate-2.2-5.fc20.x86_64                                  4/7 
  Installing : compat-lua-libs-5.1.5-1.fc20.x86_64                          5/7 
  Installing : tinyxml-2.6.2-4.fc20.x86_64                                  6/7 
  Installing : mysql-workbench-community-6.1.7-1.fc20.x86_64                7/7 
Error in file "/usr/share/applications/oraclexe-startdb.desktop": "Application/database" is an invalid MIME type ("Application" is an unregistered media type)
  Verifying  : tinyxml-2.6.2-4.fc20.x86_64                                  1/7 
  Verifying  : compat-lua-libs-5.1.5-1.fc20.x86_64                          2/7 
  Verifying  : ctemplate-2.2-5.fc20.x86_64                                  3/7 
  Verifying  : vsqlite++-0.3.13-3.fc20.x86_64                               4/7 
  Verifying  : mysql-workbench-community-6.1.7-1.fc20.x86_64                5/7 
  Verifying  : libzip-0.11.2-1.fc20.x86_64                                  6/7 
  Verifying  : gtkmm24-2.24.4-2.fc20.x86_64                                 7/7 
 
Installed:
  mysql-workbench-community.x86_64 0:6.1.7-1.fc20                               
 
Dependency Installed:
  compat-lua-libs.x86_64 0:5.1.5-1.fc20     ctemplate.x86_64 0:2.2-5.fc20       
  gtkmm24.x86_64 0:2.24.4-2.fc20            libzip.x86_64 0:0.11.2-1.fc20       
  tinyxml.x86_64 0:2.6.2-4.fc20             vsqlite++.x86_64 0:0.3.13-3.fc20    
 
Complete!

After successfully installing MySQL Workbench, you can launch it with the following command:

<shell> mysql-workbench

It should launch the following MySQL Workbench home page (click on it to see the full size image):

FedoraMySQLWorkbenchHome

Written by maclochlainn

September 13th, 2014 at 12:00 am

E-Business Customers

without comments

It seems the Oracle E-Business Suite stymies newbies. Naturally, it’s complex and requires some research before you try to develop any customization.

Having recently fielded a question about how to manage, search, create and update customers for a third party order entry system, I’m always surprised by how little people know about Oracle’s Trading Community Architecture (TCA). They likewise frequently don’t know that there is already a documented process, like the one defined in the Oracle Trading Community Architecture User Guide:

You also have the Oracle Trading Community Architecture Reference Guide, which provides more detailed information. I strongly recommend you read these before trying to Google a solution that may or may not meet your needs. Naturally, good PL/SQL coding skills make this type of activity straightforward. That’s why Oracle staff should master PL/SQL.

Written by maclochlainn

September 4th, 2014 at 11:25 am

Wrapping SQL*Plus

without comments

One annoying thing from installing Oracle Database 11g on Fedora, was that the up arrows for command history didn’t work. I decided to fix that today after seeing Lutz Hartmann’s article on rlwrap. Unfortunately, the epel (Extra Packages for Enterprise Linux) package he recommended doesn’t run on Fedora 20. You can read my tale of woe, or skip to the .bashrc function that fixed it when I installed only rlwrap.

Attempting it on yum, gave me these errors:

# yum install http://www.mirrorservice.org/sites/dl.fedoraproject.org/pub/epel/6/i386/epel-release-6-8.noarch.rpm
Loaded plugins: langpacks, refresh-packagekit
epel-release-6-8.noarch.rpm                                 |  14 kB  00:00     
Examining /var/tmp/yum-root-5CLTPa/epel-release-6-8.noarch.rpm: epel-release-6-8.noarch
Marking /var/tmp/yum-root-5CLTPa/epel-release-6-8.noarch.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package epel-release.noarch 0:6-8 will be installed
--> Processing Conflict: epel-release-6-8.noarch conflicts fedora-release
No package matched to upgrade: epel-release
--> Finished Dependency Resolution
Error: epel-release conflicts with fedora-release-20-3.noarch
 You could try using --skip-broken to work around the problem
 You could try running: rpm -Va --nofiles –nodigest

Poking around for an epel fix wasn’t successful, so I chose to install only the rlwrap package. Here’s that command and log file:

[root@localhost ~]# yum install rlwrap 
Loaded plugins: langpacks, protectbase, refresh-packagekit
0 packages excluded due to repository protections
Resolving Dependencies
--> Running transaction check
---> Package rlwrap.x86_64 0:0.41-1.fc20 will be installed
--> Finished Dependency Resolution
 
Dependencies Resolved
 
================================================================================
 Package         Arch            Version                 Repository        Size
================================================================================
Installing:
 rlwrap          x86_64          0.41-1.fc20             updates           95 k
 
Transaction Summary
================================================================================
Install  1 Package
 
Total download size: 95 k
Installed size: 204 k
Is this ok [y/d/N]: y
Downloading packages:
rlwrap-0.41-1.fc20.x86_64.rpm                               |  95 kB  00:00     
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction (shutdown inhibited)
  Installing : rlwrap-0.41-1.fc20.x86_64                                    1/1 
  Verifying  : rlwrap-0.41-1.fc20.x86_64                                    1/1 
 
Installed:
  rlwrap.x86_64 0:0.41-1.fc20                                                   
Complete!

The next step was getting it to work. A sqlplus function wrapper inside the .bashrc file seemed the easiest. Here’s the code to the .bashrc file:

# .bashrc
 
# Source global definitions
if [ -f /etc/bashrc ]; then
	. /etc/bashrc
fi
 
# Uncomment the following line if you don't like systemctl's auto-paging feature:
# export SYSTEMD_PAGER=
 
# User specific aliases and functions
. /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh
 
# Wrap sqlplus with rlwrap to edit prior lines.
sqlplus()
{
  if [ "$RLWRAP" = "0" ]; then
    sqlplus "$@"
  else
    rlwrap sqlplus "$@"
  fi
}
 
# Set vi as a command line editor.
set -o vi

As always, I hope this helps some folks.

Written by maclochlainn

August 20th, 2014 at 6:58 pm