Oracle : Truncate Table in Procedure

DDL and commands like TRUNCATE are not allowed in Oracle PL/SQL blocks. However, there is a workaround. Native dynamic SQL with the EXECUTE IMMEDIATE command will do the trick. Here is an example:

create procedure MyProc
as
begin
 execute immediate 'truncate table MyTable';
end;

The SQL is executed as if from a host application. Pretty much anything you can run from a SQL prompt can be run from inside a PL/SQL block this way. Because running dynamic native SQL executes a string containing SQL, PL/SQL suddenly becomes very flexible. Dynamic native SQL in Oracle allows you to combine the benefits of a host language with the benefits of stored procedures. Here's an example of the previous procedure changed to include a parameter for the name of the table name to be truncated:

create procedure MyProc (pTable in varchar2)
as
begin
 execute immediate 'truncate table ' || pTable;
end;

Connected v/s Unconnected Transformation

Tips :

want to know the use of unconnected transformation.

Why shd we use unconnected transformation, if we can achieve same with connected one?

Which gives better performance, connected or unconnected and why?

Answers 1 :
Depending upon the scenario we use the conn and unconn tr..

for e.g
In u r mapping u require same lkp multiple times,creating lkp multiple times put it in flow is not a good idea..which occupies more cache ...
Instead of that create one unconnectes lkp use multiple times where ever u want ..which very fast..

Answers 2:
u can use conn lookup but if u need 1 row so go for unconn lookup coz u can
use it multiple time in same mapping from multiple expression trn.
i hope it will help to u

Benefit :
The benefit of an unconnected lookup is that you can do conditional lookups (using IIF,DECODE, etc.). In other words, you can control when the lookup is used with in unconnected lookup.

unconnected lookup gives better performance because it's not connectd to mapping pipeline.
so when data transform is very fast compare to connected transformation.

unconnected look up can be used when you need only one port to be
returned from the look up table or if the same is to be used
many times in the mapping. and moreover unconnected lookup gives
better performance as it just acts as a function call. but if
more ports has to be returned from look up table we must use a
connected look up

Distinct & duplicate records to 2 different targets

Problem :
source is flat file which contains duplicates,we have 2 targets in the mapping,we need to pass unique records to one target & records which is repeating(duplicates) to another target.
can anyone please tell how we can achieve this.

Solutions 1:
Sort the data. Create a field in an Expression that counts the duplicate rows by comparing it to the previous row. Use a Router with two groups: where count=1 and where count >1.

Solution 2 :
put an aggregator transformation and do group by all ports and add a count(*) function in another output port and based on count(*)>1 or =1 you can use router to divert the distinct and duplicate records.

Solutions 3:
u can also pass all duplicate records.
by following these steps:

1. take sql qualifier with sorted input checked on.
2.pass all rows to aggregator , n do group by all
ports, and take on port (count(*))
3. take another sql qualifier ,
4. use joiner with full outer join n join 2 & 3. with
condition (port matching)
5. take 2 filters to separate duplicate row & distinct
rows.. based on condn: count(*)>1 n count(*)=1 and
pass it to respective targets.

Data Mart Migration from sql server to oracle Db

Tips

If you have foreign key constraints on your fact table pointing to your dimension tables, you'll need to load the dimesnion tables first, then load the facts.

bind: (WSAEADDRINUSE)Address already in use

bind: (WSAEADDRINUSE)Address already in use

Problem :
when i initialize the debugger for a session the debugger log is giving the following message :
"bind: (WSAEADDRINUSE)Address already in use"

Solve : Change the PORT No
click--->Tools-->ooptions-->click on Debug tab and below TCP/IP check pick a port number between Min and Max automatically
Min: 5001 Max:32000

Are y using any kinds of chatting services like Yahoo messenger. Close them.
This means that ur port number is already in use by
another application.
try using ps -ef in the server and kill any application that is using that port.
Then try running the application. If it still does not run
then just log off and log in the repository again. It
should work.