generic stored procedures in oracle

Question

i want to write a generic stored procedure in oracle .For example i want to take table name as input and then do maipulations on it. I want to learn some sample generic codes and the basica of writing generic stored procedures in oracle. Can any one provie code snippets/ links to websites or other material for this?

Answer 1

Well you'll need the EXECUTE IMMEDIATE statement for sure.

Answer 2

EXECUTE IMMEDIATE does what you want.

Answer 3

Generic = dynamic SQL, either "Native Dynamic SQL" (Execute Immediate) or the DBMS_SQL package. http://download.oracle.com/docs/cd/B19306%5F01/appdev.102/b14251/adfns%5Fdynamic%5Fsql.htm#i1006546

Ordinarily SQL statements are parsed when the procedure is compiled, however this is not possible if the table name is not known -- the table may of course not even exist at compile time.

Here are links to documentation on the topic, with examples.

http://download.oracle.com/docs/cd/B19306%5F01/appdev.102/b14261/dynamic.htm#LNPLS011

http://download.oracle.com/docs/cd/B19306%5F01/appdev.102/b14251/adfns%5Fdynamic%5Fsql.htm#ADFNS008

A word of caution -- do not use dynamic SQL if you can use static SQL. The flexibility comes at a price.