Archive for the ‘Oracle’ Category

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'

SET SERVEROUTPUT ON;

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

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

END ;

Output

anonymous block completed

DROP TABLE USER_TEST;

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 »

Oracle SQL*Loader 4 – Load data using SQLLDR command

Now we have two files and one table named STUDENT_DATA

The Data file         : std_data.dat
The Control file     : std_control.ctl

If you didn't have the two files then read this post –> Oracle SQL*Loader 2 – Data and Control File

Place the data and control file inside the directory "D:\A5518\Zzz\SQL Loader". Open command prompt and navigate to "D:\A5518\Zzz\SQL Loader" directory then enter the following command.

The command used to initiate this load needs to invoke SQL*Loader and point it to the control file describing the data. In this case, the input file name is not provided in the control file, that name needs to be passed in on the command line as well. The following sqlldr command will do the job:

sqlldr <username>/<password> CONTROL=<control_file_name> DATA=<data_file_name> LOG=<log_file_name> DISCARD=<discard_file_name> BAD=<bad_file_name>

or

sqlldr jijo/kjose CONTROL=std_control DATA=std_data LOG=std_log DISCARD=std_discard BAD=std_bad

Example

D:\A5518\Zzz\SQL Loader>sqlldr jijo/kjose CONTROL=std_control DATA=std_data LOG=std_log DISCARD=std_discard BAD=std_bad

SQL*Loader: Release 11.2.0.2.0 – Production on Mon Aug 5 17:05:14 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached – logical record count 9
Commit point reached – logical record count 10

D:\A5518\Zzz\SQL Loader>

Read more »