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;
  }

    Why don't you query the INFORMATION_SCHEMA directly?

    Chris

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

        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;
  }

          select table_name from tabs where table_name = 'blah'

            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! 😃

              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!!

                Write a Reply...