MacLochlainns Weblog

A student encountered a connection problem with a PHP application that failed to resolve to the database. The steps to validate this are to check the PHP credentials, which are in this older post. Once you’ve done that, you should do:

1. Check the tnsnames.ora file contents, they should have a hostname value not an IP address. When you’ve not set your machine hostname in Microsoft Windows host file (mine in the example is: McLaughlin7x64), Oracle opts for the IP address.

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = McLaughlin7x64)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = xe)
    )
  )

2. Check if the Oracle TNS (Transparent Network Substrate) validates with this command:

tnsping xe

3. If the prior step fails, check to see if you’re listener is running and that it’s configuration file looks more or less like this sample. If it doesn’t, you should edit this listener.ora file and start the listener process through the services dialog.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = McLaughlin7x64)(PORT = 1521))
    )
  )

4. You should then be able to connect like this:

sqlplus username/password@xe

Hope this helps a few people.

Somebody wanted to know if you could add a NOT NULL column constraint in MySQL. That’s a great question and the answer is yes. The following example shows you how to do it.

1. Create a sample table without a NOT NULL constraint on a column that should have one. After creating this table, describe it and you’ll see that the testing_text column is

CREATE TABLE testing
( testing_id   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
, testing_text VARCHAR(10));

2. Change the column definition from null allowed to not null for the TESTING_TEXT column. The only problem with this syntax is that it only works when there are no null values in the table or there are no rows in the table.

ALTER TABLE testing
CHANGE testing_text testing_text VARCHAR(10) NOT NULL;

3. Change the column definition from not null constrained to null allowed for the TESTING_TEXT column.

ALTER TABLE testing
CHANGE testing_text testing_text VARCHAR(10);

As always, I hope this helps.

Quite some time ago, summer 2008, I wrote a post about how you could embed an image in a cell comment. It was for the then current version of the product – Excel 2007. Here’s a User-Defined Function (UDF) in VBA to perform that trick that works in Excel 2010/2011. A comment on that older post fed my disappointment that Excel 2011 doesn’t even support the navigation but it does perform it with VBA. This includes the UDF to add an image and an ordinary Excel Macro to remove the image when you want to preserve the text.

Let’s assume you have a list of image files in a directory and that you’ve entered their fully qualified or absolute path values column B of your worksheet. Now you want to load them as comment images in Column A and insert a value in each column A cell that describes the comment image.

Unfortunately, as I’ve explained before you can’t assign the image as a property of the cell (or more precisely, I’ve never found a way to do it). If this is wrong, please post the magic here for all to enjoy without a fee or registration. 😉

The following UDF takes a string value to describe the image and a cell reference that holds a string value that holds an absolute file name, which is a logical drive letter (C:\), a file path, and file name.

Function InsertCommentImage(title As String, cellAddress As Range)
  Dim commentBox As comment
  ' Clear any comments before attempting to add them.
  Application.ActiveCell.ClearComments
  ' Define the comment as a local variable and assign the file name from the
  ' cellAddress input parameter to the comment of a cell.
  Set commentBox = Application.ActiveCell.AddComment
  With commentBox
    .Text Text:=""
    With .Shape
      .Fill.UserPicture (cellAddress.Value)
      .ScaleHeight 3#, msoFalse, msoScaleFormTopLeft
      .ScaleWidth 2.4, msoFalse, msoScaleFromTopLeft
    End With
    ' Set the visible to True when you always want the image displayed, and
    ' to False when you want it displayed only when you click on the cell.
    .Visible = False
  End With
  InsertCommentImage = title
End Function

A fully qualified address for the cellAddress parameter on a PC would look like this in let’s say cell B1:

C:\Data\Images\WizardChess.png

While it would be like this for the cellAddress parameter on a Mac OS X installation in cell B1:

Macintosh HD:Users:mclaughlinm:Desktop:WizardChess.png

You would call this from a cell like this when the text is provided as a string and fully qualified file name is in cell B1 of a worksheet named ImageSource:

=InsertCommentImage("Wizard Chess",B1)

Alternatively, you rewrite InsertCommentImage() as follows, which takes a string for the cell value and a string for the absolute file name:

Function InsertCommentImage(title As String, absoluteFileName As String)
  Dim commentBox As Comment
  ' Clear any comments before attempting to add them.
  Application.ActiveCell.ClearComments
  ' Define the comment as a local variable and assign the file name from the
  ' cellAddress input parameter to the comment of a cell.
  Set commentBox = Application.ActiveCell.AddComment
  With commentBox
    .Text Text:=""
    With .Shape
      .Fill.UserPicture (absoluteFileName)
      .ScaleHeight 3#, msoFalse, msoScaleFormTopLeft
      .ScaleWidth 2.4, msoFalse, msoScaleFromTopLeft
    End With
    ' Set the visible to True when you always want the image displayed, and
    ' to False when you want it displayed only when you click on the cell.
    .Visible = False
  End With
  InsertCommentImage = title
End Function

With the change of the second parameter, you would call the InsertCommentImage() function with two strings, like:

=InsertCommentImage("Wizard Chess","C:\Data\Images\WizardChess.png")

Here’s how it would look if you’d put the formula in cell A1:

