fbpx

Oracle

loadjava: Loading Java Sources/Classes/Jars into oracle Database

Loading the Java Sources/Classes into Database is important thing in Java based application software. Java sources (jar/Classes) can be loaded into database by using few methods, and using loadjava command is one of them. loadjava:This is OS based utility tool by Database. This command will be available in the OS. This can be run by …

loadjava: Loading Java Sources/Classes/Jars into oracle Database Read More »

get tables and their number of rows in Oracle Database Schema

Simplest way is to use the data dictionaries provided by Oracle. And you can depend on the results if the schema is up to date with the statistics. Use the following query to get the table list and their number of rows (records). SELECT table_name, num_rows FROM user_tables; OR SELECT table_name, num_rows FROM all_tables WHERE …

get tables and their number of rows in Oracle Database Schema Read More »

expdp : Exporting datadump in Oracle 11g

The export can be done in two ways from local machine from Server From Local MachineFor Windows operating system Follow the steps# create directorycreate directory dmpdir as ‘c:/temp’;select directory_name,directory_path from dba_directories; # expdp ownercreate user scott identified by tiger;grant connect, resource to scott; grant read, write on directory dmpdir to scott;expdp scott/[email protected] directory=dmpdir dumpfile=scott.dmp logfile=expdp.log …

expdp : Exporting datadump in Oracle 11g Read More »

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE in PLSQL

In Big/complex applications, it is essential to call Sub-programs (Procedures/Functions) from other sub-programs. In that case it will be difficult to trace out Errors/Exceptions from where it is raised i.e. which sub-program caused the exception. Normally we can use SQLCODE and SQLERRM to get the error/exception, but these may not work in the above case. …

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE in PLSQL Read More »

Histograms in Oracle for Table Columns

Histogram:A histogram is a special type of column statistic that provides more detailed information about the data distribution in a table column. Purpose of Histograms:By default the optimizer assumes a uniform distribution of rows across the distinct values in a column. For columns that contain data skew (a nonuniform distribution of data within the column), …

Histograms in Oracle for Table Columns Read More »

Finding characters in a string (VARCHAR-VARCHAR2) column

Compile the following Function in Oracle Database by copying it and Pressing F5 or F9 create or replace function test_num (v_card varchar2) return varchar2 is v_num number; begin select v_card into v_num from dual; return 1; exception when others then  return v_card; –DBMS_OUTPUT.PUT_LINE(v_card); –DBMS_OUTPUT.PUT_LINE(SQLCODE); –DBMS_OUTPUT.PUT_LINE(SQLERRM); end test_num; Now fire the query as follows to find …

Finding characters in a string (VARCHAR-VARCHAR2) column Read More »

Difference between REPLACE and TRANSLATE in SQL?

Replace will replace the character or string with the given character/string for every occurrence of the character/string.Translate will replace the character with another character given not by the string/multiple characters Case 1: SELECT REPLACE(‘Jack and Jue’,’J’,’Bl’) replace_str,TRANSLATE(‘Jack and Jue’,’J’,’Bl’) translate_strFROM DUAL; Case 2: SELECT REPLACE(‘Jack and Jue’,’J’,”) replace_str,TRANSLATE(‘Jack and Jue’,’J’,”) translate_strFROM DUAL;

Scroll to Top