MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘SQL*Plus’ Category

Substitutable Columns

with 2 comments

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.

  1. 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 FINAL default by making the base type NOT FINAL. The example use BASE_T as the generalized type or data structure of a substitutable column:

    CREATE OR REPLACE TYPE base_t AS OBJECT
( base_id  NUMBER ) NOT FINAL;
/
  2. After you create your base data structure, you create a specialized subtype. The following example creates a PERSON_T type and accepts the default of FINAL, 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));
/
  3. With a generalized BASE_T type and a specialized PERSON_T subtype, you create a CUSTOMER table with a substitutable CUSTOMER_NAME column. The CUSTOMER_NAME column uses the generalized BASE_T data type. You should also create a CUSTOMER_S sequence 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;
  4. You can now populate the table with instances of the BASE_T type or the PERSON_T subtype. The following inserts three rows into the CUSTOMER table. 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'));
  5. 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

    6. 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.

  6. This error message may lead you to call the CUSTOMER_NAME column in a subquery and use the TABLE function 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. The TABLE function 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
  7. The non-nested table error message should lead you to wrap the call to the TREAT function in a call to the COLLECT function, 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
  8. 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
  9. 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 JOIN to 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
  10. 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"(+))

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.

Written by maclochlainn

December 8th, 2017 at 11:17 pm

Posted in Oracle,Oracle 11g,Oracle 12c,sql,SQL*Plus

Tagged with ,

Finding Direct Indexes

without comments

If you’re not using Toad DBA Suite, it’s sometimes hard to find solutions. Somebody wanted to know how to find indexes that aren’t indirect. Indirect indexes are those created for a primary key because a primary key column or set of columns are both not null and uniquely constrained. Likewise, you create a unique index when you can create a unique constraint. You can’t drop a unique index for a primary key without dropping the primary key or unique constraint that indirectly created it.

The following query returns indexes with one or more columns that are created by a CREATE INDEX statement on a target table. It excludes unique indexes created by a primary key constraint, and it returns the relative position of columns in an index:

COLUMN sequence_name   FORMAT A22 HEADING "Sequence Name"
COLUMN column_position FORMAT 999 HEADING "Column|Position"
COLUMN column_name     FORMAT A22 HEADING "Column|Name"
SELECT   uin.index_name
,        uic.column_position
,        uic.column_name
FROM     user_indexes uin INNER JOIN user_ind_columns uic
ON       uin.index_name = uic.index_name
AND      uin.table_name = uic.table_name
WHERE    uin.table_name = UPPER('&&table_name')
AND NOT  uin.index_name IN (SELECT constraint_name
                            FROM   user_constraints
                            WHERE  table_name = UPPER('&&table_name'))
ORDER BY uin.index_name
,        uic.column_position;

It can be rewritten into a function, which can then drop indexes based on a table name:

CREATE OR REPLACE FUNCTION drop_indexes_on
( pv_table_name  VARCHAR2 ) RETURN NUMBER IS 
 
  /* A return value. */
  lv_return  NUMBER := 0;
 
  /* A query to return only directly created indexes. */
  CURSOR find_indexes_on
  ( cv_table_name  VARCHAR2 ) IS
    SELECT   DISTINCT ui.index_name
    FROM     user_indexes ui INNER JOIN user_ind_columns uic
    ON       ui.index_name = uic.index_name
    AND      ui.table_name = uic.table_name
    WHERE    ui.table_name = UPPER(cv_table_name)
    AND NOT  ui.index_name IN (SELECT constraint_name
                               FROM   user_constraints
                               WHERE  table_name = UPPER(cv_table_name));
 
  /* Declare function autonomous. */
  PRAGMA AUTONOMOUS_TRANSACTION;
 
BEGIN
 
  /* Drop the indexes on a table. */
  FOR i IN find_indexes_on(pv_table_name) LOOP
    EXECUTE IMMEDIATE 'DROP INDEX '||i.index_name;
    lv_return := 1;
  END LOOP;
 
  RETURN lv_return;
END drop_indexes_on;
/

You can call the drop_on_indexes_on function like this:

SELECT   drop_indexes_on(UPPER('address_lab'))
FROM     dual;

Hope this helps those who need to work with dropping indexes.

Written by maclochlainn

November 23rd, 2014 at 8:42 pm

Posted in Oracle,Oracle 11g,Oracle 12c,pl/sql,sql,SQL*Plus

SQL Insert from Query

with 5 comments

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.

Written by maclochlainn

June 14th, 2014 at 10:40 pm

Posted in Oracle,Oracle 11g,Oracle 12c,sql,SQL*Plus

Tagged with ,

Provision Oracle 12c PDB

with 8 comments

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:

  1. 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
  1. You need to stop the Oracle listener and modify the listener.ora file. Lines 9 through 12 configure a VIDEODB 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))
    )
  )
  1. You also need to add a VIDEO TNS alias to the tnsnames.ora file for the VIDEODB 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)
    )
  )
  1. You connect as the SYSDBA for the VIDEO pluggable database with the following syntax (not presently an example in the SQL*Plus Guide). After connecting as the SYSDBA for the VIDEODB, you’ll be prompted for a password. The required password is the Video1 password that you used when you set up the VIDEODB database.
sqlplus sys@VIDEO AS sysdba
  1. After authenticating as the SYSDBA, you need to start the VIDEODB 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;
  1. As Oracle said during the Oracle 11gR2 release, the DBA role no longer grants UNLIMITED TABLESPACE. That means you need to grant it as the SYSDBA for the PDB, like:
GRANT UNLIMITED TABLESPACE TO videoadm;
  1. 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"
  1. 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.

Written by maclochlainn

September 13th, 2013 at 1:51 am

Posted in Oracle,Oracle 12c,sql,SQL*Plus

Tagged with , , ,

SELECT-INTO variables

without comments

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 the v_name local variable, which holds a null value. That means the SELECT-INTO always fails on a no data found error unless you add an assignment statement for the v_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.

Written by maclochlainn

November 29th, 2012 at 7:09 pm

Posted in Oracle,Oracle 11g,pl/sql,SQL*Plus

Newer Entries »