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:
CALL SCHEMA_NAME.SP_NAME();
OR
CALL SP_NAME();
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.
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 :)
Good information. Thank you for sharing
ReplyDeleteWhat is difference between CALL and EXEC while running a Stored Procedure?
ReplyDeleteHello Rakesh,
DeleteThe main difference between EXEC and CALL command is that EXECUTE is a SQL*Plus command whereas CALL is a pure SQL command.
- in SQL*Plus, you can use both CALL or EXEC
- in pure SQL (eg. through JDBC), you can use only CALL
In Informatica here, we are using SQL, so CALL works but EXEC does not.
Let me know if you have any other questions.
Hi Aaradhana,
DeleteCALL StoredProcedurename(); It doesn't work in Pre SQL. I tried specifying just the Stored Procedure name and it works.
Glad to know that just using SP name worked for you. For me it was not working that way.
Deleteobrigado aaradhana singh
DeleteHi Aradhana,
ReplyDeleteThanks for the tip. Although in my case, the the below piece of code worked
begin SCHEMA_NAME.STORED_PROCEDURE_NAME\;
end\;
CALL or just the stored procedure name didn't work.
This comment has been removed by the author.
ReplyDeleteHi Aradhana,
ReplyDeleteCould you please let me know what is pre-session stored procedure? Kindly explain with an joiner trn, informatica example.
Your support is highly appreciated.
Hi,
ReplyDeletecould you please let me know How to call multiple stored procedure in postsql workflow manager.
Your support is highly appreciated.
Style & Beauty You completed a number of nice points there. I did a search on the issue and found nearly all people will have the same opinion with your blog.
ReplyDelete