i wrote a program to that takes tablename as input parameter and returns true or false based on table exist or not
the below code works differently when table has data and when table does not have data ? how to improve the below code to make sure my function
always retuns true if table exists and false if does not exist regardless of 0 records or more than one record
CREATE OR REPLACE FUNCTION is_tab_present_g (pi_tab_name IN VARCHAR2)
RETURN BOOLEAN
IS
row_cnt NUMBER := 0;
sql_stm VARCHAR2 (4000);
l_tab_name VARCHAR2 (4000);
BEGIN
l_tab_name := UPPER (TRIM (pi_tab_name));
sql_stm := 'select 1 from ' || l_tab_name || ' where rownum < 2';
DBMS_OUTPUT.put_line ('sql_stm: ' || sql_stm);
DBMS_OUTPUT.put_line ('row_cnt before ei: ' || row_cnt);
EXECUTE IMMEDIATE sql_stm
INTO row_cnt;
DBMS_OUTPUT.put_line ('row_cnt after ei: ' || row_cnt);
RETURN TRUE;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('in exception');
RETURN FALSE;
END is_tab_present_g;
/
create table chk_pk(x number);
my verification block
BEGIN
if is_tab_present_g('chk_pk') then
dbms_output.put_line('yessssss');
execute immediate 'drop table chk_pk cascade constraints';
else
dbms_output.put_line('nooo');
end if;
END;
/