MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘Oracle 18c’ Category

Wrap Oracle SQL*Plus

without comments

One of the key problems with Oracle’s deployment is that you can not use the up-arrow key to navigate the sqlplus command-line history. Here’s little Bash shell function that you can put in your .bashrc file. It requires you to have your system administrator install the rlwrap package, which wraps the sqlplus command-line history.

You should also set the $ORACLE_HOME environment variable before you put this function in your .bashrc file.

sqlplus () 
{
    # Discover the fully qualified program name. 
    path=`which rlwrap 2>/dev/null`
    file=''
 
    # Parse the program name from the path.
    if [ -n ${path} ]; then
        file=${path##/*/}
    fi;
 
    # Wrap when there is a file and it is rewrap.
    if [ -n ${file} ] && [[ ${file} = "rlwrap" ]]; then
        rlwrap sqlplus "${@}"
    else
        echo "Command-line history unavailable: Install the rlwrap package."
        $ORACLE_HOME/bin/sqlplus "${@}"
    fi
}

If you port this shell script to an environment where rlwrap is not installed, it simply prints the error message and advises you to install the rlwrap package.

As always, I hope this helps those looking for a solution.

Written by maclochlainn

June 29th, 2020 at 10:53 pm

Posted in bash,DBA,Fedora,Linux,Oracle,Oracle 11g,Oracle 12c,Oracle 18c,Oracle DBA,Oracle Developer,SQL*Plus

Tagged with ,

Create Student User

without comments

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.

Written by maclochlainn

May 4th, 2020 at 8:01 pm

Posted in DBA,Oracle,Oracle 11g,Oracle 12c,Oracle 18c,pl/sql,sql

Tagged with ,

MySQL Linux to Windows

without comments

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.

Written by maclochlainn

May 3rd, 2020 at 11:58 am

Posted in Fedora,Linux,MySQL,MySQL 8,Oracle,Oracle 18c,PuTTy,Unix,Windows 10

Tagged with , , , ,

Session Variables

without comments

In MySQL and Oracle, you set a session variable quite differently. That means you should expect there differences between setting a session variable in Postgres. This blog post lets you see how to set them in all three databases. I’m always curious what people think but I’m willing to bet that MySQL is the simplest approach. Postgres is a bit more complex because you must use a function call, but Oracle is the most complex.

The difference between MySQL and Postgres is an “@” symbol versus a current_setting() function call. Oracle is more complex because it involves the mechanics in Oracle’s sqlplus shell, SQL dialect, and PL/SQL language (required to assign a value to a variable).

MySQL

MySQL lets you declare a session variable in one step and use it one way in a SQL statement or stored procedure.

  1. You set a session variable on a single line with the following command: 

    SET @my_variable_name := 'My Value';

  2. You can query a variable from the pseudo table dual or as a comparison value in the SELECT-list 

    SELECT @my_variable_name AS "The Value" FROM dual;

    or WHERE clause

    SELECT column_name
FROM   table_name
WHERE  column_name = @my_variable_name;

Postgres

Postgres lets you declare a session variable in one step and use it one way in a SQL statement or stored procedure.

  1. You set a session variable in a single line. It iss critical to note that you must use double quotes around the session variable name and single quotes for the value. You raise an error when you use a single quote instead a double quote around the session variable name. The syntax is: 

    SET SESSION "videodb.table_name" = 'new_hire';

  2. You can query a variable from the pseudo table dual or as a comparison value in the SELECT-list with the current_setting() function call. 

    SELECT current_setting('videodb.table_name') AS "The Value";

    or WHERE clause

    SELECT column_name
FROM   table_name
WHERE  column_name = current_setting('videodb.table_name');

Oracle

There are two steps required to declare a session variable in Oracle. First, you need to define the variable in the SQL*Plus session. Oracle lets you define a variable like you would define a variable in the C language, using extern before the variable’s type. Second, you assign a value to the session variable in an anonymous PL/SQL block. There is no single line statement to declare a variable with an initial value.

  1. You set a session variable by using the VARIABLE keyword, a variable name, and data type. The supported data types are: BLOB, BFILE, BINARY_DOUBLE, BINARY_FLOAT, CHAR, CLOB, NCHAR, NCLOB, NVARCHAR2, REFCURSOR, and VARCHAAR2. You define a variable with the following syntax: 

    VARIABLE bv_variable_name VARCHAR2(30)

  2. You assign a value to the bind variable inside an anonymous block by prefacing the variable name with a colon. You assign values inside PL/SQL with the walrus operator (:=) and a string enclosed by single quotes. Anonymous blocks start with a BEGIN and end with an END followed by a semicolon (;) and a forward slash (/) to dispatch the block for execution. The following example shows a full block: 

    BEGIN
  :bv_variable_name := 'Some Value';
END;
/

  3. You can query any declared variable from the pseudo table dual or as a comparison value in the SELECT-list 

    SELECT :bv_variable_name FROM dual;

    or WHERE clause

    SELECT column_name
FROM   table_name
WHERE  column_name = :bv_variable_name;

Written by maclochlainn

September 28th, 2019 at 9:01 pm

Posted in MySQL,MySQL 8,Oracle,Oracle 11g,Oracle 12c,Oracle 18c,Postgres,PostgreSQL,Uncategorized

Tagged with , ,

Misleading ORA- Message

without comments

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.

Written by maclochlainn

August 17th, 2019 at 4:52 pm

Posted in Database Design,DBA,Oracle,Oracle 11g,Oracle 12c,Oracle 18c,Oracle DBA,Oracle Developer,Oracle XE,pl/sql

Tagged with ,

Newer Entries »