Thursday, August 30, 2012

DDL in trigger

--create table usermeta(instime date, uname varchar2(20));
--insert into usermeta values(sysdate,'Aannd');
--insert into usermeta values(sysdate-4,'Yadav');
--insert into usermeta values(sysdate-32,'Prakash');

CREATE OR REPLACE PROCEDURE my_create AS
v_maxtime usermeta.instime%TYPE;
v_tbname usermeta.uname%TYPE;
v_Sqlstring VARCHAR2(200);
BEGIN
SELECT MAX(instime) INTO v_maxtime FROM usermeta;
SELECT uname INTO v_tbname FROM usermeta WHERE instime=v_maxtime;
DBMS_OUTPUT.PUT_LINE('Table Name:  '||v_tbname);
v_SqlString:='CREATE TABLE ' || v_tbname || '(' || v_tbname || 'Date TIMESTAMP, '
||v_tbname||'Values NUMBER)';
DBMS_OUTPUT.PUT_LINE('DDL Query:  '||v_sqlstring);
EXECUTE IMMEDIATE v_sqlstring;
END;
/


create or replace trigger create_ddl
AFTER INSERT ON usermeta
for each row
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
begin
--your statements
my_create;
commit;
end;
/

No comments:

Post a Comment