check if table exists - ORACLE

[sharul23]

Hi everyone,
i just join this forum today and found that lots of knowledge i can get from here. awesome!! 😃 and, hopefully u guys can help me with this problem.. (sorry for my improper english too)

i'm using PHP to develop a site and my d/base is ORACLE. One of the function is to check the existance of a table. If found, then i should run a script to drop that table. This is kind of new thing to me..

this is what i do currently;

function drop_table($conn,$tbl_name){ 
	$del="y";
	if exists(select * from $tbl_name) {
	  //drop table $tbl_name
	  $strSQL = "drop table $tbl_name";
	  $stmt = ociparse($conn,$strSQL);
  	  if(!ociexecute($stmt)){$del="n";}
	}
	return $del;
  }

[chriskl]

Why don't you query the INFORMATION_SCHEMA directly?

Chris

[jmcneese]

<sql>
DROP TABLE IF EXISTS tablename
</sql>

[sharul23]

thank you for ur responses..

chriskl,
sorry.. i don't understand what INFORMATION_SCHEMA is.. i am a newbie in php, could u please eloborate?

jmcneese,
i've tried that too.. but this error comes out;

SQL> drop table if exists test2;
drop table if exists test2
*
ERROR at line 1:
ORA-00933: SQL command not properly ended

however, i believe i have got the solution now.. but perhaps there are other better ways..

this is my solution;

  function drop_table($conn,$tbl){ 
	$drop = "n";
	$sql="select table_name from user_tables where table_name='$tbl'";
    list($rows,$cols) = count_record($conn,$sql);

if($rows>0){  //table is exists
  $strSQL2 = "drop table $tbl";
  $stmt1 = OCIParse($conn, $strSQL2);
  OCIExecute($stmt1);
  $drop = "y";
  echo "strSQL2: $strSQL2<br>";
} else {
  $drop = "n";
}

return $drop;
  }

[Bunkermaster]

select table_name from tabs where table_name = 'blah'

[sharul23]

Originally posted by Bunkermaster
select table_name from tabs where table_name = 'blah'

select table_name from cat where table_name = 'blah'
--> this works too!

thanks! 😃

[sharul23]

Hi,
I have another problem regarding renaming table..

One of my programme (PHP) is to rename a table on certain time, but problem occurs when i want to execute the program, if there is other people accessing the same table on the same time, i cant rename the table.

This is what i do now...

function rename_table($conn,$curr_table,$new_table){ 
	$strSQL  = "BEGIN ";
	$strSQL .= "LOCK TABLE $curr_table IN EXCLUSIVE MODE;";
	$strSQL .= "alter table $curr_table rename to $new_table;";
	$strSQL .= "END;";

$stmt = ociparse($conn,$strSQL);
ociexecute($stmt);
commit($conn);
  }

But it doesn't work!! 🙁

And, just found from a site..

begin
  $sql = "alter table $curr_table rename to $new_table"
  execute immediate sql;
end

same.. doesn't work!

Please help me!!