Archive for the ‘SQL*Plus’ Category
SQL Insert from Query
Sometimes my students find new errors that I’ve never seen. One student did that this week by including an ORDER BY
clause in a subquery that feeds an INSERT
statement. It raises an ORA-00907
exception, like:
ORA-00907: missing right parenthesis |
You can’t include a subquery with an ORDER BY
clause because it generates an error. The reason is simple. A subquery can’t perform a sort operation inside a subquery. Here’s a quick demonstration:
DROP TABLE destination; CREATE TABLE destination ( destination_id NUMBER , destination_name VARCHAR2(20)); INSERT INTO destination ( SELECT 1,'Sample1' FROM dual UNION ALL SELECT 2,'Sample2' FROM dual ORDER BY 1 DESC ); |
If you remove the ORDER BY
clause, the statement works without a problem. For example, here’s the working version:
INSERT INTO destination ( SELECT 1,'Sample1' FROM dual UNION ALL SELECT 2,'Sample2' FROM dual ); |
Alternatively, you can include an ORDER BY
clause when you remove the parentheses from around the subquery. This is an example:
INSERT INTO destination SELECT 1,'Sample1' FROM dual UNION ALL SELECT 2,'Sample2' FROM dual ORDER BY 1 DESC; |
I hope this helps anybody who runs into the problem.
Provision Oracle 12c PDB
Somebody wants help provisioning a pluggable database (PDB). While it’s a new concept in the Oracle 12c database, I can sympathize with their plight. It wasn’t clear to me when first working with it, and I couldn’t seem to find a quick tutorial on the web. While it’s in the new Oracle 12c PL/SQL Programming Book, that won’t be out until January 2014.
If you’re new to Oracle 12c, which is most of us, then it’s important to understand that PDB is a private data context. Most of it’s data catalog is private and separate from the overall database. Only a small portion of the database catalog is stored in the container database catalog, and new CDB_ administrative views are added to the database. A PDB is a great solution when you’re leveraging the Editioning feature of Oracle 11g database.
You should note the guarantee from page #9 of Oracle’s Oracle Multitenant:
From the point of view of the client connecting via Oracle Net, the PDB is the database. A PDB is fully compatible with a non-CDB. We shall refer to this from now on as the PDB/non-CDB compatibility guarantee. In other words, the installation scheme for an application backend that ran without error against a non-CDB will run, with no change, and without error, in a PDB and will produce the same result.
Here are the steps that work on both Windows, Linux, or Windows:
- The following SQL command lets you create a pluggable database (PDB) with a
video
user assigned to it:
1 2 3 4 5 6 7 | CREATE PLUGGABLE DATABASE videodb ADMIN USER videoadm IDENTIFIED BY Video1 ROLES = (dba) DEFAULT TABLESPACE videots DATAFILE 'C:\APP\ORACLE\ORADATA\ORCL\VIDEO01.DBF' SIZE 500M ONLINE FILE_NAME_CONVERT = ('C:\APP\ORACLE\ORADATA\ORCL\PDBSEED\', 'C:\APP\ORACLE\ORADATA\ORCL\VIDEOPDB\'); |
Don’t try to create the DEFAULT TABLESPACE
before you provision the database. If you do, you’ll get the following exception:
CREATE PLUGGABLE DATABASE videodb * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-01537: cannot add file 'C:\APP\ORACLE\ORADATA\ORCL\VIDEO01.DBF' - file already part of database |
- You need to stop the Oracle listener and modify the
listener.ora
file. Lines 9 through 12 configure aVIDEODB
Oracle SID. After you make the changes, start the Oracle listener.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = CLRExtProc) (ORACLE_HOME = C:\app\oracle\product\12.1.0\dbhome_1) (PROGRAM = extproc) (ENVS = "EXTPROC_DLLS=ONLY:C:\app\oracle\product\12.1.0\dbhome_1\bin\oraclr12.dll") ) (SID_DESC = (SID_NAME = VIDEODB) (ORACLE_HOME = C:\app\oracle\product\12.1.0\dbhome_1) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) ) ) |
- You also need to add a
VIDEO
TNS alias to thetnsnames.ora
file for theVIDEODB
pluggable database (PDB).
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 | ORACLR_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) (CONNECT_DATA = (SID = CLRExtProc) (PRESENTATION = RO) ) ) ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) ) VIDEO = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = videodb) ) ) |
- You connect as the
SYSDBA
for theVIDEO
pluggable database with the following syntax (not presently an example in the SQL*Plus Guide). After connecting as theSYSDBA
for theVIDEODB
, you’ll be prompted for a password. The required password is theVideo1
password that you used when you set up theVIDEODB
database.
sqlplus sys@VIDEO AS sysdba |
- After authenticating as the
SYSDBA
, you need to start theVIDEODB
pluggable database, like:
SQL> startup Pluggable DATABASE opened. |
You can also start the pluggable database from the generic SYSDBA
account. You only need to change the CONTAINER
context to pluggable database. You can find the status of pluggable databases with the following query:
SQL> COLUMN RESTRICTED FORMAT A10 SQL> SELECT v.name 2 , v.open_mode 3 , NVL(v.restricted, 'n/a') AS restricted 4 , d.status 5 FROM v$PDBs v INNER JOIN dba_pdbs d USING(guid) 6 ORDER BY v.create_scn; |
The generic sample database returns this after restarting the database:
NAME OPEN_MODE RESTRICTED STATUS ------------------------------ ---------- ---------- -------- PDB$SEED READ ONLY NO NORMAL PDBORCL MOUNTED n/a NORMAL |
The following changes the CONTAINER
context, opens the pdborcl
PDB, and creates a traditional Oracle 11g and backward standard user/schema account in the pluggable database.
SQL> ALTER SESSION SET container=pdborcl; SQL> ALTER PLUGGABLE DATABASE pdborcl OPEN; SQL> CREATE USER johnny IDENTIFIED BY johnny; |
Re-query the status of the pluggable databases, and you’ll see:
NAME OPEN_MODE RESTRICTED STATUS ------------------------------ ---------- ---------- -------- PDB$SEED READ ONLY NO NORMAL PDBORCL READ WRITE NO NORMAL |
If you have more than one pluggable database, you can use the following to open them all as the CDB’s SYSDBA
:
SQL> ALTER PLUGGABLE DATABASE ALL OPEN; |
- As Oracle said during the Oracle 11gR2 release, the DBA role no longer grants
UNLIMITED TABLESPACE
. That means you need to grant it as theSYSDBA
for the PDB, like:
GRANT UNLIMITED TABLESPACE TO videoadm; |
- After you’ve done all the prior steps, you can connect with the following as the Administrative
VIDEO
user:
sqlplus videoadm@VIDEO/Video1 |
Although making such a connection is a poor choice, it would be better to make a secure connection like the one below. The problem with the former is that it exposes information at the OS command line that would be visible to other users. Connecting to the Oracle SQL*Plus with the /NOLOG
option prevents disclosure of that information.
C:\Users\mclaughlinm>sqlplus /nolog SQL*Plus: Release 12.1.0.1.0 Production ON Tue Aug 13 01:28:30 2013 Copyright (c) 1982, 2013, Oracle. ALL rights reserved. SQL> CONNECT videoadm@VIDEO/Video1 Connected. SQL> SHOW USER USER IS "VIDEOADM" |
- Once you’re connected as the
SYSDBA
role, you can create standard (pre-Oracle 12c) user/schemas with the old syntax. Below, we create a student account:
SQL> CREATE USER student IDENTIFIED BY student; SQL> GRANT CREATE cluster, CREATE indextype, CREATE operator 2 , CREATE PROCEDURE, CREATE SEQUENCE, CREATE SESSION 3 , CREATE TABLE, CREATE TRIGGER, CREATE TYPE 4 , CREATE VIEW, UNLIMITED TABLESPACE TO student; |
Naturally, you can also add a USER
from the CDB’s SYSDBA
role when in the proper CONTAINER
context.
This has shown you how to provision a pluggable database (PDB). As a side note, you should know that you can only create user-defined common users (at the CDB-level) with a c##
prefix.
An addendum: DBMS_COMPARE
isn’t provisioned automatically, and you need to manually apply it in your ADMIN
user for the PDB. I’ve blogged about it here.
SELECT-INTO variables
Somebody said, I shouldn’t have buried so much information in an answer to a question posed in a comment on a summary blog page. They suggested I put it in a regular blog post, and here it is with as little editing as possible.
The Ron Quizon user provided this sample code and a “What’s wrong with this PL/SQL program?”
1 2 3 4 5 6 7 8 9 10 11 12 13 | DECLARE v_name friends.fname%TYPE; v_grade friends.id%TYPE; BEGIN SELECT fname, grade INTO &ssv_name, v_grade FROM friends WHERE v_name = fname; DBMS_OUTPUT.PUT_LINE(NVL(v_name,'No Name ')||' has an id of '||NVL(v_grade, 0)); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ('There is no record with '||'id 123'); END; |
While this certainly looks like a question from a class on PL/SQL or something from Steven Feuerstein’s PL/SQL question quizzes, I paused before answering it. The give away is the style is what Steven’s advocated for two decades. My guess is that it’s for Steven’s Q&A stuff, which means there’s no harm in answering it because I’m likely not defeating a teacher’s learning objective.
There are two core errors. The first error is an inappropriate assignment target on line #6 and the second is failing to assign a value to the local v_name
variable. If you’d taken the time to create the tables and try it, you should generate an error like this:
SQL> / Enter VALUE FOR ssv_name: Harry OLD 6: INTO &ssv_name, v_grade NEW 6: INTO Harry, v_grade INTO Harry, v_grade * ERROR at line 6: ORA-06550: line 6, COLUMN 12: PLS-00201: identifier 'HARRY' must be declared ORA-06550: line 7, COLUMN 4: PL/SQL: ORA-00904: : invalid identifier ORA-06550: line 5, COLUMN 4: PL/SQL: SQL Statement ignored |
Also, at this point if you couldn’t see the error by quick inspection, it should be transparent to you. However, I don’t believe in playing games. Here’s the answer you need:
- The SELECT-INTO statement is an left-to-right assignment operator in SQL (available in all dialects), and the right operand (variable) or list of operands (variables) must be identifier(s). “Identifiers are words. They can be reserved words, predefined identifiers, quoted identifiers, user-defined variables, subroutines, or user-defined types. (Oracle Database 11g PL/SQL Programming on page #51).” In this case, as the right operand(s), they are user-defined variables.
- The & (ampersand) preceding
ssv_name
makes that a substitution placeholder or target, which is a SQL*Plus prompt for a value. The value provided at run-time is assigned to the SQL*Plus placeholder as a string literal during the preparing phase. That phase precedes the anonymous block parse, fetch, and execute cycle. Therefore, you raise a parsing error while running the anonymous block unless the&ssv_name
input value is a valid locally declared variable or defined session bind variable name. - Assuming you input a valid identifier, the next problem is that the
WHERE
clause uses an equality comparison operator against thev_name
local variable, which holds a null value. That means theSELECT-INTO
always fails on a no data found error unless you add an assignment statement for thev_name
variable.
Anyway, I hope spelling it out in a formal blog post was helpful to someone in the future. If so, leave a comment and let me know.