This is a simple Excel macro for which you could create a button. You run it when you want to delete only the image comment from a cell. The macro works by highlighting the cell and running it. Naturally, you could wrap it in another Excel macro to navigate through the list and clean up a bunch of comment images at one time.

Sub RemoveComment()
  Application.ActiveCell.ClearComments
End Sub

If you want to allow the macro to detach all comments for a range, you would rewrite it this way:

Sub RemoveComment()
  Application.Selection.ClearComments
End Sub

As always, I hope this helps and furthers sharing information.

Writing an anonymous block to conditionally drop tables and sequences got very old. I figured it was time to simply put a procedure that would simplify it. Avoiding repetition was important too, so it supports an invoker rights model. This mimics the IF EXISTS syntax available in MySQL.

You need to create this as the SYSTEM user and then grant execute permission on the procedure to PUBLIC. Then, you need to create a couple public synonyms.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

-- Create a procedure to simplify dropping objects.
CREATE OR REPLACE PROCEDURE drop_ifexists
( pv_type   VARCHAR2
, pv_table  VARCHAR2 ) AUTHID CURRENT_USER IS
 
  -- String for DDL command.
  sql_text  VARCHAR2(2000);
 
  -- Declare a parameterized cursor.
  CURSOR find_object
  ( cv_type   VARCHAR2
  , cv_table  VARCHAR2 ) IS
    SELECT   uo.object_name
    ,        uo.object_type
    FROM     user_objects uo
    WHERE    uo.object_name = UPPER(cv_table)
    AND      uo.object_type = UPPER(cv_type);
 
BEGIN
 
  -- Open the cursor with the input variables.
  FOR i IN find_object(pv_type, pv_table) LOOP
 
    -- Check for a table object and append cascade constraints.
    IF i.object_type = 'TABLE' THEN
      sql_text := 'DROP '||i.object_type||' '||i.object_name||' CASCADE CONSTRAINTS';
    ELSE
      sql_text := 'DROP '||i.object_type||' '||i.object_name;
    END IF;
 
    -- Run dynamic command.
    EXECUTE IMMEDIATE sql_text;
 
  END LOOP;
 
END drop_ifexists;
/

After creating the procedure in the SYSTEM user schema, you should run these Data Control Language (DCL) commands:

GRANT EXECUTE ON drop_ifexists TO PUBLIC;
CREATE PUBLIC SYNONYM drop_ifexists FOR system.drop_ifexists;

Assuming you have a table named MESSAGE, you can call the drop_ifexists procedure to conditionally delete it as follows below. The same procedure works with any object in a user’s schema.

EXECUTE drop_ifexists('table','message');

The only problem comes with types because they can have dependents. When they have dependents you can’t drop them until you’ve removed all the dependents. There are two types of dependents types and collection types.

Since discovering those may be difficult for new users, here’s another invoker rights function to discover user defined types. You should note that you’ll need to create the types before you try to compile the code (create the stored program in the Oracle database).

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

-- Create a SQL structure.
CREATE OR REPLACE TYPE dependent_type_obj IS OBJECT
( dependent_type  VARCHAR2(10)
, type_name       VARCHAR2(30));
/
 
-- Create a SQL collection of a user-defined data structure.
CREATE OR REPLACE TYPE dependent_type_tab IS TABLE OF dependent_type_obj;
/
 
-- Create a procedure to discover type dependents.
CREATE OR REPLACE FUNCTION type_dependents
( pv_type_name  VARCHAR2 ) RETURN dependent_type_tab AUTHID CURRENT_USER IS
 
  -- Declare a counter for the collection variable.
  c NUMBER := 1;
 
  -- Declare a return type variable.
  list DEPENDENT_TYPE_TAB := dependent_type_tab();
 
  -- Declare a parameterized cursor for dependent types.
  CURSOR find_dependent_types
  ( cv_type  VARCHAR2 ) IS
    SELECT   'STANDALONE' AS dependent_type
    ,        at.type_name
    FROM     all_types at INNER JOIN all_type_attrs ata
    ON       at.type_name = ata.type_name
    WHERE    at.owner = USER
    AND      ata.attr_type_name = UPPER(cv_type)
    UNION ALL
    SELECT   'COLLECTION' AS dependent_type
    ,        act.type_name
    FROM     all_types at INNER JOIN all_coll_types act
    ON       at.type_name = act.elem_type_name
    WHERE    at.owner = USER
    AND      act.elem_type_name = UPPER(cv_type); 
 
BEGIN
 
  -- Loop through all returns and add them to the return collection.
  FOR i IN find_dependent_types(pv_type_name) LOOP
    list.EXTEND;
    list(c) := dependent_type_obj(i.dependent_type, i.type_name);
	c := c + 1;
  END LOOP;
 
  -- Return the list.
  RETURN list;
END type_dependents;
/

You should also make the following grants and synonyms:

GRANT EXECUTE ON system.type_dependents TO PUBLIC;
CREATE PUBLIC SYNONYM type_dependents FOR system.type_dependents;
GRANT ALL ON system.dependent_type_obj TO PUBLIC;
GRANT ALL ON system.dependent_type_tab TO PUBLIC;

Then, you can call the type_dependents function to find any dependent user-defined data types. If so, you must drop them from the bottom or most specialized straight up the type tree to the root node data type.

SELECT *
FROM   TABLE(type_dependents('&type_name'));