Subscribe

Share

Database, SQL and PL/SQL

Test Your Oracle Database 12c PL/SQL Knowledge

Do you know about the latest database features and how to use them?

By Steven Feuerstein Oracle ACE Director

September/October 2015

Oracle Database 12c brings a variety of useful, powerful enhancements to the PL/SQL language. Your DBAs may not have upgraded to Oracle Database 12.1, but it’s bound to happen soon. And when it does, wouldn’t you like to be ready to take advantage of the new features? Use Oracle Magazine quizzes (powered by the PL/SQL Challenge platform at plsqlchallenge.oracle.com) to “learn through play” and actively test and deepen your PL/SQL knowledge.

I create two packages:

CREATE OR REPLACE PACKAGE private_pkg
IS
   PROCEDURE do_this;
   PROCEDURE do_that;
END;
/
CREATE OR REPLACE PACKAGE BODY private_pkg
IS
   PROCEDURE do_this
   IS
   BEGIN
      DBMS_OUTPUT.put_line ('THIS');
   END;
   PROCEDURE do_that
   IS
   BEGIN
      DBMS_OUTPUT.put_line ('THAT');
   END;
END;
/
CREATE OR REPLACE PACKAGE public_pkg
IS
   PROCEDURE do_only_this;
END;
/
CREATE OR REPLACE PACKAGE BODY public_pkg
IS
   PROCEDURE do_only_this
   IS
   BEGIN
      private_pkg.do_this;
      private_pkg.do_that;
   END;
END;
/

Which of the choices, when run before the following blocks, will result in “Done” being displayed once and only once (possibly along with some other text) after the blocks are executed?

BEGIN
   public_pkg.do_only_this;
   DBMS_OUTPUT.put_line ('Done');
END;
/
BEGIN
   private_pkg.do_this;
   DBMS_OUTPUT.put_line ('Done');
END;
/

a.

CREATE OR REPLACE PACKAGE BODY private_pkg
IS
   PROCEDURE do_this
   IS
      l_unit   VARCHAR2 (30) := $$PLSQL_UNIT;
   BEGIN
      IF l_unit <> 'PUBLIC_PKG'
      THEN
         RAISE PROGRAM_ERROR;
      END IF;
      DBMS_OUTPUT.put_line ('THIS');
   END;
   PROCEDURE do_that
   IS
      l_unit   VARCHAR2 (30) := $$PLSQL_UNIT;
   BEGIN
      IF l_unit <> 'PUBLIC_PKG'
      THEN
         RAISE PROGRAM_ERROR;
      END IF;
      DBMS_OUTPUT.put_line ('THAT');
   END;
END;
/

b.

CREATE OR REPLACE PACKAGE private_pkg
   ACCESSIBLE BY (public_pkg)
IS
   PROCEDURE do_this;
   PROCEDURE do_that;
END;
/

c.

CREATE OR REPLACE PACKAGE BODY private_pkg
IS
   PROCEDURE do_this
   IS
   BEGIN
      DBMS_OUTPUT.put_line ('THIS');
   END;
   PROCEDURE do_that
   IS
   BEGIN
      DBMS_OUTPUT.put_line ('THAT');
   END;
BEGIN
   DECLARE
      l_stack   VARCHAR2 (32767)
                   := DBMS_UTILITY.format_call_stack;
   BEGIN
      IF INSTR (l_stack, 'PUBLIC_PKG') = 0
      THEN
         RAISE PROGRAM_ERROR;
      END IF;
   END;
END;
/

d.

CREATE OR REPLACE PACKAGE BODY private_pkg
IS
   PROCEDURE check_stack
   IS
      l_stack   VARCHAR2 (32767)
                   := DBMS_UTILITY.format_call_stack;
   BEGIN
      IF INSTR (l_stack, 'PUBLIC_PKG') = 0
      THEN
         RAISE PROGRAM_ERROR;
      END IF;
   END;
   PROCEDURE do_this
   IS
   BEGIN
      check_stack;
      DBMS_OUTPUT.put_line ('THIS');
   END;
   PROCEDURE do_that
   IS
   BEGIN
      check_stack;
      DBMS_OUTPUT.put_line ('THAT');
   END;
