Feeds:
Posts
Comments

Archive for the ‘Web development’ Category

So the other day I had to back up all database objects for an Oracle instance i manage and I was looking for some tool that would help me generate DDL for each object in a separate SQL file.

A quick Google search revealed I could backup DDL using TOAD (full version), DB Solo and RazorSQL. I use DBArtisan at work and my organization doesn’t want to move to TOAD yet. I have used DB Solo in the past and I have liked the clean and light interface and the quick ability to back up DDL but I just couldn’t use it this time because I had already expired my trial version and didn’t have enough time to get the folks sign off on purchase of a license (which is not very expensive). So I decided to try RazorSQL, but was disappointed because it dumps all objects in one single SQL file. Also, when I tried to back up stored procedures (over 5000 of them), the program refused to copy.

Out of luck and exhausted, I remembered the good old DBMS_METADATA commands. I quickly created a new directory entry in the database for the location where i wanted to dump my DDL, and executed a procedure that I wrote to perform the DDL backup.

Here are the steps I followed: (one can use SQL PLUS or any DB tool he wants)

–create a new directory in all_directories table. This is important to avoid ORA-29280 invalid_path execption.

CREATE OR REPLACE DIRECTORY BACKUP_DIR AS 'E:\DATA_BKP';

–also grant permissions to everyone to read, write (maybe restricted based on your requirement).

GRANT READ,WRITE ON BACKUP_DIR TO PUBLIC;

–the stored procedure for DDL backup
–note that we are reading from USER_OBJECTS data dictionary, this way it exports only the current user’s objects.
–to export all objects, execute procedure as sys or sysdba and use the table ALL_OBJECTS instead.

CREATE OR REPLACE PROCEDURE	EXPORT_DDL
AS
	V_DDL_CLOB  CLOB;
	VPATH VARCHAR2(255);
BEGIN
	FOR C IN (SELECT OBJECT_NAME, OBJECT_TYPE
             FROM USER_OBJECTS
             WHERE OBJECT_TYPE IN ('TABLE','VIEW','FUNCTION','PROCEDURE','PACKAGE','PACKAGE_BODY'))
	LOOP
		V_DDL_CLOB := DBMS_METADATA.GET_DDL(C.OBJECT_TYPE, C.OBJECT_NAME, 'EAGLE');
		DBMS_XSLPROCESSOR.CLOB2FILE(V_DDL_CLOB, 'BACKUP_DIR', C.OBJECT_TYPE || '_' || C.OBJECT_NAME||'.SQL');
	END LOOP;
END;
/

EXEC EXPORT_DDL;

Now that was simple, wasn’t it? 🙂

Advertisements

Read Full Post »

Now here is something that a programmer would have come across sometime or the other. Let us say you have a table where more than one columns make up the primary key and you need to find out all those rows which have two of these columns (attributes) same but the third attribute is different. You can do this using a simple join like this

SELECT * FROM Table1 A INNER JOIN Table1 B ON A.PK1=B.PK1 AND A.PK3=B.PK3 AND A.PK2 <> B.PK2

Read Full Post »

Hi,

I am a novice in the field of web designing and I have been developing some websites using ASP.Net recently. The environment was working fine for a few months when all of a sudden one fine day, I get this error when I try to run my solution: Internet Explorer can not display page. I tried everything under the roof that a novice can be expected of doing viz. re-installing VS 2008, re-starting the IIS and ASP services a couple of times, etc. I also started to dig into online forums and tried everything but in vain.

During one of my fruitless online searches, I stumbled upon this page: http://forums.asp.net/p/1235447/2247054.aspx

The page displays a lot of solutions and sadly none of them worked for me, but one posted by Arien. The solution was removing the next line in the windows host file C:\Windows\System32\drivers\etc\hosts

::1  localhost

This being a IPv6 entry, when commented works wonders.. 🙂

My ASP.Net is working fine again. How on earth in the first place this problem started I don’t know, but it is solved now and I am again enjoying working with ASP.

Hope this blog helps all those who have been facing similar problems.

Read Full Post »