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 »
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 »
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/tiger@xe directory=dmpdir dumpfile=scott.dmp logfile=expdp.log …
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. …
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), …
We will get this when we are trying to update a row in a table, for which there will be two or more corresponding records. Table 1: tc11 tcl2 t1 a t2 b t3 c t4 d Table 2: tc21 tc22 b1 a b2 b b3 c b4 d Table 3: tc31 tc32 t1 b1 …
ORA-30926: unable to get a stable set of rows in the source tables Read More »
We may get this error, when we try to assign a value to a variable which is bigger than the maximum size of the variable.Find the following code for example: DECLARE v_data VARCHAR2(1); BEGIN v_data := ‘YES’; END; / Error starting at line : 1 in command –DECLAREv_data varchar2(1);BEGINv_data := ‘YES’;END;Error …
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 »
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;
