How to call DDL statements from pl/sql?

Answers were Sorted based on User's Feedback



How to call DDL statements from pl/sql?..

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

How to call DDL statements from pl/sql?..

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

How to call DDL statements from pl/sql?..

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

How to call DDL statements from pl/sql?..

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

How to call DDL statements from pl/sql?..

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

Post New Answer



More SQL PLSQL Interview Questions

In pl/sql, what is bulk binding, and when/how would it help performance?

0 Answers  


Explain ddl statements in pl/sql?

0 Answers  


how to calculate expressions with sql statements? : Sql dba

0 Answers  


How many types of literals are available in pl sql?

0 Answers  


Is inner join same as self join?

0 Answers  






What is the difference between numeric and autonumber?

0 Answers  


How does a trigger work?

0 Answers  


What are the different parts of a package?

0 Answers  


Differentiate between pl/sql and sql?

0 Answers  


Why is there a need for sqlcode and sqlerrm variables?

0 Answers  


how to concatenate two character strings? : Sql dba

0 Answers  


How to create a view on a table which does not exists

4 Answers   TCS, Oracle,






Categories