In this article i am going to explain how to
create dynamic sql in pl/sql.

What is Dynamic Sql?

Dynamic SQL is the ability to build and run SQL statements on the fly.

Benefits of Dynamic Sql:

You can include DDL statements within your PL/SQL programs.

You can write code that adjusts to table redefinitions.

You can enable stored programs to support various user inputs.

See this below examble,

PROCEDURE create_sequence
( sequence_name           IN     VARCHAR2) IS

–– Define local variable.
statement           VARCHAR2(2000);

–– Define local function to find a sequence.
FUNCTION verify_not_sequence
( sequence_name_in      IN     VARCHAR2)
RETURN BOOLEAN IS

–– Defines default return value.
retval                  BOOLEAN := TRUE;

–– Cursor returns a single row when finding a sequence.
CURSOR find_sequence IS
SELECT   null
FROM     user_objects
WHERE    object_name = sequence_name_in;

BEGIN

–– Sets the Boolean when it finds a sequence.
FOR i IN find_sequence LOOP
retval := FALSE;
END LOOP;

–– Return Boolean state.
RETURN retval;

END verify_not_sequence;

BEGIN

–– If sequence does not exist create it.
IF verify_not_sequence(sequence_name) = TRUE THEN

–– Build dynamic SQL statement.
statement := ‘CREATE SEQUENCE ‘||sequence_name||CHR(10)
|| ‘  INCREMENT BY   1′             ||CHR(10)
|| ‘  START WITH     1′             ||CHR(10)
|| ‘  CACHE          20′            ||CHR(10)
|| ‘  ORDER’;

–– Use NDS to run the statement.
EXECUTE IMMEDIATE statement;

–– Print successful output message.
dbms_output.put_line(
‘-> NetProgrammingHelp_tutorial.create_sequence’);

–– Print output break.
dbms_output.put_line(sline);

–– Print sequence created.
dbms_output.put_line(
‘Created Sequence <’||sequence_name||’>’);

ELSE

–– Print module name output message.
dbms_output.put_line(
‘-> NetProgrammingHelp_tutorial.create_sequence’);

–– Print output line break.
dbms_output.put_line(sline);

–– Print output message.
dbms_output.put_line(
‘Sequence <’||sequence_name||’> already exists’);

END IF;

END create_sequence;

Form this above examble we created sequence using dynamic
sql at run time.

Now,i hope its will help you to understand the inteligence of
dynamic sql.

Happy Coding.