Java7
Posts tagged sql
Oracle show tables
Apr 23rd
The ‘SHOW TABLES’ is a useful command in MySQL that does not really work in or for Oracle DBMS. Here are two ways to show tables in your schema or username.
You may choose from either of the two steps below to retrieve information of your schema.
1. select * from dba_tables
You may however use this only if you have DBA privileges which i find most of the time many application developers (like myself) should not have unless in other various environments where security settings are stepped down.
SELECT owner, table_name FROM dba_tables;For example, i am connected as user hr, which is configured not to have DBA access. Oracle prompts me that the table or view does not exist. In this case, this alternative does not work for me.
2. select * from all_tables
This method of retrieving information of your tables may be used if the particular user has not been given DBA rights.
SELECT owner, table_name FROM all_tables;SELECT owner, table_name FROM all_tables WHERE owner = 'your schema or username';
Note that a schema is not a user.
Reference
http://stackoverflow.com/questions/205736/oracle-get-list-of-all-tables
Revision
April 24, 2011. Minor elaboration.
Creating Oracle “AUTO_INCREMENT” column for your surrogate key
Mar 9th
Lets face it, nobody tries to create primary keys out from natural keys in today’s context, maybe due to the sheer number of database tables an application or system today requires.
Creating surrogate keys is simple as it is common to most people. Yet Oracle has made this task a non trivial one.
I have come upon the chance to create a new table supporting new business requirements. The guide steps through using Oracle SQL Developer.
Step 1: Create Table
Create a new table of your choice.
** The steps to create a table are omitted.
For the purpose of this guide, we are interested to make column STATUS_ID of table GL_RECORD_STATUS_MATRIX an auto incremented surrogate key. STATUS_ID is also the primary key of the table.
Step 2: Create Sequence
Auto incremental numbers come in the form of sequence in Oracle. It is necessary to create a sequence object.
Name your sequence. For my sequence, i have chosen “SEQ_GL_RECORD_STATUS_MATRIX” in the naming convention “SEQ” + <table name>.
Create your sequence with the following properties:
Increment 1
Min value 0
Max value 999999999
Start with 1
Step 3: Create Trigger
In the configuration dialog, name your trigger and select the table you wish to apply this trigger to. You may choose to name your sequence trigger in the convention of <table name> + “SEQ” or “SEQ” + <table name> depending on the naming scheme you prefer.
Select the “Row Level” option as we are applying this trigger on a row level.
Once you have created your trigger, SQL Developer should display the “code” of the trigger that you have just created.
CREATE OR REPLACE TRIGGER GL_RECORD_STATUS_MATRIX_SEQ
BEFORE INSERT ON GL_RECORD_STATUS_MATRIX
FOR EACH ROW
BEGIN
NULL;
END;
Note that between the BEGIN and END keywords is a NULL literal. This is where you fill in the “how” of this trigger.
CREATE OR REPLACE TRIGGER GL_RECORD_STATUS_MATRIX_SEQ
BEFORE INSERT ON GL_RECORD_STATUS_MATRIX
FOR EACH ROW
BEGIN
select SEQ_GL_RECORD_STATUS_MATRIX.nextval into :NEW.STATUS_ID from dual;
END;
Note that i have replaced the string literal “NULL” with the following:
select SEQ_GL_RECORD_STATUS_MATRIX.nextval into :NEW.STATUS_ID from dual;
This statement means, select the next value from the sequence i have just created, and insert into onto STATUS_ID of my table.
Compile the trigger when done.
Complete! Testing…
Test that your auto number is working by adding a new row to your table without explicitly providing a value to the STATUS_ID field. Oracle should insert the value of STATUS_ID automatically for you.
Recap…
Usually, it is not necessary to add a prefix to the name of the object that you are creating, for example, it is not necessary to name an employee table tbl_employee or table_employee. However, because Oracle does not quite allow us to create a sequence object with the same name as the table object in the same schema, we have no choice but to select another more definitive name for our sequence.
(Unable to create a sequence with the same name as the table)
SQL Script For Country Table (DDL and DML)
Oct 7th
The following SQL scripts below create the standard country and country code.
- Includes most of if not all countries (238) as according to ISO and standards bodies
- Provides a country code (US), and the country in full (United States Of America), properly escaped.
- Runs in MySQL DBMS
I have sticked to using (and updating/refining the list) for most production settings.
Correct and current as of October 8, 2010.
# Dumping structure for table abc.country_matrix
DROP TABLE IF EXISTS `country_matrix`;
CREATE TABLE IF NOT EXISTS `country_matrix` (
`country_id` smallint(5) unsigned NOT NULL,
`country` varchar(100) NOT NULL,
`country_code` varchar(2) NOT NULL,
PRIMARY KEY (`country_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
# Dumping data for table abc.country_matrix: 238 rows
/*!40000 ALTER TABLE `country_matrix` DISABLE KEYS */;
INSERT INTO `country_matrix` (`country_id`, `country`, `country_code`) VALUES (1, ‘Singapore’, ‘SG’), (2, ‘China’, ‘CN’), (3, ‘Malaysia’, ‘MY’), (4, ‘United States’, ‘US’), (5, ‘Canada’, ‘CA’), (6, ‘Albania’, ‘AL’), (7, ‘Algeria’, ‘DZ’), (8, ‘Amerin Samoa’, ‘DS’), (9, ‘Andorra’, ‘AD’), (10, ‘Angola’, ‘AO’), (11, ‘Anguilla’, ‘AI’), (12, ‘Antarctica’, ‘AQ’), (13, ‘Antigua and/or Barbuda’, ‘AG’), (14, ‘Argentina’, ‘AR’), (15, ‘Armenia’, ‘AM’), (16, ‘Aruba’, ‘AW’), (17, ‘Austria’, ‘AT’), (18, ‘Azerbaijan’, ‘AZ’), (19, ‘Bahamas’, ‘BS’), (20, ‘Bahrain’, ‘BH’), (21, ‘Bangladesh’, ‘BD’), (22, ‘Barbados’, ‘BB’), (23, ‘Belarus’, ‘BY’), (24, ‘Belgium’, ‘BE’), (25, ‘Belize’, ‘BZ’), (26, ‘Benin’, ‘BJ’), (27, ‘Bermuda’, ‘BM’), (28, ‘Bhutan’, ‘BT’), (29, ‘Bolivia’, ‘BO’), (30, ‘Bosnia and Herzegovina’, ‘BA’), (31, ‘Botswana’, ‘BW’), (32, ‘Bouvet Island’, ‘BV’), (33, ‘Brazil’, ‘BR’), (34, ‘British lndian Ocean Territory’, ‘IO’), (35, ‘Brunei Darussalam’, ‘BN’), (36, ‘Bulgaria’, ‘BG’), (37, ‘Burkina Faso’, ‘BF’), (38, ‘Burundi’, ‘BI’), (39, ‘Cambodia’, ‘KH’), (40, ‘Cameroon’, ‘CM’), (41, ‘Cape Verde’, ‘CV’), (42, ‘Cayman Islands’, ‘KY’), (43, ‘Central African Republic’, ‘CF’), (44, ‘Chad’, ‘TD’), (45, ‘Chile’, ‘CL’), (46, ‘Christmas Island’, ‘CX’), (47, ‘Cocos (Keeling) Islands’, ‘CC’), (48, ‘Colombia’, ‘CO’), (49, ‘Comoros’, ‘KM’), (50, ‘Congo’, ‘CG’), (51, ‘Cook Islands’, ‘CK’), (52, ‘Costa Rica’, ‘CR’), (53, ‘Croatia (Hrvatska)’, ‘HR’), (54, ‘Cuba’, ‘CU’), (55, ‘Cyprus’, ‘CY’), (56, ‘Czech Republic’, ‘CZ’), (57, ‘Denmark’, ‘DK’), (58, ‘Djibouti’, ‘DJ’), (59, ‘Dominica’, ‘DM’), (60, ‘Dominican Republic’, ‘DO’), (61, ‘East Timor’, ‘TP’), (62, ‘Ecudaor’, ‘EC’), (63, ‘Egypt’, ‘EG’), (64, ‘El Salvador’, ‘SV’), (65, ‘Equatorial Guinea’, ‘GQ’), (66, ‘Eritrea’, ‘ER’), (67, ‘Estonia’, ‘EE’), (68, ‘Ethiopia’, ‘ET’), (69, ‘Falkland Islands (Malvinas)’, ‘FK’), (70, ‘Faroe Islands’, ‘FO’), (71, ‘Fiji’, ‘FJ’), (72, ‘Finland’, ‘FI’), (73, ‘France’, ‘FR’), (74, ‘France, Metropolitan’, ‘FX’), (75, ‘French Guiana’, ‘GF’), (76, ‘French Polynesia’, ‘PF’), (77, ‘French Southern Territories’, ‘TF’), (78, ‘Gabon’, ‘GA’), (79, ‘Gambia’, ‘GM’), (80, ‘Georgia’, ‘GE’), (81, ‘Germany’, ‘DE’), (82, ‘Ghana’, ‘GH’), (83, ‘Gibraltar’, ‘GI’), (84, ‘Greece’, ‘GR’), (85, ‘Greenland’, ‘GL’), (86, ‘Grenada’, ‘GD’), (87, ‘Guadeloupe’, ‘GP’), (88, ‘Guam’, ‘GU’), (89, ‘Guatemala’, ‘GT’), (90, ‘Guinea’, ‘GN’), (91, ‘Guinea-Bissau’, ‘GW’), (92, ‘Guyana’, ‘GY’), (93, ‘Haiti’, ‘HT’), (94, ‘Heard and Mc Donald Islands’, ‘HM’), (95, ‘Honduras’, ‘HN’), (96, ‘Hong Kong’, ‘HK’), (97, ‘Hungary’, ‘HU’), (98, ‘Iceland’, ‘IS’), (99, ‘India’, ‘IN’), (100, ‘Indonesia’, ‘ID’), (101, ‘Iran (Islamic Republic of)’, ‘IR’), (102, ‘Iraq’, ‘IQ’), (103, ‘Ireland’, ‘IE’), (104, ‘Israel’, ‘IL’), (105, ‘Italy’, ‘IT’), (106, ‘Ivory Coast’, ‘CI’), (107, ‘Jamaica’, ‘JM’), (108, ‘Japan’, ‘JP’), (109, ‘Jordan’, ‘JO’), (110, ‘Kazakhstan’, ‘KZ’), (111, ‘Kenya’, ‘KE’), (112, ‘Kiribati’, ‘KI’), (113, ‘Korea, Democratic People\’\'\’\'s Republic of’, ‘KP’), (114, ‘Korea, Republic of’, ‘KR’), (115, ‘Kuwait’, ‘KW’), (116, ‘Kyrgyzstan’, ‘KG’), (117, ‘Lao People\’\'\’\'s Democratic Republic’, ‘LA’), (118, ‘Latvia’, ‘LV’), (119, ‘Lebanon’, ‘LB’), (120, ‘Lesotho’, ‘LS’), (121, ‘Liberia’, ‘LR’), (122, ‘Libyan Arab Jamahiriya’, ‘LY’), (123, ‘Liechtenstein’, ‘LI’), (124, ‘Lithuania’, ‘LT’), (125, ‘Luxembourg’, ‘LU’), (126, ‘Macau’, ‘MO’), (127, ‘Macedonia’, ‘MK’), (128, ‘Madagascar’, ‘MG’), (129, ‘Malawi’, ‘MW’), (130, ‘New Zealand’, ‘NZ’), (131, ‘Maldives’, ‘MV’), (132, ‘Mali’, ‘ML’), (133, ‘Malta’, ‘MT’), (134, ‘Marshall Islands’, ‘MH’), (135, ‘Martinique’, ‘MQ’), (136, ‘Mauritania’, ‘MR’), (137, ‘Mauritius’, ‘MU’), (138, ‘Mayotte’, ‘TY’), (139, ‘Mexico’, ‘MX’), (140, ‘Micronesia, Federated States of’, ‘FM’), (141, ‘Moldova, Republic of’, ‘MD’), (142, ‘Monaco’, ‘MC’), (143, ‘Mongolia’, ‘MN’), (144, ‘Montserrat’, ‘MS’), (145, ‘Morocco’, ‘MA’), (146, ‘Mozambique’, ‘MZ’), (147, ‘Myanmar’, ‘MM’), (148, ‘Namibia’, ‘NA’), (149, ‘Nauru’, ‘NR’), (150, ‘Nepal’, ‘NP’), (151, ‘Netherlands’, ‘NL’), (152, ‘Netherlands Antilles’, ‘AN’), (153, ‘New Caledonia’, ‘NC’), (154, ‘Nicaragua’, ‘NI’), (155, ‘Niger’, ‘NE’), (156, ‘Nigeria’, ‘NG’), (157, ‘Niue’, ‘NU’), (158, ‘Norfork Island’, ‘NF’), (159, ‘Northern Mariana Islands’, ‘MP’), (160, ‘Norway’, ‘NO’), (161, ‘Oman’, ‘OM’), (162, ‘Pakistan’, ‘PK’), (163, ‘Palau’, ‘PW’), (164, ‘Panama’, ‘PA’), (165, ‘Papua New Guinea’, ‘PG’), (166, ‘Paraguay’, ‘PY’), (167, ‘Peru’, ‘PE’), (168, ‘Philippines’, ‘PH’), (169, ‘Pitcairn’, ‘PN’), (170, ‘Poland’, ‘PL’), (171, ‘Portugal’, ‘PT’), (172, ‘Puerto Rico’, ‘PR’), (173, ‘Qatar’, ‘QA’), (174, ‘Reunion’, ‘RE’), (175, ‘Romania’, ‘RO’), (176, ‘Russian Federation’, ‘RU’), (177, ‘Rwanda’, ‘RW’), (178, ‘Saint Kitts and Nevis’, ‘KN’), (179, ‘Saint Lucia’, ‘LC’), (180, ‘Saint Vincent and the Grenadines’, ‘VC’), (181, ‘Samoa’, ‘WS’), (182, ‘San Marino’, ‘SM’), (183, ‘Sao Tome and Principe’, ‘ST’), (184, ‘Saudi Arabia’, ‘SA’), (185, ‘Senegal’, ‘SN’), (186, ‘Seychelles’, ‘SC’), (187, ‘Sierra Leone’, ‘SL’), (188, ‘Slovakia’, ‘SK’), (189, ‘Slovenia’, ‘SI’), (190, ‘Solomon Islands’, ‘SB’), (191, ‘Somalia’, ‘SO’), (192, ‘South Africa’, ‘ZA’), (193, ‘South Georgia South Sandwich Islands’, ‘GS’), (194, ‘Spain’, ‘ES’), (195, ‘Sri Lanka’, ‘LK’), (196, ‘St. Helena’, ‘SH’), (197, ‘St. Pierre and Miquelon’, ‘PM’), (198, ‘Sudan’, ‘SD’), (199, ‘Suriname’, ‘SR’), (200, ‘Svalbarn and Jan Mayen Islands’, ‘SJ’), (201, ‘Swaziland’, ‘SZ’), (202, ‘Sweden’, ‘SE’), (203, ‘Switzerland’, ‘CH’), (204, ‘Syrian Arab Republic’, ‘SY’), (205, ‘Taiwan’, ‘TW’), (206, ‘Tajikistan’, ‘TJ’), (207, ‘Tanzania, United Republic of’, ‘TZ’), (208, ‘Thailand’, ‘TH’), (209, ‘Togo’, ‘TG’), (210, ‘Tokelau’, ‘TK’), (211, ‘Tonga’, ‘TO’), (212, ‘Trinidad and Tobago’, ‘TT’), (213, ‘Tunisia’, ‘TN’), (214, ‘Turkey’, ‘TR’), (215, ‘Turkmenistan’, ‘TM’), (216, ‘Turks and Caicos Islands’, ‘TC’), (217, ‘Tuvalu’, ‘TV’), (218, ‘Uganda’, ‘UG’), (219, ‘Ukraine’, ‘UA’), (220, ‘United Arab Emirates’, ‘AE’), (221, ‘United Kingdom’, ‘GB’), (222, ‘Australia’, ‘AU’), (223, ‘Uruguay’, ‘UY’), (224, ‘Uzbekistan’, ‘UZ’), (225, ‘Vanuatu’, ‘VU’), (226, ‘Vatican City State’, ‘VA’), (227, ‘Venezuela’, ‘VE’), (228, ‘Vietnam’, ‘VN’), (229, ‘Virigan Islands (British)’, ‘VG’), (230, ‘Virgin Islands (U.S.)’, ‘VI’), (231, ‘Wallis and Futuna Islands’, ‘WF’), (232, ‘Western Sahara’, ‘EH’), (233, ‘Yemen’, ‘YE’), (234, ‘Yugoslavia’, ‘YU’), (235, ‘Zaire’, ‘ZR’), (236, ‘Zambia’, ‘ZM’), (237, ‘Zimbabwe’, ‘ZW’), (238, ‘Afghanistan’, ‘AF’);
