How to Create Index on a table in Oracle

Here is the syntax to create an index on a table in oracle:

CREATE INDEX INDEX_NAME ON TABLE_NAME(COLUMN_NAME);

If you want to specify tablespace you can do it as follows:

CREATE INDEX INDEX_NAME ON TABLE_NAME(COLUMN_NAME)
TABLESPACE TABLE_SPACE;

Isn't that easy?

How to add more columns to an existing table with a simple SQL in Oracle

Most of you may know that you can add a column easily with Toad by going through add column wizard or Alter table wizard. Let's say if you have to add lots of columns to a table, I would rather write a simple sql code than to add columns through wizard. Here is the simple sql you can write for the same

Alter table_name add 

(column_a   datatype,

column_b   datatype,

column_c   datatype,

column_d   datatype,

....

)

You can add as many columns as you want in just one shot. Is' nt that useful??

                                    

Create a Table from another table/sql with/without copying data in Oracle

Sometimes we need to create a table from a specified pl/sql code or other table. Say we only want to create the table columns and not copy the data. Here is the sql code to create a table from another table along with data.

Create table table_name as Select * from table_a

You can substitute red part of the query to any sql query. This will create table with all the data in it. Now, I want to create the same table but without copying the data. It's actually very simple, you only have to add a where clause to restrict the row number to zero. This is how you can write it:

Create table table_name as Select * from table_a where 1=0

This could be useful when you have to create a table from different table columns. This automatically adapts datatypes of columns.

How to add ON DELETE CASCADE feature to a table/constraint when the foreign key constraint is already added?

Well, I figured out that we cannot add ON DELETE CASCADE to an existing constraint or already added foreign key constraint. You need to drop the constraint and recreate it with the ON DELETE CASCADE added to it. Here is how you can do it.

1.First drop the constraint.
2.Add the new constraint with ON DELETE CASCADE option.

Here is the example of syntax:

ALTER TABLE table_1
DROP CONSTRAINT constraint_1

This will drop the constraint.

ALTER TABLE table_1
DROP CONSTRAINT constraint_1 FOREIGN KEY(a) REFERENCES table_2(a)
ON DELETE CASCADE

This option will enable you to delete the child record automatically whenever the parent record is deleted.

Execute Stored Procedure in Informatica Pre SQL

There are times in practice when you may want to execute a stored procedure in pre sql of a session. For example: In my case, I wanted to truncate a table before loading data into it each time I run the workflow/mapping/session. I know you can truncate a table by selecting an option inside a session task(Target properties), but I was not able to do that because of limited privileges. The connection was not allowing me truncating a table. To resolve it, I got to call a stored procedure, which is written to truncate the related table. Now my job was to execute the stored procedure each time the session is run. There are 2 methods to do so

1) Create a dummy mapping and use the Stored Procedure transformation in it and run this mapping before my required mapping each time.

2) Write a pre sql to execute the stored procedure.

I chose option 2 as it takes less time and effort. So here is how it works:

• Open the session you want to edit.
• Click on the mappings tab.
• Click on the target properties.
• Go to the Pre-Sql.
• Write following command

          CALL SCHEMA_NAME.SP_NAME();
              OR
         CALL SP_NAME();

• Click OK and Save.

I have to use schema name as I was using a different schema/role in connection. Do not use 'EXEC' , instead use CALL and remember to use braces in the end. Picture might help :)

You can also use Post SQL depending on the needs and requirements. Just remember the syntax to call stored procedure.