Archive for December, 2006

Delete from one table with matching rows of the other in Oracle

Written by coregps on Tuesday, December 26th, 2006 in Oracle.

If you're new here, you may want to subscribe to my RSS feed. Thanks for visiting!

I want to delete all rows from table A with matching rows of table B in Oracle. This can be achived by using the “EXISTS” statement or “IN” statement:

DELETE FROM A WHERE EXISTS(
SELECT (1) FROM A WHERE A.ColumnA=B.ColumnB AND …);

For example, I have two tables with the names EMP and DEPT. The first table EMP lists the names of the employees of a company under the column ENAME and the number of the department for which they work under the column DEPTNO. The table DEPT has a similar column named DEPTNO in which each department number is only listed once and adjacent to this is a column entitled DNAME giving the names of the respective departments. I want to delete all employees who work for research dept.

DELETE FROM EMP WHERE EXISTS(
SELECT (1) FROM EMP WHERE EMP.DEPTNO = DEPT.DEPTNO AND DEPT.DNAME = ‘Research’);

or

DELETE FROM EMP WHERE DEPTNO IN(
SELECT DEPTNO FROM DEPT WHERE DNAME = ‘Research’);

PHP function used to check if a URL exists

Written by coregps on Monday, December 25th, 2006 in PHP.

I have a PHP function which is used to check if a URL exists. It works very well on my old web server. However, when I switched to my new hosting providers, It can’t work any more. The code of the function is as following:

< ?php
function check_url($url)
{
$furl = @fopen($url, "r");
if (!$furl) {
return false;
}
@fclose($furl);
return true;
}
?>

Aftering the PHP manual, I found the following section:

If PHP has decided that filename specifies a registered protocol, and that protocol is registered as a network URL, PHP will check to make sure that allow_url_fopen is enabled. If it is switched off, PHP will emit a warning and the fopen call will fail.

When I looked at the phpinfo() page. I found that the parameter “allow_url_fopen” was set to “Off”. Although someone recommend setting this parameter to On using ini_set at the first line of the script:

< ?php

ini_set("allow_url_fopen", "1");
?>

This made no difference. In the PHP manual, I found the root cause of the problem.

allow_url_fopen boolean
This option enables the URL-aware fopen wrappers that enable accessing URL object like files. Default wrappers are provided for the access of remote files using the ftp or http protocol, some extensions like zlib may register additional wrappers.
Note: This setting can only be set in php.ini due to security reasons.
Note: This option was introduced immediately after the release of version 4.0.3. For versions up to and including 4.0.3 you can only disable this feature at compile time by using the configuration switch –disable-url-fopen-wrapper.

Finally, I implemented my function using CURL which can be used to connect and communicate to many different types of servers with many different types of protocols.

< ?php
function check_url($url) {
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, $url);
curl_setopt($ch, CURLOPT_HEADER, true);
curl_setopt($ch, CURLOPT_NOBODY, true);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_FOLLOWLOCATION, true);
curl_setopt($ch, CURLOPT_MAXREDIRS, 10); //follow up to 10 redirections - avoids loops

curl_exec ($ch);

$code = curl_getinfo($ch, CURLINFO_HTTP_CODE);
curl_close ($ch);

if ($code != 200 && $code != 302 && $code != 304) {
return false;
} else {
return true;
}
}
?>


Or

< ?php
function check_url($url)
{
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, "$url");
curl_setopt($ch, CURLOPT_HEADER, true);
curl_setopt($ch, CURLOPT_NOBODY, true);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_FOLLOWLOCATION, true);
curl_setopt($ch, CURLOPT_MAXREDIRS, 10); //follow up to 10 redirections - avoids loops
$data = curl_exec($ch);
curl_close($ch);
preg_match_all("/HTTP\/1\.[1|0]\s(\d{3})/", $data, $matches);
$code = end($matches[1]);
if (!$data) {
return 0; // "Domain could not be found";
} else {
if ($code == 200) {
return true; // "Page Found";
} elseif ($code == 404) {
return false; // "Page Not Found";
}
}
}
?>

And the following is the usage example:

< ?php
if (check_url("http://www.memezilla.com")) {
echo "URL is OK!";
} else {
echo "URL Error";
}
?>

Make Your Professional Quality Logos In Minutes

Written by coregps on Sunday, December 24th, 2006 in Web Design.

Click to create a professional logoLogoYes allows you to create a professional, custom logo for a fraction of the cost and time of traditional methods. Choose from over 20,000 unique symbols and a wide variety of fonts, colors and layout options. Thanks to an easy point & click process, you can create the perfect logo in no time.

The LogoYes process even includes a free Image CalculatorTM to help you pinpoint the best look and feel for your company’s image. In just minutes you can have your new logo on your desktop, ready for a multitude of marketing materials and your website — for a fraction of the cost of traditional methods.