END;
/

e.

CREATE OR REPLACE PACKAGE private_pkg
   ACCESSIBLE BY (public_pkg.do_only_this)
IS
   PROCEDURE do_this;
   PROCEDURE do_that;
END;
/



 

Quiz Summary

Use the ACCESSIBLE BY clause to restrict which program units can execute a subprogram, even when all code is defined within the same schema.


Read a discussion of the question and answers—and look for more challenges—at the PL/SQL Challenge.

I want to maximize the availability of the PL/SQL Challenge website, so I sent a proposal for an n-node Oracle Real Application Clusters (Oracle RAC) configuration to my boss.

I created a sequence and a table to store the infrastructure details that would be required for the Oracle RAC setup. The table contains the configuration details of each node in the cluster. Here are simplified versions of the sequence and the table:

CREATE SEQUENCE plch_seq 
  START WITH 1 
  INCREMENT BY 1 
  NOCACHE 
  NOCYCLE
/
CREATE TABLE plch_nodes 
  ( 
     node_name       VARCHAR2(30), 
     node_pub_ip     VARCHAR2(15) UNIQUE, 
     CONSTRAINT plch_nodes_pk PRIMARY KEY(node_name) 
  )
/

Which of the following choices will successfully insert two rows into the PLCH_NODES table?

a.

CREATE OR REPLACE TRIGGER plch_node_name
   BEFORE INSERT
   ON plch_nodes
   FOR EACH ROW
DECLARE
   v_node_name   plch_nodes.node_name%TYPE := 'PLCH_RAC';
BEGIN
   :new.node_name := v_node_name || plch_seq.NEXTVAL;
END plch_node_name;
/
BEGIN 
    INSERT INTO plch_nodes (node_pub_ip) 
    VALUES     ('192.168.2.101'); 
    INSERT INTO plch_nodes (node_pub_ip) 
    VALUES     ('192.168.2.102'); 
    COMMIT; 
END; 
/

b.

DROP SEQUENCE plch_seq 
/ 
DECLARE
   v_node_name   plch_nodes.node_name%TYPE := 'PLCH_RAC';
   v_plch_seq    INTEGER := 0;
BEGIN
   SELECT NVL (
             MAX (SUBSTR (node_name, LENGTH (v_node_name) + 1)),
             1)
     INTO v_plch_seq
     FROM plch_nodes;
   INSERT INTO plch_nodes
        VALUES (v_node_name || v_plch_seq, '192.168.2.101');
   v_plch_seq := v_plch_seq + 1;
   INSERT INTO plch_nodes
        VALUES (v_node_name || v_plch_seq, '192.168.2.102');
   COMMIT;
END;
/

c.

DECLARE 
    v_node_name plch_nodes.node_name%TYPE; 
BEGIN 
    v_node_name := 'PLCH_RAC' || plch_seq.NEXTVAL; 
    INSERT INTO plch_nodes 
    VALUES     (v_node_name, '192.168.2.101'); 
    v_node_name := 'PLCH_RAC' || plch_seq.NEXTVAL; 
    INSERT INTO plch_nodes 
    VALUES     (v_node_name, '192.168.2.102'); 
    COMMIT; 
END; 
/

d.

DROP TABLE plch_nodes 
/ 
CREATE TABLE plch_nodes 
  ( 
     node_name       VARCHAR2(30) 
        DEFAULT 'PLCH_RAC'|| plch_seq.NEXTVAL, 
     node_pub_ip     VARCHAR2(15) UNIQUE, 
     CONSTRAINT plch_nodes_pk PRIMARY KEY(node_name) 
  ) 
/ 
BEGIN 
    INSERT INTO plch_nodes 
                (node_pub_ip) 
    VALUES      ('192.168.2.101'); 
    INSERT INTO plch_nodes 
                (node_pub_ip) 
    VALUES      ('192.168.2.102'); 
    COMMIT; 
