Archive for the ‘Oracle DBA’ tag
Create Student User
It’s amazing how old some of my students’ computers are. The oldest with least memory are the foreign students. Fortunately, I kept copies of the old Oracle Database 10g XE. I give it to some students who need to run the smallest possible option. Then, again I have students who get emotional about having to use Unix or Linux as an operating system, which means I now also support Oracle Database 18c.
Anyway, I had to write a script that would support building a small 200 MB student schema in any of the Express Edition databases from 10g to 18c. Here’s the script for those who would like to use it. It sets up a student schema for Oracle Database 10g and 11g databases and a c##student schema for Oracle’s Containized Database 12c and 18c.
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 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 | DECLARE /* Control variable. */ container BOOLEAN := FALSE; /* Weakly structured system reference cursor. */ container_sql SYS_REFCURSOR; /* Constant required for pre-container databases to avoid a a compile time error. */ sql_statement CONSTANT VARCHAR2(50) := 'SELECT cdb FROM v$database WHERE cdb = ''YES'''; BEGIN /* Check if the current user is the superuser. */ FOR i IN (SELECT USER FROM dual) LOOP /* Perform tasks as superuser. */ IF i.USER = 'SYSTEM' THEN /* Check for a container-enabled column, which enables this to work in both pre-container Oracle databases, like 10g and 11g. */ FOR j IN (SELECT DISTINCT column_name FROM dba_tab_columns WHERE column_name = 'CDB') LOOP /* Check for a container database, set control variable and exit when found. */ OPEN container_sql FOR sql_statement; LOOP container := TRUE; EXIT WHEN container_sql%FOUND; END LOOP; END LOOP; /* Conditionally drop existing user and role. */ IF container THEN /* Conditionally drop a container user. */ FOR j IN (SELECT username FROM dba_users WHERE username = 'C##STUDENT') LOOP EXECUTE IMMEDIATE 'DROP USER c##student CASCADE'; END LOOP; /* Conditionally rop the container c##studentrole role. */ FOR j IN (SELECT ROLE FROM dba_roles WHERE ROLE = 'C##STUDENTROLE') LOOP EXECUTE IMMEDIATE 'DROP ROLE c##studentrole'; END LOOP; /* Create a container user with 200 MB of space. */ EXECUTE IMMEDIATE 'CREATE USER c##student'||CHR(10) || 'IDENTIFIED BY student'||CHR(10) || 'DEFAULT TABLESPACE users'||CHR(10) || 'QUOTA 200M ON users'||CHR(10) || 'TEMPORARY TABLESPACE temp'; /* Create a container role. */ EXECUTE IMMEDIATE 'CREATE ROLE c##studentrole CONTAINER = ALL'; /* Grant privileges to a container user. */ EXECUTE IMMEDIATE 'GRANT CREATE CLUSTER, CREATE INDEXTYPE,'||CHR(10) || 'CREATE PROCEDURE, CREATE SEQUENCE,'||CHR(10) || 'CREATE SESSION, CREATE TABLE,'||CHR(10) || 'CREATE TRIGGER, CREATE TYPE,'||CHR(10) || 'CREATE VIEW TO c##studentrole'; /* Grant role to user. */ EXECUTE IMMEDIATE 'GRANT c##studentrole TO c##student'; ELSE /* Conditonally drop the non-container database user. */ FOR j IN (SELECT username FROM dba_users WHERE username = 'STUDENT') LOOP EXECUTE IMMEDIATE 'DROP USER student CASCADE'; END LOOP; /* Create the student database. */ EXECUTE IMMEDIATE 'CREATE USER student'||CHR(10) || 'IDENTIFIED BY student'||CHR(10) || 'DEFAULT TABLESPACE users'||CHR(10) || 'QUOTA 200M ON users'||CHR(10) || 'TEMPORARY TABLESPACE temp'; /* Grant necessary privileges to the student database. */ EXECUTE IMMEDIATE 'GRANT CREATE CLUSTER, CREATE INDEXTYPE,'||CHR(10) || 'CREATE PROCEDURE, CREATE SEQUENCE,'||CHR(10) || 'CREATE SESSION, CREATE TABLE,'||CHR(10) || 'CREATE TRIGGER, CREATE TYPE,'||CHR(10) || 'CREATE VIEW TO student'; END IF; ELSE /* Print an message that the user lacks privilegs. */ dbms_output.put_line('You must be the SYSTEM user to drop and create a user.'); END IF; END LOOP; END; / |
As always, I hope this helps those looking for a solution beyond Quest’s Toad for Oracle, APEX, or SQL Developer. Let me know if you like it.
MySQL Linux to Windows
My students want to transfer their course setup MySQL files from the Linux VM that I provide to Microsoft Windows 10. This post explains how because I found a couple small errors in the Google’d posts they would most likely see.
The first step is required because when I didn’t assign a name or domain to the the Fedora VM, which allows it to run as localhost on any student machine. In tandem, I didn’t assign a static IP address but opted for dynamic IP assignment. That means, the first step to securely copy the files requires you to find the assigned IP address. You can do that with the following Linux command:
ifconfig -a | grep 'inet[[:blank:]]' | head -1 | cut -c 14-30 |
It would return something like:
192.168.147.198 |
After you have discovered the IP address, you need to download PuTTy from their web site because includes the pscp (PuTTy Secure Copy) utility. I recommend you click on the MSI (Microsoft Installer Package) file, and install it on your Windows machine. As a rule, you should accept the default location, which is C:\Program Files\PuTTy.
While you could alter your system-level %PATH% environment variable after you install the software, I recommend you only include it in the %PATH% within the scope of a Command (cmd) shell. Navigate to your Windows Start and enter cmd in the search field. It should launch the Command Prompt terminal, which is a terminal emulator.
In the terminal editor, type the following case sensitive command to add the PuTTy directory to your path (yes, it’s case sensitive):
SET PATH=%PATH%;%ProgramFiles%\PuTTy |
Now, you can securely copy the files and directory structure from Linux to Windows with the following command (where you replace the traditional server name with the dynamically assigned IP address). You should also be in the target directory where you want the files and directories copied:
C:\Data\cit225>pscp -r student@192.168.147.198:/home/student/Data/cit225/mysql . |
After entering the password for the student on the Linux VM, you should see the following copy over:
Raiders2.png | 99 kB | 99.5 kB/s | ETA: 00:00:00 | 100% LordOfTheRings3.png | 119 kB | 119.8 kB/s | ETA: 00:00:00 | 100% HarryPotter4.png | 103 kB | 103.9 kB/s | ETA: 00:00:00 | 100% Raiders1.png | 92 kB | 92.4 kB/s | ETA: 00:00:00 | 100% Raiders3.png | 123 kB | 123.9 kB/s | ETA: 00:00:00 | 100% LordOfTheRings2.png | 111 kB | 111.7 kB/s | ETA: 00:00:00 | 100% LordOfTheRings1.png | 103 kB | 104.0 kB/s | ETA: 00:00:00 | 100% HarryPotter2.png | 118 kB | 118.7 kB/s | ETA: 00:00:00 | 100% HarryPotter7.png | 150 kB | 150.2 kB/s | ETA: 00:00:00 | 100% HarryPotter3.png | 106 kB | 106.1 kB/s | ETA: 00:00:00 | 100% HarryPotter5.png | 82 kB | 82.5 kB/s | ETA: 00:00:00 | 100% HarryPotter6.png | 129 kB | 129.9 kB/s | ETA: 00:00:00 | 100% HarryPotter1.png | 118 kB | 118.8 kB/s | ETA: 00:00:00 | 100% HarryPotter8.png | 150 kB | 150.9 kB/s | ETA: 00:00:00 | 100% HarryPotter8.txt | 8 kB | 8.5 kB/s | ETA: 00:00:00 | 100% HarryPotter3.txt | 5 kB | 5.8 kB/s | ETA: 00:00:00 | 100% HarryPotter5.txt | 7 kB | 7.9 kB/s | ETA: 00:00:00 | 100% HarryPotter1.txt | 6 kB | 6.6 kB/s | ETA: 00:00:00 | 100% HarryPotter2.txt | 7 kB | 7.8 kB/s | ETA: 00:00:00 | 100% Raiders3.txt | 5 kB | 5.6 kB/s | ETA: 00:00:00 | 100% HarryPotter4.txt | 7 kB | 7.5 kB/s | ETA: 00:00:00 | 100% HarryPotter7.txt | 5 kB | 5.4 kB/s | ETA: 00:00:00 | 100% HarryPotter6.txt | 7 kB | 7.4 kB/s | ETA: 00:00:00 | 100% LOTRFellowship.txt | 4 kB | 5.0 kB/s | ETA: 00:00:00 | 100% apply_store_base.sql | 1 kB | 1.6 kB/s | ETA: 00:00:00 | 100% query_membership.sql | 0 kB | 0.3 kB/s | ETA: 00:00:00 | 100% apply_mysql_lab1.sql | 1 kB | 1.9 kB/s | ETA: 00:00:00 | 100% configure_mysql_web.sql | 37 kB | 37.1 kB/s | ETA: 00:00:00 | 100% seed_mysql_store_ri2.sql | 58 kB | 58.5 kB/s | ETA: 00:00:00 | 100% cleanup_mysql_store.sql | 5 kB | 5.4 kB/s | ETA: 00:00:00 | 100% create_mysql_store_ri2.sq | 21 kB | 21.1 kB/s | ETA: 00:00:00 | 100% |
My students will need to repeat this step to transfer all of the sample PHP files that demonstrate web application patterns. They also need to inspect individual files to ensure any path referencing commands are manually converted to their new Windows equivalent.
They can move the physical files as the root superuser with the following pscp command provide you haven’t stored the files somewhere other than the default location:
C:\Data\temp>pscp -r root@192.168.147.198:/var/lib/mysql . |
As always, I hope this helps those trying to sort things out.
SQL Developer Error
It’s been a couple releases trying to fix the following error thrown by SQL Developer on Fedora 30 and shown as the following dialog:
When you click the Detail button it shows the following error stack:
java.lang.NoClassDefFoundError: javafx/embed/swing/JFXPanel at oracle.dbtools.raptor.javafx.ui.JFXPanelFactory.createJFXPanelImpl(JFXPanelFactory.java:58) at oracle.dbtools.raptor.javafx.ui.JFXPanelFactory.createJFXPanel(JFXPanelFactory.java:34) at oracle.dbtools.raptor.startpage.StartPageViewer.createGUIComponent(StartPageViewer.java:179) at oracle.dbtools.raptor.startpage.StartPageViewer.getEditorContent(StartPageViewer.java:136) at oracle.ide.editor.AsynchronousEditor$2.run(AsynchronousEditor.java:345) at oracle.ide.editor.AsynchronousEditor$5.run(AsynchronousEditor.java:555) at org.openide.util.RequestProcessor$Task.run(RequestProcessor.java:1443) at org.netbeans.modules.openide.util.GlobalLookup.execute(GlobalLookup.java:68) at org.openide.util.lookup.Lookups.executeWith(Lookups.java:303) at org.openide.util.RequestProcessor$Processor.run(RequestProcessor.java:2058) Caused by: java.lang.ClassNotFoundException: javafx.embed.swing.JFXPanel cannot be found by oracle.sqldeveloper_19.2.0 at org.eclipse.osgi.internal.loader.BundleLoader.findClassInternal(BundleLoader.java:501) at org.eclipse.osgi.internal.loader.BundleLoader.findClass(BundleLoader.java:421) at org.eclipse.osgi.internal.loader.BundleLoader.findClass(BundleLoader.java:412) at org.eclipse.osgi.internal.baseadaptor.DefaultClassLoader.loadClass(DefaultClassLoader.java:107) at org.netbeans.modules.netbinox.NetbinoxLoader.loadClass(NetbinoxLoader.java:81) at java.lang.ClassLoader.loadClass(ClassLoader.java:352) ... 10 more |
I thought applying the Open Java FX package might fix the problem. I installed the package like the following:
yum install -y openjfx |
The installation log:
Last metadata expiration check: 4:03:29 ago on Tue 21 Apr 2020 06:42:26 PM MDT. Dependencies resolved. ============================================================================================= Package Architecture Version Repository Size ============================================================================================= Installing: openjfx x86_64 8.0.202-8.b07.fc30 updates 8.8 M Transaction Summary ============================================================================================= Install 1 Package Total download size: 8.8 M Installed size: 11 M Downloading Packages: openjfx-8.0.202-8.b07.fc30.x86_64.rpm 2.5 MB/s | 8.8 MB 00:03 --------------------------------------------------------------------------------------------- Total 2.1 MB/s | 8.8 MB 00:04 Running transaction check Transaction check succeeded. Running transaction test Transaction test succeeded. Running transaction Preparing : 1/1 Installing : openjfx-8.0.202-8.b07.fc30.x86_64 1/1 Running scriptlet: openjfx-8.0.202-8.b07.fc30.x86_64 1/1 Verifying : openjfx-8.0.202-8.b07.fc30.x86_64 1/1 Installed: openjfx-8.0.202-8.b07.fc30.x86_64 Complete! |
After installing the software, I determined the new JAR files. Then, I added them to my $CLASSPATH environment variable, like:
export CLASSPATH=/usr/share/java/mysql-connector-java.jar:/usr/lib/jvm/openjfx/rt/lib/ext/fxrt.jar:/usr/lib/jvm/openjfx/rt/lib/jfxswt.jar:. |
While it appears to load faster with these JAR files, it still raises the same Dialog error. I simply have to continue to look for a complete fix.
Postgres SQL Nuance
I ran across an interesting nuance between Oracle and Postgres with the double-pipe operator. I found that the following query failed to cross port from Oracle to Postgres:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | COL account_number FORMAT A10 HEADING "Account|Number" COL full_name FORMAT A16 HEADING "Name|(Last, First MI)" COL city FORMAT A12 HEADING "City" COL state_province FORMAT A10 HEADING "State" COL telephone FORMAT A18 HEADING "Telephone" SELECT m.account_number , c.last_name || ', ' || c.first_name || CASE WHEN c.middle_name IS NOT NULL THEN ' ' || c.middle_name END AS full_name , a.city , a.state_province , t.country_code || '-(' || t.area_code || ') ' || t.telephone_number AS telephone FROM member m INNER JOIN contact c ON m.member_id = c.member_id INNER JOIN address a ON c.contact_id = a.contact_id INNER JOIN street_address sa ON a.address_id = sa.address_id INNER JOIN telephone t ON c.contact_id = t.contact_id AND a.address_id = t.address_id WHERE c.last_name = 'Winn'; |
In Oracle, a CASE statement ignores the null of a missing ELSE clause between lines 4 and 5. Oracle assumes a null value is an empty string when concatenated to a string with the double-piped concatenation operator. Oracle’s implementation differs from the ANSI standard and is non-compliant.
It would display the following thanks to the SQL reporting features that don’t exist in other Command-Line Interface (CLI) implementations, like mysql, psql, sqlcmd, or cql:
Account Name Number (Last, First MI) City State Telephone ---------- ---------------- ------------ ---------- ------------------ B293-71445 Winn, Randi San Jose CA 001-(408) 111-1111 B293-71445 Winn, Brian San Jose CA 001-(408) 111-1111 |
However, it fails in Postgres without a notice, warning, or error. Postgres simply returns a null string for the missing ELSE clause and follows the rule that any string concatenated against a null is a null. That means it retunes a null value for the full_name column above. The Postgres behavior is the ANSI standard behavior. After years of working with Oracle it was interesting to have this pointed out while porting a query.
You can fix the statement in Postgres by adding an explicit ELSE clause on a new line 5 that appends an empty string, like this:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | SELECT m.account_number , c.last_name || ', ' || c.first_name || CASE WHEN c.middle_name IS NOT NULL THEN ' ' || c.middle_name ELSE '' END AS full_name , a.city , a.state_province , t.country_code || '-(' || t.area_code || ') ' || t.telephone_number AS telephone FROM member m INNER JOIN contact c ON m.member_id = c.member_id INNER JOIN address a ON c.contact_id = a.contact_id INNER JOIN street_address sa ON a.address_id = sa.address_id INNER JOIN telephone t ON c.contact_id = t.contact_id AND a.address_id = t.address_id WHERE c.last_name = 'Winn'; |
It would display:
account_number | full_name | city | state_province | telephone ----------------+-------------+----------+----------------+-------------------- B293-71445 | Winn, Randi | San Jose | CA | 001-(408) 111-1111 B293-71445 | Winn, Brian | San Jose | CA | 001-(408) 111-1111 (2 rows) |
As always, I hope this helps those looking to solve a problem.
Misleading ORA- Message
Oracle error messages are more or less the best in the industry but time-to-time they saddle you with a bad or misleading message. For example, I was running one of the code modules from my Oracle Database 12c PL/SQL Programming book for a class exercise and got this error message:
BEGIN * ERROR AT line 1: ORA-22288: FILE OR LOB operation failed ORA-06512: AT "STUDENT.LOAD_CLOB_FROM_FILE", line 71 ORA-06512: AT line 11 |
Oddly enough, it was simple to identify generally. It failed on a call to the DBMS_LOB.LOADCLOBFROMFILE procedure. However, the better question is why did it fail because the virtual directory resolved and the permissions worked.
The first test was to try another file, which worked perfectly with the same code. That meant it had to be something with the physical file. I took a look and sure enough I found a character set problem, like the following:
… he reveals that the Nazgûl, or Ringwraiths, have left Mordor to capture the Ring and kill whoever carries it.
and,
The group flees to the elvish realm of Lothlórien …
The “û” and “ó” characters were incompatible with the default NLS_LANG setting of the database and a CLOB limits the use of non-standard character sets. It’s ashamed that Oracle didn’t through a character set error, which would have expedited resolution of the problem.
As always, I hope this helps those looking for solutions.
Oracle Error Bash f(x)
My students always struggle initially with basic Linux skills. I wrote little function for their .bashrc file to help them avoid the frustration. It finds and displays all errors by file name, line number and error message for a collection of log files in a single directory (or folder).
errors() { # Determine if any log files exist and check for errors. label="File Name:Line Number:Error Code" list=`ls ./*.$1 | wc -l` if [[ $list} -eq 1 ]]; then echo ${label} echo "--------------------------------------------------" filename=`ls *.txt` echo ${filename}:`find . -type f | grep -in *.txt -e ora\- -e pls\- -e sp2\-` elif [[ ${list} -gt 1 ]]; then echo ${label} echo "--------------------------------------------------" find . -type f | grep -in *.txt -e ora\- -e pls\- -e sp2\- fi } |
Let’s say you name your log files with a file extension of .txt, then you would call the function like this:
errors txt |
It would return output like the following:
common_lookup_lab.txt:229:ORA-02275: such a referential constraint already exists in the table common_lookup_lab.txt:239:ORA-02275: such a referential constraint already exists in the table |
As always, I hope this helps those looking for a solution.
Create Oracle User
After you create and provision the Oracle Database 11g XE, you create an instance with the following two step process.
- Create a
studentOracle user account with the following command:CREATE USER student IDENTIFIED BY student DEFAULT TABLESPACE users QUOTA 200M ON users TEMPORARY TABLESPACE temp;
- Grant necessary privileges to the newly created
studentuser:GRANT CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR , CREATE PROCEDURE, CREATE SEQUENCE, CREATE SESSION , CREATE TABLE, CREATE TRIGGER, CREATE TYPE , CREATE VIEW TO student;
As always, I hope this helps those looking for how to do something that’s less than clear because everybody uses tools.
Fedora 30 Missing Library
Having run into an obsolete library issue installing Oracle Database 18c XE on Fedora, Version 30, I opted to revert my student image to Oracle Database 11g XE. The installation went without issue but when I tried to log into SQL*Plus as the oracle user, I got the following error message:
sqlplus: error while loading shared libraries: libnsl.so.1: cannot open shared object file: No such file or directory |
The libnsl.so.1 library is no longer installed as part of the distribution for Fedora 28 forward but you can install it with the yum tool, like:
yum install -y libnsl |
Display detailed console log →
Last metadata expiration check: 1:35:44 ago on Sun 11 Aug 2019 07:28:07 PM MDT. Dependencies resolved. ============================================================================= Package Architecture Version Repository Size ============================================================================= Installing: libnsl x86_64 2.29-15.fc30 updates 97 k Transaction Summary ============================================================================= Install 1 Package Total download size: 97 k Installed size: 287 k Downloading Packages: libnsl-2.29-15.fc30.x86_64.rpm 134 kB/s | 97 kB 00:00 ----------------------------------------------------------------------------- Total 69 kB/s | 97 kB 00:01 Running transaction check Transaction check succeeded. Running transaction test Transaction test succeeded. Running transaction Preparing : 1/1 Installing : libnsl-2.29-15.fc30.x86_64 1/1 Running scriptlet: libnsl-2.29-15.fc30.x86_64 1/1 Verifying : libnsl-2.29-15.fc30.x86_64 1/1 Installed: libnsl-2.29-15.fc30.x86_64 Complete! |
If you attempted to run the oracle-xe utility to configure the database prior to adding this library, it fails to provision the instance without a message. You won’t get the message until you manually try to connect as the sysdba privileged user. At that point, you’ll determine the instance wasn’t provisioned.
You can see that the installation failed when the oracle-xe utility fails to print the following lines to the console after the options are entered:
Starting Oracle Net Listener...Done Configuring database...Done Starting Oracle Database 11g Express Edition instance...Done Installation completed successfully. |
After installing the missing library, the oracle-xe utility works correctly. Alas, it looks like I’ll never bother to sort the Oracle Database 18c XE issues because after this version of the image we are moving the courses to a PostgreSQL database. PostgreSQL offers the smaller footprint that supports the core learning objectives of the courses.
As always, I hope this helps those looking for a solution.
Add user as sudoer
Somebody asked why adding a user to the wheel group in didn’t enable them as a sudoer, as qualified in my earlier Fedora post. The reason is that you also need to modify the primary group in the /etc/passwd file to specify the Group ID value for the wheel group as the primary group of the designated student user.
You can identify the Group ID with the following command:
cat /etc/group | grep wheel |
It should return the following for the wheel group:
wheel:x:10:student |
You need to check the target student user in the /etc/passwd file, which you can do with the following command:
cat /etc/passwd | grep student |
It should return the following for the student user, which has a default group value equal to the user of the same name:
student:x:1000:1000:Student:/home/student:/bin/bash |
As the root user, edit the /etc/passwd file to correct the student user’s primary group ID, as follows:
student:x:1000:10:Student:/home/student:/bin/bash |
You should see the following two lines. If you want authorized sudoers to provide a password (recommended), then modify the first line by removing the # comment. If you don’t want authorized sudoers to provide a password, modify the second line by removing the # comment. Open the /etc/sudoers file with vi or gedit if you’d like a GUI editor.
# %wheel ALL=(ALL) ALL # %wheel ALL=(ALL) NOPASSWD: ALL |
Hope this helps. It’s a quick update for Fedora 30, you su to root and add your user to the sudoers list with the following syntax:
usermod someusername -a -G wheel |
By the way, don’t forget to log off and then back on to the account.
Find files with errors
My students wanted a quick solution on how to find the log files that contain errors. That’s a simple line of code in Linux if you want any Oracle errors that start with ORA-:
find $HOME/lab2 -type f | xargs grep -i ora\- |
It takes only a moment more to look for errors starting with ORA- or PLS-, like:
find $HOME/lab2 -type f | xargs grep -i -e ora\- -e pls\- |
The latter might return something like this:
contact_lab.txt:ORA-00904: "MEMBER_LAB_ID": invalid identifier contact_lab.txt:ORA-00942: table or view does not exist contact_lab.txt:ORA-00942: table or view does not exist member_lab.txt:ORA-02264: name already used by an existing constraint member_lab.txt:ORA-00955: name is already used by an existing object |
You can improve the error identification by identifying line numbers by adding -n option, like:
find $HOME/lab2 -type f | xargs grep -in -e ora\- -e pls\- |
The latter might return something like this when there are two or more files:
contact_lab.txt:76:ORA-00904: "MEMBER_LAB_ID": invalid identifier contact_lab.txt:150:ORA-00942: table or view does not exist contact_lab.txt:157:ORA-00942: table or view does not exist member_lab.txt:75:ORA-02264: name already used by an existing constraint member_lab.txt:149:ORA-00955: name is already used by an existing object |
Unfortunately, the command raises an error when there aren’t any files found of with a qualified extension. It also fails to prepend the file name when there’s only one qualified file name. As a result of these deficiencies, I’ve written the following Bash shell script. I’ve opted to call it the .findErrors.bashrc file name and deploy it in the user’s $HOME directory.
#!/bin/bash # Assign any file filter to the ext variable. ext=${1} # Assign the extension or simply use a wildcard for all files. if [ ! -z ${ext} ]; then ext="*.${ext}" else ext="*" fi # Assign the number of qualifying files to a variable. fileNum=$(ls -l ${ext} 2>/dev/null | grep -v ^l | wc -l) # Evaluate the number of qualifying files and process. if [ ${fileNum} -eq "0" ]; then echo "[0] files exist." elif [ ${fileNum} -eq "1" ]; then fileName=$(ls ${ext}) find `pwd` -type f | grep -in ${ext} -e ora\- -e pls\- | while IFS='\n' read list; do echo "${fileName}:${list}" done else find `pwd` -type f | grep -in ${ext} -e ora\- -e pls\- | while IFS='\n' read list; do echo "${list}" done fi |
You can modify the errors() function with or without a file extension to identify errors beginning with ORA- or PLS- in their log files. As always, I hope this helps those looking for a solution.
Subscribe via RSS