Test Your Oracle Database 12c PL/SQL Knowledge
Do you know about the latest database features and how to use them?
By Steven Feuerstein
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
READ more Feuerstein
|
DISCLAIMER: We've captured these popular historical magazine articles for your reference. Links etc contained within these article possibly won't work. These articles are provided as-is with no guarantee that the information within them is the best advice for current versions of the Oracle Database.