How to call DDL statements from pl/sql?

Answer Posted / pavan_1981

One can call DDL statements like CREATE, DROP, TRUNCATE,
etc. from PL/SQL by using the "EXECUTE IMMEDATE" statement.
Users running Oracle versions below 8i can look at the
DBMS_SQL package .
begin
EXECUTE IMMEDIATE 'CREATE TABLE X(A DATE)';
end;
NOTE: The DDL statement in quotes should not be terminated
with a semicolon.

Another way is One can also use the older DBMS_SQL package
(V2.1 and above) to execute dynamic statements. Look at
these examples:
CREATE OR REPLACE PROCEDURE DYNSQL AS
cur integer;
rc integer;
BEGIN
cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cur, 'CREATE TABLE X (Y DATE)',
DBMS_SQL.NATIVE);
rc := DBMS_SQL.EXECUTE(cur);
DBMS_SQL.CLOSE_CURSOR(cur);
END;
/


Is This Answer Correct ?    28 Yes 4 No



Post New Answer       View All Answers


Please Help Members By Posting Answers For Below Questions

What is a schema sql?

533


Is sql a case sensitive language?

524


what is a foreign key ? : Sql dba

614


How do sql triggers work?

556


How global cursor can be declare with dynamic trigger ?

1756






what are the drivers in mysql? : Sql dba

563


what is oltp (online transaction processing)? : Sql dba

538


Differentiate between pl/sql and sql?

621


What is sql indexing?

559


What is difference between cursor and trigger?

564


what is a constraint? : Sql dba

716


what is meant by urlencode and urldocode? : Sql dba

604


what is the stuff function and how does it differ from the replace function? : Sql dba

626


How to get each name only once from an employee table?

616


Is sql a backend?

551