How to call DDL statements from pl/sql?
Answers were Sorted based on User's Feedback
Answer / 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 |
Answer / santosh kumar
declare
v_ddl_stat varchar2(200):='create table '||'&table_name'||'
('||'&column_names'||')'; --col_name like(eid number(5),name
varchar2(10))
begin
EXECUTE IMMEDIATE v_ddl_stat;
END;
/
Is This Answer Correct ? | 8 Yes | 4 No |
Answer / oracle_tigress
for this question when i answered as we can user package
DBMS_DDL package it was correct..let me know whether it is
write or not..
Is This Answer Correct ? | 4 Yes | 3 No |
Answer / parag tyagi
CREATE OR REPLACE PROCEDURE emp_test( in_name VARCHAR2) IS
cnt NUMBER;
BEGIN
EXECUTE IMMEDIATE ('grant create table to user_name');
EXECUTE IMMEDIATE ('create table ' || in_name || '(name
varchar2(10))');
SELECT '1' INTO cnt FROM User_Objects WHERE object_name
= 'EMP_TEST';
IF cnt IS NOT NULL THEN
dbms_output.put_line('Table Created');
END IF;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Name is already used by an existing
object');
END;
Is This Answer Correct ? | 6 Yes | 6 No |
Answer / kavitha nedigunta
set serveroutput on;
DECLARE
CNT NUMBER;
table_name varchar2(300) := 'testnew';
BEGIN
--EXECUTE IMMEDIATE ('grant create table to user_name');
EXECUTE IMMEDIATE ('create table '||table_name||'(name
varchar2(10))');
SELECT COUNT(*) INTO CNT FROM USER_OBJECTS WHERE OBJECT_NAME = upper(''||table_name||'');
IF cnt > 0 THEN
dbms_output.put_line('Table Created');
END IF;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Name is already used by an existing object');
END;
Is This Answer Correct ? | 0 Yes | 0 No |
Is left join and outer join same?
what is oracle sql,pl/sql with interfaces
What do you understand by exception handling in pl/sql?
Does inner join remove duplicates?
I have a procedure in a procedure. The inner procedure contains out parameter. How I can call the inner procedure in the out procedure and send the inner procedure parameter value(out parameter value) into out procedure?
What is the most common sql injection tool?
How to update salary of employees department wise?
What is procedure and function?
What are the two characteristics of a primary key?
how to concatenate two character strings? : Sql dba
can a stored procedure call itself or recursive stored procedure? How much level sp nesting is possible? : Sql dba
How to create an array in pl/sql?