END; 
/

e.

BEGIN
   INSERT INTO plch_nodes (node_name, node_pub_ip)
        VALUES ('PLCH_RAC' || plch_seq.NEXTVAL
              , '192.168.2.101');
   INSERT INTO plch_nodes (node_name, node_pub_ip)
        VALUES ('PLCH_RAC' || plch_seq.NEXTVAL
              , '192.168.2.102');
   COMMIT;
END;
/


Quiz Summary

Oracle Database offers multiple ways to generate unique values for table columns. One of the most commonly used methods for doing this is to use a sequence, and in Oracle Database 12c, you can now call the NEXTVAL function to get that next unique value right within the default value for the column.

Read a discussion of the question and answers—and look for more challenges—at the PL/SQL Challenge.

 

I create this package:

CREATE OR REPLACE PACKAGE plch_rec_pkg
AS
   TYPE rec_t IS RECORD
   (
      n1   NUMBER,
      n2   NUMBER
   );
   PROCEDURE set_rec (n1_in     IN     NUMBER,
                      n2_in     IN     NUMBER,
                      rec_out      OUT rec_t);
   PROCEDURE set_values (n1_in    IN     NUMBER,
                         n2_in    IN     NUMBER,
                         n1_out      OUT NUMBER,
                         n2_out      OUT NUMBER);
END plch_rec_pkg;
/
CREATE OR REPLACE PACKAGE BODY plch_rec_pkg
AS
   PROCEDURE set_rec (n1_in     IN     NUMBER,
                      n2_in     IN     NUMBER,
                      rec_out      OUT rec_t)
   AS
   BEGIN
      rec_out.n1 := n1_in - n2_in;
      rec_out.n2 := n2_in - n1_in;
   END set_rec;
   PROCEDURE set_values (n1_in    IN     NUMBER,
                         n2_in    IN     NUMBER,
                         n1_out      OUT NUMBER,
                         n2_out      OUT NUMBER)
   IS
      l_record   rec_t;
   BEGIN
      set_rec (n1_in, n2_in, l_record);
      n1_out := l_record.n1;
      n2_out := l_record.n2;
   END;
END plch_rec_pkg;
/

Which of the choices displays the following text after execution?

N1 = -10
N2 = 10

a.

DECLARE
   l_record   plch_rec_pkg.rec_t;
BEGIN
   EXECUTE IMMEDIATE
      'BEGIN plch_rec_pkg.set_rec (10, 20, :rec); END;'
      USING OUT l_record;
   DBMS_OUTPUT.put_line ('N1 = ' || l_record.n1);
   DBMS_OUTPUT.put_line ('N2 = ' || l_record.n2);
END;
/

b.

DECLARE
   l_record   plch_rec_pkg.rec_t;
BEGIN
   plch_rec_pkg.set_rec (10, 20, l_record);
   DBMS_OUTPUT.put_line ('N1 = ' || l_record.n1);
   DBMS_OUTPUT.put_line ('N2 = ' || l_record.n2);
END;
/

c.

DECLARE
   l_record   plch_rec_pkg.rec_t;
BEGIN
   EXECUTE IMMEDIATE
      'BEGIN plch_rec_pkg.set_values (10, 20, :n1, :n2); END;'
      USING OUT l_record.n1, out l_record.n2;
   DBMS_OUTPUT.put_line ('N1 = ' || l_record.n1);
   DBMS_OUTPUT.put_line ('N2 = ' || l_record.n2);
END;
/


Quiz Summary

As of Oracle Database 12c, you can now bind user-defined types (such as record types and collection types) defined in PL/SQL packages into dynamic SQL statements.

Read a discussion of the question and answers—and look for more challenges—at the PL/SQL Challenge.


Next Steps

 TEST your PL/SQL knowledge

READ more Feuerstein
 bit.ly/omagplsql
 stevenfeuersteinonplsql.blogspot.com

 READ more about PL/SQL

 WATCH the video Steven Says Enjoy

 

Photography by Scott Webb, Unsplash