CREATE PROCEDURE create_user_done_table
(
v_user_name varchar(20)
) RETURN INTEGER
is
v_temp_table_name varchar2(50);
v_sqlstr varchar2(1000);
begin
v_temp_table_name:='temp_'+v_user_name+'_done_list'
BEGIN
select count(*) into my_count from tabs where table_anme =v_temp_table_name;
if my_count > 0 then
v_sqlstr := ' drop table '+v_temp_table_name
execute immediate v_sqlstr;
v_sqlstr :=' create Table '+v_temp_table_name+'(ID INT PRIMARY KEY, FileName varchar(255) Not NULL )';
execute immediate v_sqlstr;
end if
EXCEPTION
WHEN OTHERS THEN
P_ERROR := P_ERROR + 1;
RAISE_APPLICATION_ERROR(-20002,'create_user_done_table: Cannot Create User Temp Table'||SQLERRM);
RETURN 1;
END;
RETURN 0;
END;
/
(
v_user_name varchar(20)
) RETURN INTEGER
is
v_temp_table_name varchar2(50);
v_sqlstr varchar2(1000);
begin
v_temp_table_name:='temp_'+v_user_name+'_done_list'
BEGIN
select count(*) into my_count from tabs where table_anme =v_temp_table_name;
if my_count > 0 then
v_sqlstr := ' drop table '+v_temp_table_name
execute immediate v_sqlstr;
v_sqlstr :=' create Table '+v_temp_table_name+'(ID INT PRIMARY KEY, FileName varchar(255) Not NULL )';
execute immediate v_sqlstr;
end if
EXCEPTION
WHEN OTHERS THEN
P_ERROR := P_ERROR + 1;
RAISE_APPLICATION_ERROR(-20002,'create_user_done_table: Cannot Create User Temp Table'||SQLERRM);
RETURN 1;
END;
RETURN 0;
END;
/
RETURN INTEGER
is
v_temp_table_name varchar2(50);
v_sqlstr varchar2(1000);
my_count integer;
begin
v_temp_table_name:='temp_'+v_user_name+'_done_list'
BEGIN
select count(*) into my_count from tabs where table_name =UPPER(v_temp_table_name);
if my_count > 0 then
v_sqlstr := ' drop table '+v_temp_table_name ;
execute immediate v_sqlstr;
v_sqlstr :=' create Table '+v_temp_table_name+'(ID INT PRIMARY KEY, FileName varchar(255) Not NULL )';
execute immediate v_sqlstr;
end if;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20002,'create_user_done_table: Cannot Create User Temp Table'||SQLERRM);
RETURN 1;
END;
RETURN 0;
END;
/