Fedora Install unixODBC
Encountered a problem while running the RODBC library from the R prompt as the root user, as follows:
> install.packages('RODBC') |
It failed with the following library dependency:
checking for unistd.h... yes
checking sql.h usability... no
checking sql.h presence... no
checking for sql.h... no
checking sqlext.h usability... no
checking sqlext.h presence... no
checking for sqlext.h... no
configure: error: "ODBC headers sql.h and sqlext.h not found"
ERROR: configuration failed for package ‘RODBC’
* removing ‘/usr/lib64/R/library/RODBC’
The downloaded source packages are in
‘/tmp/RtmpdT1gay/downloaded_packages’
Updating HTML index of packages in '.Library'
Making 'packages.html' ... done
Warning message:
In install.packages("RODBC") :
installation of package ‘RODBC’ had non-zero exit status |
I installed unixODBC-devel and unixODBC-gui-qt libraries to fix the library dependencies with the following command as the root user:
yum install -y unixODBC* |
It should show you the following when it installs the unixODBC-devel and unixODBC-gui-qt libraries:
Loaded plugins: langpacks, refresh-packagekit You need to be root to perform this command. [student@localhost ~]$ su - root Password: Last login: Fri Apr 20 21:18:56 PDT 2018 on pts/1 [root@localhost ~]# yum install -y unixODBC* Loaded plugins: langpacks, refresh-packagekit cassandra/signature | 819 B 00:00 cassandra/signature | 2.9 kB 00:00 !!! fedora/20/x86_64/metalink | 3.3 kB 00:00 mysql-connectors-community | 2.5 kB 00:00 mysql-tools-community | 2.5 kB 00:00 mysql56-community | 2.5 kB 00:00 http://yum.postgresql.org/9.3/fedora/fedora-20-x86_64/repodata/repomd.xml: [Errno 14] HTTP Error 404 - Not Found Trying other mirror. updates/20/x86_64/metalink | 3.1 kB 00:00 Package unixODBC-2.3.2-4.fc20.x86_64 already installed and latest version Resolving Dependencies --> Running transaction check ---> Package unixODBC-devel.x86_64 0:2.3.2-4.fc20 will be installed ---> Package unixODBC-gui-qt.x86_64 0:0-0.8.20120105svn98.fc20 will be installed --> Processing Dependency: libQtNetwork.so.4()(64bit) for package: unixODBC-gui-qt-0-0.8.20120105svn98.fc20.x86_64 --> Processing Dependency: libQtGui.so.4()(64bit) for package: unixODBC-gui-qt-0-0.8.20120105svn98.fc20.x86_64 --> Processing Dependency: libQtCore.so.4()(64bit) for package: unixODBC-gui-qt-0-0.8.20120105svn98.fc20.x86_64 --> Processing Dependency: libQtAssistantClient.so.4()(64bit) for package: unixODBC-gui-qt-0-0.8.20120105svn98.fc20.x86_64 --> Running transaction check ---> Package qt.x86_64 1:4.8.6-30.fc20 will be installed --> Processing Dependency: qt-common = 1:4.8.6-30.fc20 for package: 1:qt-4.8.6-30.fc20.x86_64 --> Processing Dependency: qt-settings for package: 1:qt-4.8.6-30.fc20.x86_64 ---> Package qt-assistant-adp.x86_64 0:4.6.3-6.fc20 will be installed ---> Package qt-x11.x86_64 1:4.8.6-30.fc20 will be installed --> Processing Dependency: libmng.so.1()(64bit) for package: 1:qt-x11-4.8.6-30.fc20.x86_64 --> Processing Dependency: libclucene.so.3()(64bit) for package: 1:qt-x11-4.8.6-30.fc20.x86_64 --> Running transaction check ---> Package clucene09-core.x86_64 0:0.9.21b-13.fc20 will be installed ---> Package libmng.x86_64 0:1.0.10-12.fc20 will be installed ---> Package qt-common.noarch 1:4.8.6-30.fc20 will be installed ---> Package qt-settings.noarch 0:20-18.fc20 will be installed --> Finished Dependency Resolution Dependencies Resolved ================================================================================ Package Arch Version Repository Size ================================================================================ Installing: unixODBC-devel x86_64 2.3.2-4.fc20 updates 55 k unixODBC-gui-qt x86_64 0-0.8.20120105svn98.fc20 fedora 624 k Installing for dependencies: clucene09-core x86_64 0.9.21b-13.fc20 updates 300 k libmng x86_64 1.0.10-12.fc20 fedora 166 k qt x86_64 1:4.8.6-30.fc20 updates 4.7 M qt-assistant-adp x86_64 4.6.3-6.fc20 fedora 257 k qt-common noarch 1:4.8.6-30.fc20 updates 5.8 k qt-settings noarch 20-18.fc20 updates 19 k qt-x11 x86_64 1:4.8.6-30.fc20 updates 12 M Transaction Summary ================================================================================ Install 2 Packages (+7 Dependent packages) Total download size: 18 M Installed size: 56 M Downloading packages: (1/9): libmng-1.0.10-12.fc20.x86_64.rpm | 166 kB 00:01 (2/9): clucene09-core-0.9.21b-13.fc20.x86_64.rpm | 300 kB 00:01 (3/9): qt-4.8.6-30.fc20.x86_64.rpm | 4.7 MB 00:00 (4/9): qt-common-4.8.6-30.fc20.noarch.rpm | 5.8 kB 00:00 (5/9): qt-settings-20-18.fc20.noarch.rpm | 19 kB 00:00 (6/9): qt-assistant-adp-4.6.3-6.fc20.x86_64.rpm | 257 kB 00:00 (7/9): qt-x11-4.8.6-30.fc20.x86_64.rpm | 12 MB 00:01 (8/9): unixODBC-devel-2.3.2-4.fc20.x86_64.rpm | 55 kB 00:00 (9/9): unixODBC-gui-qt-0-0.8.20120105svn98.fc20.x86_64.rpm | 624 kB 00:01 -------------------------------------------------------------------------------- Total 4.1 MB/s | 18 MB 00:04 Running transaction check Running transaction test Transaction test succeeded Running transaction (shutdown inhibited) Installing : libmng-1.0.10-12.fc20.x86_64 1/9 Installing : qt-settings-20-18.fc20.noarch 2/9 Installing : 1:qt-common-4.8.6-30.fc20.noarch 3/9 Installing : 1:qt-4.8.6-30.fc20.x86_64 4/9 Installing : clucene09-core-0.9.21b-13.fc20.x86_64 5/9 Installing : 1:qt-x11-4.8.6-30.fc20.x86_64 6/9 Installing : qt-assistant-adp-4.6.3-6.fc20.x86_64 7/9 Installing : unixODBC-gui-qt-0-0.8.20120105svn98.fc20.x86_64 8/9 Installing : unixODBC-devel-2.3.2-4.fc20.x86_64 9/9 Verifying : clucene09-core-0.9.21b-13.fc20.x86_64 1/9 Verifying : unixODBC-gui-qt-0-0.8.20120105svn98.fc20.x86_64 2/9 Verifying : 1:qt-x11-4.8.6-30.fc20.x86_64 3/9 Verifying : 1:qt-4.8.6-30.fc20.x86_64 4/9 Verifying : qt-settings-20-18.fc20.noarch 5/9 Verifying : 1:qt-common-4.8.6-30.fc20.noarch 6/9 Verifying : unixODBC-devel-2.3.2-4.fc20.x86_64 7/9 Verifying : qt-assistant-adp-4.6.3-6.fc20.x86_64 8/9 Verifying : libmng-1.0.10-12.fc20.x86_64 9/9 Installed: unixODBC-devel.x86_64 0:2.3.2-4.fc20 unixODBC-gui-qt.x86_64 0:0-0.8.20120105svn98.fc20 Dependency Installed: clucene09-core.x86_64 0:0.9.21b-13.fc20 libmng.x86_64 0:1.0.10-12.fc20 qt.x86_64 1:4.8.6-30.fc20 qt-assistant-adp.x86_64 0:4.6.3-6.fc20 qt-common.noarch 1:4.8.6-30.fc20 qt-settings.noarch 0:20-18.fc20 qt-x11.x86_64 1:4.8.6-30.fc20 Complete! |
After installing the unixODBC-devel and unixODBC-gui-qt libraries, I installed the RODBC library from the R prompt, having launched the R environment as the root user:
> install.packages('RODBC') |
Installing the RODBC library should install cleanly and generate the following output:
Installing package into ‘/usr/lib64/R/library’ (as ‘lib’ is unspecified) trying URL 'http://cran.cnr.berkeley.edu/src/contrib/RODBC_1.3-15.tar.gz' Content type 'application/x-gzip' length 1163967 bytes (1.1 MB) ================================================== downloaded 1.1 MB * installing *source* package ‘RODBC’ ... ** package ‘RODBC’ successfully unpacked and MD5 sums checked checking for gcc... gcc -m64 -std=gnu99 checking whether the C compiler works... yes checking for C compiler default output file name... a.out checking for suffix of executables... checking whether we are cross compiling... no checking for suffix of object files... o checking whether we are using the GNU C compiler... yes checking whether gcc -m64 -std=gnu99 accepts -g... yes checking for gcc -m64 -std=gnu99 option to accept ISO C89... none needed checking how to run the C preprocessor... gcc -m64 -std=gnu99 -E checking for grep that handles long lines and -e... /bin/grep checking for egrep... /bin/grep -E checking for ANSI C header files... yes checking for sys/types.h... yes checking for sys/stat.h... yes checking for stdlib.h... yes checking for string.h... yes checking for memory.h... yes checking for strings.h... yes checking for inttypes.h... yes checking for stdint.h... yes checking for unistd.h... yes checking sql.h usability... yes checking sql.h presence... yes checking for sql.h... yes checking sqlext.h usability... yes checking sqlext.h presence... yes checking for sqlext.h... yes checking for library containing SQLTables... -lodbc checking for SQLLEN... yes checking for SQLULEN... yes checking size of long... 8 configure: creating ./config.status config.status: creating src/Makevars config.status: creating src/config.h ** libs gcc -m64 -std=gnu99 -I/usr/include/R -DNDEBUG -I. -I/usr/local/include -fpic -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -c RODBC.c -o RODBC.o gcc -m64 -std=gnu99 -shared -L/usr/lib64/R/lib -Wl,-z,relro -o RODBC.so RODBC.o -lodbc -L/usr/lib64/R/lib -lR installing to /usr/lib64/R/library/RODBC/libs ** R ** inst ** preparing package for lazy loading ** help *** installing help indices converting help for package ‘RODBC’ finding HTML links ... done RODBC-internal html RODBC-package html odbc html odbcClose html odbcConnect html odbcDataSources html odbcGetInfo html odbcSetAutoCommit html setSqlTypeInfo html sqlColumns html sqlCopy html sqlDrop html sqlFetch html sqlQuery html sqlSave html sqlTables html sqlTypeInfo html ** building package indices ** installing vignettes ** testing if installed package can be loaded * DONE (RODBC) Making 'packages.html' ... done The downloaded source packages are in ‘/tmp/RtmpdT1gay/downloaded_packages’ Updating HTML index of packages in '.Library' Making 'packages.html' ... done |
I hope that helps anybody who runs into the library dependency problems.
Fedora R Install
I’ve started building the new image for the database courses. This one needs to include Oracle, MySQL, Cassandra, Hive, and MongoDB databases; and include examples for C, C++, Java, Perl, PHP, Python, R programming languages.
Installing R was a surprise when I saw how many packages there are for it. It’s a standard yum command from the repository, but it will install 256 packages. The command is:
yum install -y R |
Once you install it, you simply start the R interpreter, which is part of the Comprehensive R Archive Network (CRAN). Any installation of the R packages includes CRAN, but there are many additional libraries that you may install.
You can launch the R interpreter by typing the following at the Linux command-line:
R |
It will display the following licensing information and then the command prompt:
R version 3.2.0 (2015-04-16) -- "Full of Ingredients" Copyright (C) 2015 The R Foundation for Statistical Computing Platform: x86_64-redhat-linux-gnu (64-bit) R is free software and comes with ABSOLUTELY NO WARRANTY. You are welcome to redistribute it under certain conditions. Type 'license()' or 'licence()' for distribution details. Natural language support but running in an English locale R is a collaborative project with many contributors. Type 'contributors()' for more information and 'citation()' on how to cite R or R packages in publications. Type 'demo()' for some demos, 'help()' for on-line help, or 'help.start()' for an HTML browser interface to help. Type 'q()' to quit R. |
You have two options for help. As qualified above, you can type help() to get a Linux man page, and q at the colon quits the man page. Typing help.start() launches a browser interface (shown below), which is more helpful.
You can discover your installed R packages with a call to the installed.packages() function but the output lacks clarity and is verbose. You can see a formatted summary of your installed packages with the following command:
print(as.data.frame(installed.packages()[,c(1,3:4)]),row.names=FALSE) |
It should display the following:
Package Version Priority
base 3.2.0 base
boot 1.3-16 recommended
class 7.3-12 recommended
cluster 2.0.1 recommended
codetools 0.2-11 recommended
compiler 3.2.0 base
datasets 3.2.0 base
foreign 0.8-63 recommended
graphics 3.2.0 base
grDevices 3.2.0 base
grid 3.2.0 base
KernSmooth 2.23-14 recommended
lattice 0.20-31 recommended
MASS 7.3-40 recommended
Matrix 1.2-0 recommended
methods 3.2.0 base
mgcv 1.8-6 recommended
nlme 3.1-120 recommended
nnet 7.3-9 recommended
parallel 3.2.0 base
rpart 4.1-9 recommended
spatial 7.3-9 recommended
splines 3.2.0 base
stats 3.2.0 base
stats4 3.2.0 base
survival 2.38-1 recommended
tcltk 3.2.0 base
tools 3.2.0 base
utils 3.2.0 base |
The print() function allows us to remove the text-based indexes from display. The indexes would be the same as the package names. If you call frame() function with a second argument of row.names=FALSE, then R converts the text-based indexes to numeric indexes.
You can quit the R environment with the q() or quit() function calls. It will prompt you whether or not you want to save your workspace before you exit.
It’s time to play with R now. I hope this helps you get started by installing and playing with the R programming language.
External Tables + Merge
This is an example of how you would upload data from a flat file, or Comma Separated Value (CSV) file. It’s important to note that in the file upload you are transferring information that doesn’t have surrogate key values by leveraing joins inside a MERGE statement.
Step #1 : Create a virtual directory
You can create a virtual directory without a physical directory but it won’t work when you try to access it. Therefore, you should create the physical directory first. Assuming you’ve created a /u01/app/oracle/upload file directory on the Windows platform, you can then create a virtual directory and grant permissions to the student user as the SYS privileged user.
The syntax for these steps is:
CREATE DIRECTORY upload AS '/u01/app/oracle/upload'; GRANT READ, WRITE ON DIRECTORY upload TO student; |
Step #2 : Position your CSV file in the physical directory
After creating the virtual directory, copy the following contents into a file named kingdom_import.csv in the /u01/app/oracle/upload directory or folder. If you attempt to do this in Windows, you need to disable Windows UAC before performing this step.
Place the following in the kingdom_import.csv file. The trailing commas aren’t too meaningful in Oracle but they’re very helpful if you use the file in MySQL. A key element in creating this files requires that you avoid trailing line returns at the bottom of the file because they’re inserted as null values. There should be no lines after the last row of data.
'Narnia',77600,'Peter the Magnificent','20-MAR-1272','19-JUN-1292', 'Narnia',77600,'Edmund the Just','20-MAR-1272','19-JUN-1292', 'Narnia',77600,'Susan the Gentle','20-MAR-1272','19-JUN-1292', 'Narnia',77600,'Lucy the Valiant','20-MAR-1272','19-JUN-1292', 'Narnia',42100,'Peter the Magnificent','12-APR-1531','31-MAY-1531', 'Narnia',42100,'Edmund the Just','12-APR-1531','31-MAY-1531', 'Narnia',42100,'Susan the Gentle','12-APR-1531','31-MAY-1531', 'Narnia',42100,'Lucy the Valiant','12-APR-1531','31-MAY-1531', 'Camelot',15200,'King Arthur','10-MAR-0631','12-DEC-0686', 'Camelot',15200,'Sir Lionel','10-MAR-0631','12-DEC-0686', 'Camelot',15200,'Sir Bors','10-MAR-0631','12-DEC-0635', 'Camelot',15200,'Sir Bors','10-MAR-0640','12-DEC-0686', 'Camelot',15200,'Sir Galahad','10-MAR-0631','12-DEC-0686', 'Camelot',15200,'Sir Gawain','10-MAR-0631','12-DEC-0686', 'Camelot',15200,'Sir Tristram','10-MAR-0631','12-DEC-0686', 'Camelot',15200,'Sir Percival','10-MAR-0631','12-DEC-0686', 'Camelot',15200,'Sir Lancelot','30-SEP-0670','12-DEC-0682', |
Step #3 : Reconnect as the student user
Disconnect and connect as the student user, or reconnect as the student user. The reconnect syntax that protects your password is:
CONNECT student@xe |
Step #4 : Run the script that creates tables and sequences
Copy the following into a create_kingdom_upload.sql file within a directory of your choice. Then, run it as the student account.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 | -- Conditionally drop tables and sequences. BEGIN FOR i IN (SELECT TABLE_NAME FROM user_tables WHERE TABLE_NAME IN ('KINGDOM','KNIGHT','KINGDOM_KNIGHT_IMPORT')) LOOP EXECUTE IMMEDIATE 'DROP TABLE '||i.table_name||' CASCADE CONSTRAINTS'; END LOOP; FOR i IN (SELECT sequence_name FROM user_sequences WHERE sequence_name IN ('KINGDOM_S1','KNIGHT_S1')) LOOP EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.sequence_name; END LOOP; END; / -- Create normalized kingdom table. CREATE TABLE kingdom ( kingdom_id NUMBER , kingdom_name VARCHAR2(20) , population NUMBER); -- Create a sequence for the kingdom table. CREATE SEQUENCE kingdom_s1; -- Create normalized knight table. CREATE TABLE knight ( knight_id NUMBER , knight_name VARCHAR2(24) , kingdom_allegiance_id NUMBER , allegiance_start_date DATE , allegiance_end_date DATE); -- Create a sequence for the knight table. CREATE SEQUENCE knight_s1; -- Create external import table. CREATE TABLE kingdom_knight_import ( kingdom_name VARCHAR2(20) , population NUMBER , knight_name VARCHAR2(24) , allegiance_start_date DATE , allegiance_end_date DATE) ORGANIZATION EXTERNAL ( TYPE oracle_loader DEFAULT DIRECTORY upload ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII BAFFLE 'UPLOAD':'kingdom_import.bad' DISCARDFILE 'UPLOAD':'kingdom_import.dis' LOGFILE 'UPLOAD':'kingdom_import.log' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY "'" MISSING FIELD VALUES ARE NULL ) LOCATION ('kingdom_import.csv')) REJECT LIMIT UNLIMITED; |
Step #5 : Test your access to the external table
There a number of things that could go wrong with setting up an external table, such as file permissions. Before moving on to the balance of the steps, you should test what you’ve done. Run the following query from the student account to check whether or not you can access the kingdom_import.csv file.
1 2 3 4 5 6 7 8 9 | COL kingdom_name FORMAT A8 HEADING "Kingdom|Name" COL population FORMAT 99999999 HEADING "Population" COL knight_name FORMAT A30 HEADING "Knight Name" SELECT kingdom_name , population , knight_name , TO_CHAR(allegiance_start_date,'DD-MON-YYYY') AS allegiance_start_date , TO_CHAR(allegiance_end_date,'DD-MON-YYYY') AS allegiance_end_date FROM kingdom_knight_import; |
Step #6 : Create the upload procedure
Copy the following into a create_upload_procedure.sql file within a directory of your choice. Then, run it as the student account.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 | -- Create a procedure to wrap the transaction. CREATE OR REPLACE PROCEDURE upload_kingdom IS BEGIN -- Set save point for an all or nothing transaction. SAVEPOINT starting_point; -- Insert or update the table, which makes this rerunnable when the file hasn't been updated. MERGE INTO kingdom target USING (SELECT DISTINCT k.kingdom_id , kki.kingdom_name , kki.population FROM kingdom_knight_import kki LEFT JOIN kingdom k ON kki.kingdom_name = k.kingdom_name AND kki.population = k.population) SOURCE ON (target.kingdom_id = SOURCE.kingdom_id) WHEN MATCHED THEN UPDATE SET kingdom_name = SOURCE.kingdom_name WHEN NOT MATCHED THEN INSERT VALUES ( kingdom_s1.nextval , SOURCE.kingdom_name , SOURCE.population); -- Insert or update the table, which makes this rerunnable when the file hasn't been updated. MERGE INTO knight target USING (SELECT kn.knight_id , kki.knight_name , k.kingdom_id , kki.allegiance_start_date AS start_date , kki.allegiance_end_date AS end_date FROM kingdom_knight_import kki INNER JOIN kingdom k ON kki.kingdom_name = k.kingdom_name AND kki.population = k.population LEFT JOIN knight kn ON k.kingdom_id = kn.kingdom_allegiance_id AND kki.knight_name = kn.knight_name AND kki.allegiance_start_date = kn.allegiance_start_date AND kki.allegiance_end_date = kn.allegiance_end_date) SOURCE ON (target.kingdom_allegiance_id = SOURCE.kingdom_id) WHEN MATCHED THEN UPDATE SET allegiance_start_date = SOURCE.start_date , allegiance_end_date = SOURCE.end_date WHEN NOT MATCHED THEN INSERT VALUES ( knight_s1.nextval , SOURCE.knight_name , SOURCE.kingdom_id , SOURCE.start_date , SOURCE.end_date); -- Save the changes. COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK TO starting_point; RETURN; END; / |
Step #7 : Run the upload procedure
You can run the file by calling the stored procedure built by the script. The procedure ensures that records are inserted or updated into their respective tables.
EXECUTE upload_kingdom; |
Step #8 : Test the results of the upload procedure
You can test whether or not it worked by running the following queries.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | -- Check the kingdom table. SELECT * FROM kingdom; -- Format Oracle output. COLUMN knight_id FORMAT 999 HEADING "Knight|ID #" COLUMN knight_name FORMAT A23 HEADING "Knight Name" COLUMN kingdom_allegiance_id FORMAT 999 HEADING "Kingdom|Allegiance|ID #" COLUMN allegiance_start_date FORMAT A11 HEADING "Allegiance|Start Date" COLUMN allegiance_end_date FORMAT A11 HEADING "Allegiance|End Date" SET PAGESIZE 999 -- Check the knight table. SELECT knight_id , knight_name , kingdom_allegiance_id , TO_CHAR(allegiance_start_date,'DD-MON-YYYY') AS allegiance_start_date , TO_CHAR(allegiance_end_date,'DD-MON-YYYY') AS allegiance_end_date FROM knight; |
It should display the following information:
KINGDOM_ID KINGDOM_NAME POPULATION
---------- -------------------- ----------
1 Narnia 42100
2 Narnia 77600
3 Camelot 15200
Kingdom
Knight Allegiance Allegiance Allegiance
ID # Knight Name ID # Start Date End Date
------ ----------------------- ---------- ----------- -----------
1 Peter the Magnificent 2 20-MAR-1272 19-JUN-1292
2 Edmund the Just 2 20-MAR-1272 19-JUN-1292
3 Susan the Gentle 2 20-MAR-1272 19-JUN-1292
4 Lucy the Valiant 2 20-MAR-1272 19-JUN-1292
5 Peter the Magnificent 1 12-APR-1531 31-MAY-1531
6 Edmund the Just 1 12-APR-1531 31-MAY-1531
7 Susan the Gentle 1 12-APR-1531 31-MAY-1531
8 Lucy the Valiant 1 12-APR-1531 31-MAY-1531
9 King Arthur 3 10-MAR-0631 12-DEC-0686
10 Sir Lionel 3 10-MAR-0631 12-DEC-0686
11 Sir Bors 3 10-MAR-0631 12-DEC-0635
12 Sir Bors 3 10-MAR-0640 12-DEC-0686
13 Sir Galahad 3 10-MAR-0631 12-DEC-0686
14 Sir Gawain 3 10-MAR-0631 12-DEC-0686
15 Sir Tristram 3 10-MAR-0631 12-DEC-0686
16 Sir Percival 3 10-MAR-0631 12-DEC-0686
17 Sir Lancelot 3 30-SEP-0670 12-DEC-0682 |
You can rerun the procedure to check that it doesn’t alter any information, then you could add a new knight to test the insertion portion.
Windows 10 Laptops
Teaching Oracle technology always has challenges. They’re generally large challenges because we ask students to run 4 GB Linux VM with Oracle Database 11g XE pre-configured for them. A number of the student computers aren’t up to the task of running the virtualization.
Installing VMware Workstation or Player and a 64-bit Linux operating system is the easiest way to discover a laptop that advertises itself as 64-bit when it truly isn’t. Most of the computers raise an exception that says they’re unable to run hyperthreading, and naturally two BIOS settings are disabled by the manufacturers.
As a result, I get a lot of questions from students on computers. Some of the questions are simple and driven by a desire to maximize their investment. Other questions aren’t quite as simple. The harder questions are typically driven by a need to accomplish something they can’t do with their computer.
I can’t help but feel too many students see laptops as commodities, like televisions. They purchase their laptops thinking they’ve bought the right computer because it provides features like a touch screen. Unfortunately, they don’t notice things like the operating system because many of them purchase computers that run the Microsoft Windows.
They believe Microsoft Windows is simply a single operating system. They don’t know that there are seven versions of Windows 10 with different features. More importantly, they don’t know there are two key versions of Windows 10 when they purchase a laptop – the Windows 10 Home and Windows 10 Pro. The student seem to never find a simple Windows 10 Buyers Guide.
Windows 10 Home Edition is designed for end-user computing that includes using application software, whereas Windows 10 Pro Edition is designed for computing that runs both application and server software. The choice of one over the other determines what you can or can’t do with your Windows software.
Changing between Windows 10 Home and Windows 10 Pro comes at a cost to most consumers. That’s because they purchase machines with OEM versions of the Windows operating system. Vendors provide OEM versions of Windows 10 because they customize boards and chip-sets; and sometimes they purchase and install chips that fail to meet manufacturing standards. In these cases, the OEM Windows 10 comes with modifications and custom drivers. Moving from an OEM Windows 10 Home to a Windows 10 Pro can be very complicated.
Also, it’s all too common for OEM Windows 10 to disable 64-bit operations while advertising their product as 64-bit. The reasons for this can be complex and hard to identify sometimes. When a manufacturer purchases defective CPUs, they tend to disable some of the chips features. Manufacturers often disable 64-bit features to work around a defective CPU, one or more chip-sets, or their own customizations to the Windows 10 operating system.
I wrote all this to help focus purchases for those who want to run an Oracle Database on a Windows 10 operating system. You have two choices. One uses the native Windows 10 Pro operating system to run Oracle Database 11g XE natively, and the other uses Windows 10 to run VMware or Virtual Box to support a Linux operating system and Oracle Database 11g XE instance.
Best of luck, and always check the laptop specifications. As a rule, don’t buy Windows 10 Home machines if you want to run an Oracle Database.
Apple iTunes Bug
Over the years, this bug never gets fixed. I know it must irritate more people than just me. Unlike those who live in urban communities with great download speeds and relatively inexpensive Internet providers, I live in an area held hostage by expensive CableOne Internet service. Net neutrality won’t fix my issue.
The Apple iTunes bug occurs after you download a movie and the cloud symbol disappears. At first, it may appear as designed, with only one image displayed, like Papillon and Passengers is shown below:
From time to time, Apple iTunes gets confused (polite speak for an intermittent bug) and creates a new iCloud image side-by-side with the downloaded version of the movie. You can see that with the image of The Adventures of Robin Hood:
It annoys me and it takes time to fix. The present solution is to delete the downloaded file image and then re-download it if you must have a local copy (the tedious lives of those outside of large metropolitan areas). Naturally, for those of us outside of large metropolitan areas with monthly restrictions on the size of downloads, Apple’s solution is not a viable workaround. Unfortunately, Apple appears disinclined to figure out what causes the problem or fixing it in the existing iTunes code base. When I called Apple’s iTune support it took a third level engineer to agree that the problem even exists. 😉
Oracle 12c and PHP
This answers “How you connect PHP programs to an Oracle 12c multitenant database. This shows you how to connect your PHP programs to a user-defined Container Database (CDB) and Pluggable Database (PDB). It presupposes you know how to provision a PDB, and configure your Oracle listener.ora and tnsnames.ora files.
CDB Connection:
This assumes you already created a user-defined c##plsql CDB user, and granted an appropriate role or set of privileges to the user. Assuming the demonstration database Oracle TNS Service Name of orcl, you would test your connection with this script:
<?php // Attempt to connect to your database. $c = @oci_connect("video", "video", "localhost/orcl"); if (!$c) { print "Sorry! The connection to the database failed. Please try again later."; die(); } else { print "Congrats! You've connected to an Oracle database!"; oci_close($c); } ?> |
PDB Connection:
This assumes you already created a user-defined videodb PDB, and video user in the PDB, and granted an appropriate role or set of privileges to the video user. Assuming the user-defined videodb PDB uses an Oracle TNS Service Name of videodb, you would test your connection with this script:
<?php // Attempt to connect to your database. $c = @oci_connect("video", "video", "localhost/videodb"); if (!$c) { print "Sorry! The connection to the database failed. Please try again later."; die(); } else { print "Congrats! You've connected to an Oracle database!"; oci_close($c); } ?> |
Line 3 above uses the TNS Service Name from the tnsnames.ora file, which is also the SID Name from the listener.ora file after the slash that follows the localhost. That’s the only trick you should need.
You should note that because the tnsnames.ora file uses a video service name, the connection from the command line differs:
sqlplus video@video/video |
Hope this helps those trying to sort it out.
Type Dependency Tree
While trying to explain a student question about Oracle object types, it seemed necessary to show how to write a dependency tree. I did some poking around and found there wasn’t a convenient script at hand. So, I decided to write one.
This assumes the following Oracle object types, which don’t have any formal methods (methods are always provided by PL/SQL or Java language implementations):
CREATE OR REPLACE TYPE base_t AS OBJECT ( base_id NUMBER ) NOT FINAL; / CREATE OR REPLACE TYPE person_t UNDER base_t ( first_name VARCHAR2(20) , middle_name VARCHAR2(20) , last_name VARCHAR2(20)) NOT FINAL; / CREATE OR REPLACE TYPE driver_t UNDER person_t ( license VARCHAR2(20)); / |
Here’s a query to show the hierarchy of object types and attributes by object-level in the hierarchy:
COL type_name FORMAT A20 HEADING TYPE_NAME COL attr_no FORMAT 999 HEADING ATTR_NO COL attr_name FORMAT A20 HEADING ATTR_NAME COL TYPE FORMAT A12 HEADING TYPE SELECT DISTINCT LPAD(' ',2*(LEVEL-1)) || ut.type_name AS type_name , uta.attr_no , uta.attr_name , CASE WHEN uta.attr_type_name = 'NUMBER' THEN uta.attr_type_name WHEN uta.attr_type_name = 'VARCHAR2' THEN uta.attr_type_name || '(' || uta.LENGTH || ')' END AS TYPE FROM user_types ut , user_type_attrs uta WHERE ut.typecode = 'OBJECT' AND ut.type_name = uta.type_name AND uta.inherited = 'NO' START WITH ut.type_name = 'BASE_T' CONNECT BY PRIOR ut.type_name = ut.supertype_name ORDER BY uta.attr_no; |
It should return the following:
TYPE_NAME ATTR_NO ATTR_NAME TYPE
-------------------- ------- -------------------- ------------
BASE_T 1 BASE_ID NUMBER
PERSON_T 2 FIRST_NAME VARCHAR2(20)
PERSON_T 3 MIDDLE_NAME VARCHAR2(20)
PERSON_T 4 LAST_NAME VARCHAR2(20)
DRIVER_T 5 LICENSE VARCHAR2(20) |
As always, I hope this helps those looking to discover an Oracle object type hierarchy without examining each object type in turn.
Substitutable Columns
Oracle’s substitutable columns are interesting and substantially different than Oracle’s nested tables. The benefit of substitutable columns is that you can create one for an object type or any subtypes of that object type. Unfortunately, you can’t create the same behavior with nested tables because Oracle’s implementation of collection types are always final data types and you can’t extend their behaviors.
The Oracle Database has three types of collections. Two are SQL scoped collection types and the remaining one is a PL/SQL-only collection. You can only use the two SQL scoped collection types as column data types. One of the SQL-scoped collection types is an Attribute Data Type (ADT), which uses a base data type of DATA, NUMBER, or VARCHAR2.
The base data types of a UDT are scalar data types and scalar data types are data types that hold one thing. The other SQL-scoped collection type is a collection of User-Defined Types (UDTs), which are object types that you create like record structures by assembling sets of basic scalar data types. The elements of a UDT are known as members, whereas the instances of a collection are known as elements because they indexed in a set.
You can join a row with any nested table by using a cross join because they match the row with the nested table by using an ID-dependent join. An ID-dependent join is inexpensive because it relies on a structural dependency, the existence of the nested table in a column of a row. Typical joins on the other hand are joins between two tables or two copies of the same table. These non ID-dependent joins use at least matching values in one column of each table or one column of two copies of a table.
Joins between substitutable columns that hold UDTs are unlike joins between nested tables. The following sets up an example to demonstrate how you can join the non-substitutable columns of a row with the substitutable columns.
- You need a base UDT object type that you can extend, where extend means you can create a subtype of the base object type. While this is straight forward when you create an Oracle object type with methods, it isn’t necessarily straight forward when you want to simply create a base data structure as a generalized type with subtypes.
The important clause is overriding the
FINALdefault by making the base typeNOT FINAL. The example useBASE_Tas the generalized type or data structure of a substitutable column:CREATE OR REPLACE TYPE base_t AS OBJECT ( base_id NUMBER ) NOT FINAL; /
- After you create your base data structure, you create a specialized subtype. The following example creates a
PERSON_Ttype and accepts the default ofFINAL, which means you can’t create another subtype level.CREATE OR REPLACE TYPE person_t UNDER base_t ( first_name VARCHAR2(20) , middle_name VARCHAR2(20) , last_name VARCHAR2(20)); /
- With a generalized
BASE_Ttype and a specializedPERSON_Tsubtype, you create aCUSTOMERtable with a substitutableCUSTOMER_NAMEcolumn. TheCUSTOMER_NAMEcolumn uses the generalizedBASE_Tdata type. You should also create aCUSTOMER_Ssequence that you can use as a surrogate key column for the table.CREATE TABLE customer ( customer_id NUMBER , customer_name BASE_T ); CREATE SEQUENCE customer_s;
- You can now populate the table with instances of the
BASE_Ttype or thePERSON_Tsubtype. The following inserts three rows into theCUSTOMERtable. One for Hank Pym the original Ant-Man, one for Scott Lang the succeeding Ant-Man, and another for Darren Cross the original Yellowjacket.INSERT INTO customer VALUES ( customer_s.NEXTVAL , person_t( customer_s.CURRVAL , first_name => 'Hank' , middle_name => NULL , last_name => 'Pym')); INSERT INTO customer VALUES ( customer_s.NEXTVAL , person_t( customer_s.CURRVAL , first_name => 'Scott' , middle_name => NULL , last_name => 'Lang')); INSERT INTO customer VALUES ( customer_s.NEXTVAL , person_t( customer_s.CURRVAL , first_name => 'Darren' , middle_name => NULL , last_name => 'Cross'));
- The significance or problem associated with substitutable columns is that the actual columns of the object data type are hidden, which means you can’t query them like they’re nested elements of the substitutable column. The following query demonstrates what happens when you try to access those hidden member columns:
SELECT customer_id , customer_name.base_id , customer_name.first_name , customer_name.middle_name , customer_name.last_name FROM customer;
It returns the following error message:
, customer_name.last_name * ERROR at line 5: ORA-00904: "CUSTOMER_NAME"."LAST_NAME": invalid identifier - This error message may lead you to call the
CUSTOMER_NAMEcolumn in a subquery and use theTABLEfunction to convert it to a result set. However, it also fails because a UDT object type by itself is an ordinary object type not a collection of object types. TheTABLEfunction can’t promote the single instance to collection.SELECT * FROM TABLE(SELECT TREAT(customer_name AS person_t) FROM customer);
It returns the following error message:
FROM TABLE(SELECT TREAT(customer_name AS person_t) FROM customer) * ERROR at line 2: ORA-22905: cannot access rows from a non-nested table item - The non-nested table error message should lead you to wrap the call to the
TREATfunction in a call to theCOLLECTfunction, like this:COL base_id FORMAT 9999 HEADING "Base|ID #" COL customer_name FORMAT A38 HEADING "Customer Name" COL first_name FORMAT A6 HEADING "First|Name" COL middle_name FORMAT A6 HEADING "Middle|Name" COL last_name FORMAT A6 HEADING "Last|Name" SELECT * FROM TABLE( SELECT COLLECT(TREAT(customer_name AS person_t)) AS cte FROM customer);
It returns the substitutable column’s hidden column labels and their values:
Base First Middle Last ID # Name Name Name ----- ------ ------ ------ 1 Hank Pym 2 Scott Lang 3 Darren Cross - After learning how to unwrap the hidden columns of the substitutable column, you can now join the ordinary columns to the hidden columns like this:
COL customer_id FORMAT 9999 HEADING "Customer|ID #" COL base_id FORMAT 9999 HEADING "Base|ID #" COL customer_name FORMAT A38 HEADING "Customer Name" COL first_name FORMAT A6 HEADING "First|Name" COL middle_name FORMAT A6 HEADING "Middle|Name" COL last_name FORMAT A6 HEADING "Last|Name" SELECT c.customer_id , o.* FROM customer c INNER JOIN TABLE(SELECT COLLECT(TREAT(customer_name AS person_t)) AS cte FROM customer) o ON c.customer_id = o.base_id ORDER BY c.customer_id;
It returns the ordinary column and substitutable column’s hidden column labels and their values:
Customer Base First Middle Last ID # ID # Name Name Name -------- ----- ------ ------ ------ 1 1 Hank Pym 2 2 Scott Lang 3 3 Darren Cross - The preceding query only returns values when the substitutable column holds a value. It fails to return a value when the substitutable column holds a null value. You need to use a
LEFT JOINto ensure you see all ordinary columns whether or not the substitutable column holds a value.COL customer_id FORMAT 9999 HEADING "Customer|ID #" COL base_id FORMAT 9999 HEADING "Base|ID #" COL customer_name FORMAT A38 HEADING "Customer Name" COL first_name FORMAT A6 HEADING "First|Name" COL middle_name FORMAT A6 HEADING "Middle|Name" COL last_name FORMAT A6 HEADING "Last|Name" SELECT c.customer_id , o.* FROM customer c LEFT JOIN TABLE(SELECT COLLECT(TREAT(customer_name AS person_t)) AS cte FROM customer) o ON c.customer_id = o.base_id ORDER BY c.customer_id;
It returns the ordinary column and substitutable column’s hidden column labels and their values when the substitutable column holds an instance value. However, it only returns the ordinary column when the substitutable column holds a null value, as shown below:
Customer Base First Middle Last ID # ID # Name Name Name -------- ----- ------ ------ ------ 1 1 Hank Pym 2 2 Scott Lang 3 3 Darren Cross 4 - It should be noted that queries like this have a cost, and that cost is high. So, you should only implement substitutable columns when the maintenance coding costs (or sustaining engineering) outweighs the processing cost.
You can determine the cost like this:
EXPLAIN PLAN SET STATEMENT_ID = 'Strange' FOR SELECT c.customer_id , o.* FROM customer c LEFT JOIN TABLE(SELECT COLLECT(TREAT(customer_name AS person_t)) AS cte FROM customer) o ON c.customer_id = o.base_id ORDER BY c.customer_id;
You can query the cost like this:
SET LINESIZE 130 SELECT * FROM TABLE(dbms_xplan.display(NULL,'Strange'));
It should return something like this for the sample table and solution:
PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------------- Plan hash value: 2373055701 --------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 8168 | 550K| | 167 (2)| 00:00:03 | | 1 | SORT ORDER BY | | 8168 | 550K| 624K| 167 (2)| 00:00:03 | |* 2 | HASH JOIN OUTER | | 8168 | 550K| | 32 (4)| 00:00:01 | | 3 | TABLE ACCESS FULL | CUSTOMER | 5 | 15 | | 2 (0)| 00:00:01 | | 4 | VIEW | | 8168 | 526K| | 29 (0)| 00:00:01 | | 5 | COLLECTION ITERATOR PICKLER FETCH| | 8168 | | | 29 (0)| 00:00:01 | | 6 | SORT AGGREGATE | | 1 | 14 | | | | | 7 | TABLE ACCESS FULL | CUSTOMER | 5 | 70 | | 2 (0)| 00:00:01 | --------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("C"."CUSTOMER_ID"="O"."SYS_NC_ROWINFO$"."BASE_ID"(+))
It only raises the last column in the SELECT-list because that’s the first place where it fails to recognize an identifier, which is a valid column name in scope of the query.
As always, I hope this explains how to insert and query the hidden columns of a substitutable column, and how you join ordinary columns and hidden columns of a substitutable column from a table.
How to install MongoDB
This post shows the yum command to install the MongoDB packages on Linux. More on setup and use will follow.
You install the MongoDB package with the following yum command:
yum install -y mongodb |
You should see the following log file, which may also show a missing mirrored site:
Loaded plugins: langpacks, refresh-packagekit cassandra/signature | 819 B 00:00 cassandra/signature | 2.9 kB 00:00 !!! mysql-connectors-community | 2.5 kB 00:00 mysql-tools-community | 2.5 kB 00:00 mysql56-community | 2.5 kB 00:00 updates/20/x86_64/metalink | 2.6 kB 00:00 Resolving Dependencies --> Running transaction check ---> Package mongodb.x86_64 0:2.4.6-1.fc20 will be installed --> Processing Dependency: v8 for package: mongodb-2.4.6-1.fc20.x86_64 --> Processing Dependency: libv8.so.3()(64bit) for package: mongodb-2.4.6-1.fc20.x86_64 --> Processing Dependency: libtcmalloc.so.4()(64bit) for package: mongodb-2.4.6-1.fc20.x86_64 --> Processing Dependency: libboost_program_options.so.1.54.0()(64bit) for package: mongodb-2.4.6-1.fc20.x86_64 --> Processing Dependency: libboost_iostreams.so.1.54.0()(64bit) for package: mongodb-2.4.6-1.fc20.x86_64 --> Processing Dependency: libboost_filesystem.so.1.54.0()(64bit) for package: mongodb-2.4.6-1.fc20.x86_64 --> Running transaction check ---> Package boost-filesystem.x86_64 0:1.54.0-12.fc20 will be installed ---> Package boost-iostreams.x86_64 0:1.54.0-12.fc20 will be installed ---> Package boost-program-options.x86_64 0:1.54.0-12.fc20 will be installed ---> Package gperftools-libs.x86_64 0:2.1-4.fc20 will be installed ---> Package v8.x86_64 1:3.14.5.10-18.fc20 will be installed --> Finished Dependency Resolution Dependencies Resolved ================================================================================ Package Arch Version Repository Size ================================================================================ Installing: mongodb x86_64 2.4.6-1.fc20 fedora 30 M Installing for dependencies: boost-filesystem x86_64 1.54.0-12.fc20 updates 66 k boost-iostreams x86_64 1.54.0-12.fc20 updates 59 k boost-program-options x86_64 1.54.0-12.fc20 updates 147 k gperftools-libs x86_64 2.1-4.fc20 updates 266 k v8 x86_64 1:3.14.5.10-18.fc20 updates 3.0 M Transaction Summary ================================================================================ Install 1 Package (+5 Dependent packages) Total download size: 34 M Installed size: 101 M Downloading packages: (1/6): boost-iostreams-1.54.0-12.fc20.x86_64.rpm | 59 kB 00:00 (2/6): boost-filesystem-1.54.0-12.fc20.x86_64.rpm | 66 kB 00:00 (3/6): boost-program-options-1.54.0-12.fc20.x86_64.rpm | 147 kB 00:00 (4/6): gperftools-libs-2.1-4.fc20.x86_64.rpm | 266 kB 00:00 (5/6): v8-3.14.5.10-18.fc20.x86_64.rpm | 3.0 MB 00:00 (6/6): mongodb-2.4.6-1.fc20.x86_64.rpm | 30 MB 00:04 -------------------------------------------------------------------------------- Total 7.6 MB/s | 34 MB 00:04 Running transaction check Running transaction test Transaction test succeeded Running transaction (shutdown inhibited) Installing : 1:v8-3.14.5.10-18.fc20.x86_64 1/6 Installing : boost-program-options-1.54.0-12.fc20.x86_64 2/6 Installing : gperftools-libs-2.1-4.fc20.x86_64 3/6 Installing : boost-filesystem-1.54.0-12.fc20.x86_64 4/6 Installing : boost-iostreams-1.54.0-12.fc20.x86_64 5/6 Installing : mongodb-2.4.6-1.fc20.x86_64 6/6 Verifying : boost-iostreams-1.54.0-12.fc20.x86_64 1/6 Verifying : boost-filesystem-1.54.0-12.fc20.x86_64 2/6 Verifying : gperftools-libs-2.1-4.fc20.x86_64 3/6 Verifying : mongodb-2.4.6-1.fc20.x86_64 4/6 Verifying : boost-program-options-1.54.0-12.fc20.x86_64 5/6 Verifying : 1:v8-3.14.5.10-18.fc20.x86_64 6/6 Installed: mongodb.x86_64 0:2.4.6-1.fc20 Dependency Installed: boost-filesystem.x86_64 0:1.54.0-12.fc20 boost-iostreams.x86_64 0:1.54.0-12.fc20 boost-program-options.x86_64 0:1.54.0-12.fc20 gperftools-libs.x86_64 0:2.1-4.fc20 v8.x86_64 1:3.14.5.10-18.fc20 Complete! |
Hope this helps those looking for the command.
PostgreSQL Identity Columns
It’s interesting to see the way different databases implement automatic numbering. Oracle Database 12c is the closest to PostgreSQL in some significant ways. However, its probably more accurate to say Oracle Database 12c copied PostgreSQL’s implementation. At least, that’s my conjecture because Oracle added a way to reset the START WITH value of the indirect sequence. However, I prefer the MySQL approach because the automatic numbering sequence is a property of the table and a simple clause of the CREATE TABLE statement.
Both PostgreSQL and Oracle Database 12c implement automatic numbering as indirect sequences. Indirect sequences are those created by a table when you designate a column as an identity column in Oracle or as a serial column in PostgreSQL. The difference is that PostgreSQL doesn’t provide a syntax version inside the CREATE TABLE semantic.
MySQL provides such syntax. You set an auto numbering column in MySQL by appending the AUTO_INCREMENT clause to the table creation statement when you want it to start with a number other than 1, like this:
CREATE TABLE auto ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT , text_field VARCHAR(30) NOT NULL ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8; |
Oracle disallows you to changing a sequence created as a background activity of the CREATE TABLE statement; and Oracle disallows you dropping an indirect sequence without changing the table that created it, which is exactly how they handle indexes created for unique constraints. Unfortunately, Oracle also disallows altering the START WITH value of any sequence.
If you want to change the START WITH value on an Oracle Database 12c indirect sequence, you must export the table, drop the table, and recreate the table with a new START WITH value before importing the data back into the table. The syntax for setting an IDENTITY column value higher than 1 is:
CREATE TABLE auto ( auto_id NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1001) , text_field VARCHAR2(30) , CONSTRAINT auto_pk PRIMARY KEY (auto_id)); |
You can only create a PostgreSQL table with automatic numbering by using the SERIAL data type, which always sets the initial value to 1. You can reset the SERIAL sequence value in PostgreSQL with the ALTER statement. Unlike Oracle Database 12c, PostgreSQL does let you modify the START WITH value of any sequence. The trick is understanding how to find the sequence name. The name is always the combination of the table name, an underscore, an id string, an underscore, and a seq string. This behavior makes a great case for choosing id as the name of any auto numbering columns in a table.
CREATE TABLE auto ( id SERIAL CONSTRAINT auto_pk PRIMARY KEY , text_field VARCHAR(30)); ALTER SEQUENCE auto_id_seq RESTART WITH 1001; |
You can see the table and assigned sequence with the following command in PostgreSQL:
\d+ auto |
It should display:
Table "public.auto"
Column | Type | Modifiers | Storage | Stats target | Description
------------+-----------------------+---------------------------------------------------+----------+--------------+-------------
id | integer | not null default nextval('auto_id_seq'::regclass) | plain | |
text_field | character varying(30) | | extended | |
Indexes:
"auto_pk" PRIMARY KEY, btree (id)
Has OIDs: no |
As always, I hope this helps those trying to sort through how to start identity columns above the initial value of 1.
Subscribe via RSS