Archive for the ‘Database’ Category

How to convert MySQL to MySQLi procedural methods

These are nothing but the APIs of PHP that is used to access the MySQL databases and tables. The developers can choose either one of them for their project, however, the use of MySQL extension is deprecated and will not be available in future versions. It is recommended to use the MySQLi extension with PHP 5.5 and above.

Let’s have some more information about each of them:

MySQL: This was the main extension that was designed to help PHP applications send and receive data from the MySQL database. However, the use of MySQL has been deprecated and removed as of PHP 7 and its newer versions. This is why it is not recommended for new projects, and that’s the reason why MySQLi extensions are used more nowadays.
MySQLi: The ‘i’ in MySQLi stands for Improved. Therefore, this is also known as the improved version of MySQL. It has many useful features.

  • An Object-oriented interface
  • Support for prepared statements
  • Support for multiple statements
  • Support for transactions
  • Enhanced debugging capabilities
  • Embedded server support.

Read more »

DROP multiple TABLE / TRIGGER / FUNCTON in oracle using PL SQL

To drop more than one tables having a matching criteria then we should use a PL SQL statement, in oracle there is a table called USER_OBJECTS having name of all the tables for that particular user.

DROP TABLE is a DDL statement so that we need EXECUTE IMMEDIATE privilege for dropping table from a script, more easy way to drop table is to generate DROP TABLE command using a script and run that command.

The following PL SQL script generate drop table command for all tables having table name contains  word 'test'


  object_name varchar2(100);
  cursor cur1 is SELECT object_name FROM user_objects WHERE object_type='TABLE' AND object_name LIKE '%test%';

  open cur1;
        fetch cur1 into object_name;
        exit when cur1%notfound;
      dbms_output.put_line('DROP TABLE ' || object_name || ';' );
    end loop;
    close cur1;



anonymous block completed


Read more »

Find table names and column names inside a database using oracle

Display all table names in a database

select * from tab;

Display number of tables in a database

select count(*) from tab;

Find table exits in a database

select * from tab where tname like '%STUDENT%';

Display all column names in a database

select * from USER_TAB_COLUMNS;

Display number of columns in a database

select count(*) from USER_TAB_COLUMNS;

Find column name exits in a database

select * from USER_TAB_COLUMNS where COLUMN_NAME like '%STUDENT_ID%';

Find column name exits on a table in a database

select * from USER_TAB_COLUMNS where TABLE_NAME like '%STUDENT%' and COLUMN_NAME like 'STUDENT_ID';


Read more »

Last updated by at .