Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

cheking table exists in a program

user650888Feb 3 2012 — edited Feb 7 2012
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;
/
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 6 2012
Added on Feb 3 2012
7 comments
191 views