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

What is the diffrence between IN and EXISTS.which one is faster.

3 Answers   Infogain,


Which one of the following is a reason that an INSERT statement might cause an error instead of executing correctly? 1. The INSERT statement was attempting to insert a record into a view that was created from more than one table. 2. The INSERT statement was attempting to insert a record using a combination of constants and values from an existing table. 3. The INSERT statement was attempting to insert a record with a non-NULL value into a table that has that column defined as NULL. 4. The INSERT statement was attempting to insert a record into a table by selecting a record from that same table. 5. The INSERT statement was attempting to insert a record into a view rather than a table.

1 Answers   Sonata,


What is the difference between alter trigger and drop trigger statements?

0 Answers  


What are % TYPE and % ROWTYPE ? What are the advantages of using these over datatypes?

4 Answers  


TABLE A TABLE B EMPNO ENAME EMPNO ENAME 1 A 1 A 2 B 2 B 3 C 3 C 4 D 4 D 5 E 5 E 6 F 7 G HOW TO GET THE UNMATCHED RECORDS IN SQL QUERY?

10 Answers   Satyam,






how are rank and dense rank being alloted for column with same values over a particular column

1 Answers  


Can we use SQL%ISOPEN in implicit cursors? Does this attribute works properly in Implicit Curosors?

3 Answers  


Show code of a cursor for loop.

0 Answers  


how to calcuate the second highest salary of he employee

13 Answers   Synechron,


what are the advantages of cursors than procedures?

2 Answers  


What is window clause?

1 Answers   TCS,


what is the difference between union and union all

14 Answers   IBM, Oracle, Luxoft,






Categories