Archive for March, 2011
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:
- Check the
tnsnames.orafile contents, they should have a
hostnamevalue not an IP address. When you’ve not set your machine
hostnamein Microsoft Windows
hostfile (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) ) )
- Check if the Oracle TNS (Transparent Network Substrate) validates with this command:
- 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.orafile 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)) ) )
- You should then be able to connect like this:
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.
- Create a sample table without a
NOT NULLconstraint on a column that should have one. After creating this table, describe it and you’ll see that the
CREATE TABLE testing ( testing_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY , testing_text VARCHAR(10));
- Change the column definition from null allowed to not null for the
TESTING_TEXTcolumn. 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;
- Change the column definition from not null constrained to null allowed for the
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
While it would be like this for the
cellAddress parameter on a Mac OS X installation in cell
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
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:
Here’s how it would look if you’d put the formula in cell
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.
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'));