Since your logo is often your customer’s first impression of your company, your logo should clearly and dynamically present your business. Don’t settle for something half-baked just because it’s low-priced. With LogoYes, you can afford a professional-quality logo, one that’s just right for you and your business, and you won’t have to sacrifice quality for affordability. No design experience is necessary.

Get a Madison Avenue quality logo on a Main Street budget with LogoYes. Our easy point & click process is based on the same basic process top design firms use, and our library of unique symbols were designed by award-winning graphic designers specifically for use in logo creation. Choose from tons of fonts, color and layout options as well. Essentially, the LogoYes logo maker combines professional graphic design expertise with the best source of information about your business and industry — you. No design experience is necessary.

Unlike logo maker software, you don’t have to download a thing or learn a new program. And you won’t be saddled with low-quality artwork or a limited, “cookie cutter” process. The award-winning team of graphic designers at LogoYes has created a library of over 20,000 unique logo symbols, each available in different illustrative styles and crafted to convey a specific image. You can create the perfect logo in minutes, get started now!.
Click Here

How to pass database as parameter in stored procedure

Written by coregps on Friday, December 22nd, 2006 in SQL Server.

I want to write a stored procedure which is used to merge many databases into a single one. This stored procedure contains one parameter named @db_name. It is something like this first:

CREATE PROCEDURE batchimport
@db_name varchar(20)
AS
INSERT INTO employee SELECT * FROM @db_name..employee
GO

But I always get the syntax error. It seems that the parameter can’t be used to pass field names, table names and database names. So I implement the new one using dynamically-generated SQL strings.

/*
Function: Merge many databases into a single one
Call: exec batchimport ‘database name’
*/

CREATE PROCEDURE batchimport
@db_name varchar(20)
AS

BEGIN
DECLARE @sql nvarchar(4000)

SELECT @sql = ‘INSERT INTO employee SELECT * FROM ‘ + quotename(@db_name) + ‘..employee’
EXEC sp_executesql @sql

END

GO

It should be noticed that the sp_executesql need a nvarchar data type on its parameter.

How to delete duplicate rows From an Oracle Table

Written by coregps on Monday, December 18th, 2006 in SQL Server, Oracle.

I have a Oracle table which has some duplicate rows after importing data from SQL Server twice. The following SQL statement can be used to delete the duplicate entries.

DELETE FROM table_name
WHERE rowid not in
(SELECT MIN(rowid)
FROM table_name
GROUP BY column1, column2, column3…));

The subquery is used to find out a unique rowid from all the rowid’s. It can be MAX or MIN, and the group by clause should include all the UNIQUE columns. The fields column1, column2, column3… constitute the identifying key for each record.

The the subquery returns one record for the dupliacte ID’s and I delete all of them that are not in the subquery and that deletes all the dupliacte rows from the database.

List all the Oracle keywords and reserved words

Written by coregps on Sunday, December 17th, 2006 in Oracle.

When I migrated from SQL Server to Oracle recently, I want to find all the Oracle keywords and reserved words. This can be done by executing the following SQL statement:

SELECT * FROM v$reserved_words;

Create your own favicon using FavIcon from Pics

Written by coregps on Wednesday, December 6th, 2006 in General.

favicon_from_pics.jpg

Chami.com provides a “FavIcon from Pics” service, which can be used to Generate FavIcon

from pictures of any size/resolution. Just select a picture for the “Source Image” and click “Generate FavIcon.ico”. After generate the favicon.ico file, you can test it in browser or download it.

After download the favicon.ico file, upload it to the theme folder you use currently. Mine is

/wp-content/themes/bluecrunch.

And then edit the Header template(header.php), add the following script:

<link rel=”shortcut icon” href=”<?php bloginfo(’template_directory’); ?>/favicon.ico” />

Save the changes, refresh your website, you can see your own favicon in the address bar.

How to drop all tables in Oracle

Written by coregps on Wednesday, December 6th, 2006 in Oracle.

We sometimes need to drop all tables in Oracle. If there are many tables in the database, it will be a terrible work. The easiest way is to write a simple small SQL*Plus script to retrieve all tables of a specified user and generate DROP statements. It is something look like this:

SQL> spool c:\drop_tables.sql
SQL> SELECT ‘DROP TABLE ‘ || table_name || ‘ CASCADE CONSTRAINTS;’ FROM user_tables;
SQL> spool off

Then we can execute the generated script “drop_tables.sql” like following:

SQL> @c:\drop_tables.sql

But be careful! Be sure to log on as the correct user!

If we login as sys or system, we can run this script:

spool c:\drop_all_tables.sql
select “drop table ” || table_name || ” cascade constraints;” from all_tables where owner = ‘User Name’
spool off



Site Navigation