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 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)
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;
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; /
BEGIN UTL_MAIL.SEND ( sender => ‘[email protected]’, recipients => ‘rst@yahoo.’, subject => ‘test Oracle’, message => ‘testing orcl’); END; /
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 […]
SELECT * FROM (SELECT * FROM TEST_TABLE ORDER BY dbms_random.value) WHERE rownum <= 5; This will give 5 random rows from table.
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 […]