DYNAMIC SQL

create or replace procedure create_table(table_name varchar2,col_spaces varchar2)is
begin
execute immediate 'create table '||table_name||'('||col_spaces||')';
end;
                           
                                 ================================

create or replace procedure drop_table(oname varchar2,ot varchar2) is
begin
execute immediate 'drop table '||' '||oname||'  '||ot;
end;

execute create_table('alamin','id number,name varchar2(30)');

execute drop_table('alamin','purge');

                                  ==================================

CREATE FUNCTION annual_sal(emp_id NUMBER)
RETURN NUMBER IS
plsql varchar2(200) :'DECLARE '||' emprec employees%ROWTYPE; '||'BEGIN '||' emprec := get_empp(:empid); ' ||
' :res := emprec.salary * 12; ' ||
'END;';
result NUMBER;
BEGIN
EXECUTE IMMEDIATE plsql
USING IN emp_id, OUT result;
RETURN result;
END;

                                 ===================================


create or replace function del_rows(table_name varchar2)
return number is
begin
execute immediate 'delete from'||table_name;
return sql%rowcount;
exception
when OTHERS then
return sqlerrm;
end;

begin
dbms_output.put_line(del_rows('MESSAGES1'||'row deleted'));
end;

                                ====================================

CREATE  or replace FUNCTION get_emp(emp_id NUMBER)
RETURN employees%ROWTYPE IS
stmt VARCHAR2(200);
emprec employees%ROWTYPE;
BEGIN
stmt := 'SELECT * FROM employees ' ||'WHERE employee_id = :id';
EXECUTE IMMEDIATE stmt INTO emprec USING emp_id;
RETURN emprec;
END;




create or replace procedure add_col(table_name varchar2,col_spaces varchar2) is
stmt varchar2(200):='alter table '||table_name||' add '||col_spaces;
begin
execute immediate stmt;
end;


execute add_col('alamin1','new varchar2(30)');

                                    ====================================

create or replace procedure modify_col(table_name varchar2,col_spaces varchar2) is
stmt varchar2(200):='alter table '||table_name||' modify '||col_spaces;
begin
execute immediate stmt;
end;

                                      ===================================

create or replace procedure add_row(table_name varchar2,
col_values varchar2,cols varchar2)
is
stmt varchar2(200):='insert into '||table_name;
begin
if cols is not null then
stmt:=stmt||' ('||cols||')';
end if;
stmt:=stmt||' values ('||col_values||')';
execute immediate(stmt);
end;

                                        ===================================

create or replace procedure insert_row (table_name varchar2,
id number,name varchar2,
place varchar2,new varchar2) is
begin
execute immediate 'insert into '||table_name||' values (:1,:2,:3,:4) ' using id,name,
place,new;
end;

execute insert_row('alamin1',10,'alamin','dhaka','no_new');

                                         ===================================

CREATE  or replace PROCEDURE insert_table (
table_name  VARCHAR2,deptnumber  NUMBER,deptname    VARCHAR2,location    VARCHAR2) IS
stmt_str    VARCHAR2(200);
BEGIN
stmt_str := 'INSERT INTO ' ||table_name || ' values (:deptno, :dname, :loc)';
EXECUTE IMMEDIATE stmt_str
USING
deptnumber, deptname, location;
END;

execute insert_table('alamin1',101,'alamin','dhaka');


                                         =====================================

declare
a number;
b varchar2(1) := 'c';
c varchar2(2) := 'XX';
d varchar2(2) := 'YY';
e varchar2(2);
begin
if a is null then
b := 'c';
else
b := 'd';
end if;
execute immediate 'select :x from dual' INTO e USING b;
dbms_output.put_line(e);
end;

                          ==========================================

create or replace procedure del_row(table_name varchar2,condition varchar2)
is
stmt varchar2(200):='delete from '||table_name;
begin
if condition is not null then
stmt:=stmt||' where '||condition;
end if;
execute immediate(stmt);
end;

execute del_row('alamin','id=11');

execute del_row('alamin','name=''labony''');