India state city district database

Friday, 21 February 2014, 0:19 | Category : Database, Technology
Tags :

We had a requirement of India’s state, city/districts (with PIN code) database in one of our application. We searched on google, but could not find any good source of such database. So, we finally worked upon it and create our own set of database. Sources: Census of India, 2011 All India Pincode directory You can […]

Substitute of LIMIT clause (as used in MySQL) in O…

Tuesday, 3 July 2007, 18:46 | Category : Database, Technology
Tags :

Substitute of LIMIT clause (as used in MySQL) in Oracle: SELECT COL_NAME (SELECT COL_NAME, ROWNUM r from TABLE_NAME) WHERE r BETWEEN 10 AND 15 It will select 6 rows (10th to 15th inclusive)

How to create a new user on oracle

Friday, 8 June 2007, 1:43 | Category : Database, Technology
Tags :

create user newUser identified by userPassword; grant create session to newUser_ grant create table to newUser; GRANT create view TO newUser; GRANT CREATE SEQUENCE TO newUser; GRANT CREATE PROCEDURE TO newUser; GRANT CREATE ANY TRIGGER TO newUser; GRANT CREATE ANY TYPE TO newUser; alter user newUser quota unlimited on users;

How to schedule jobs in Oracle

Friday, 8 June 2007, 1:41 | Category : Database, Technology
Tags :

BEGIN DBMS_SCHEDULER.DROP_JOB (‘TEST_JOB’); END; / BEGIN DBMS_SCHEDULER.CREATE_JOB ( job_name => ‘TEST_JOB’, job_type => ‘PLSQL_BLOCK’, job_action => ‘UPDATE TABLE tblName set value=5;’, start_date => sysdate, repeat_interval => ‘FREQ=HOURLY; INTERVAL=1’, /* every one hour */ enabled => TRUE, comments => ‘Test_JOb’); END; / BEGIN DBMS_SCHEDULER.ENABLE (‘TEST_JOB’); END; /

How to send email from oracle stored procedure

Friday, 8 June 2007, 1:40 | Category : Database, Internet, Technology
Tags : ,

BEGIN UTL_MAIL.SEND ( sender => ‘[email protected]’, recipients => ‘rst@yahoo.’, subject => ‘test Oracle’, message => ‘testing orcl’); END; /

How to drop everything on a oracle schema

Friday, 8 June 2007, 1:39 | Category : Database, Technology
Tags :

BEGIN FOR cur_rec IN (SELECT table_name, constraint_name FROM user_constraints WHERE constraint_type = ‘R’) LOOP EXECUTE IMMEDIATE ‘ALTER TABLE ‘ || cur_rec.table_name || ‘ DROP CONSTRAINT ‘ || cur_rec.constraint_name; END LOOP; FOR cur_rec IN (SELECT object_name, object_type FROM user_objects) LOOP BEGIN EXECUTE IMMEDIATE ‘DROP ‘ || cur_rec.object_type || ‘ ‘ || cur_rec.object_name; EXCEPTION WHEN OTHERS THEN […]

Selecting random rows from an oracle table

Friday, 8 June 2007, 1:20 | Category : Database, Technology
Tags :

SELECT * FROM (SELECT * FROM TEST_TABLE ORDER BY dbms_random.value) WHERE rownum <= 5; This will give 5 random rows from table.

Debugging problems related to the JDBC API

Thursday, 26 April 2007, 0:57 | Category : Database, J2EE, Java, Technology
Tags :

A good way to find out what JDBC calls are doing is to enable JDBC tracing. The JDBC trace contains a detailed listing of the activity occurring in the system that is related to JDBC operations. If you use the DriverManager facility to establish your database connection, you use the DriverManager.setLogWriter method to enable tracing […]