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

Post New Answer



More SQL PLSQL Interview Questions

what is Hash join?how it is different from inner join?what is the sign used for inner join?(eg: like the (+) sign used for outer join)?

2 Answers   TCS,


what are the differences among these table level lock modes - IN SHARE MODE, IN SHARE UPDATE MODE, IN EXCLUSIVE MODE ?

3 Answers   HCL,


write a procedure to print a statement or number not using "dbms_output.put_line" package.write a procedure instead of it using procdure name as "print" ex:- declare a number:=2; begin print(a); end; /* when U type above procedure 2 have to should be printed*/

1 Answers   iFlex,


What is the maximum number of triggers,can apply to a single table?

2 Answers  


----> There is a table T with two columns C1 and C2. The data is as below: C1 C2 1 4 2 5 3 6

3 Answers   ICICI,






declare l1 number := null; l2 number :=null; begin if l1=l2 then message('equal'); else if l1<>l2 then message('not equal'); else message('else'); end if; end if; end; What will be the output ?

7 Answers   Oracle,


Give an example of any procedure.

5 Answers   Wipro, Accenture, iFlex,


What is the difference between the Primary and Foreign key?

7 Answers   Cap Gemini,


sales persons should always receive commission of 100 at least. employees who r not sales persons should never receive commission.(Triggers)

1 Answers   Oracle, Fidelity,


What is the different between Stored Procedure and Procedure?

7 Answers   Informatica, MIS,


Explain the usage of WHERE CURRENT OF clause in cursors ?

4 Answers   Satyam,


What is difference between CHAR and VARCHAR2?What is the maximum SIZE allowed for each type?

6 Answers   Saama Tech,






Categories