MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

NDS parameters as IN OUT?

without comments

A question posed by a student: “Why are Oracle Native Dynamic SQL (NDS) USING clause parameters IN, IN OUT, or OUT when the RETURNING INTO clause manages output values?” It a great question, isn’t it? The followup question was also great, “How do you implement an example of NDS IN OUT parameters?”

The answer is two fold. First, you should use the USING clause for parameter list input values and the RETURNING INTO clause for return values whenever possible. Second, when it’s not possible you’re generally passing parameters into and out of an NDS PL/SQL anonymous block.

The basic prototype for passing and retrieving values from an NDS statement is:

EXECUTE IMMEDIATE sql_stmt
  USING { IN | IN OUT | OUT } local_variable [, ...]
  RETURNING INTO { IN OUT | OUT } local_variable [, ...];

A quick and hopefully fun example is this parody on Marvel’s The Avengers. The program creates an anonymous block with a super hero of Thor and super villain of Loki, then it uses a USING clause with IN OUT parameters to an anonymous block statement. That’s basically the trick to how you use IN OUT parameters in NDS statements.

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

-- Enable SERVEROUTPUT.
SET SERVEROUTPUT ON SIZE UNLIMITED
 
-- Declare an anonymous testing block.
DECLARE
 
  -- Declare two local variables.
  lv_super_hero     VARCHAR2(20) := 'Thor';
  lv_super_villain  VARCHAR2(20) := 'Loki';
 
  -- Declare a null statement variable.
  lv_stmt  VARCHAR2(32767);
 
  -- Declare a local procedure to parse the NDS block.
  PROCEDURE print_code_block (pv_block VARCHAR2) IS
    -- Declare local parsing variables.
    lv_length   INTEGER := 1;
    lv_start    INTEGER := 1;
    lv_end      INTEGER := 1;
  BEGIN
    -- Read line by line on a line return character.
    WHILE NOT (lv_end = 0) LOOP
      -- Check for line returns.
      lv_end := INSTR(lv_stmt,CHR(10),lv_start);
      -- Check whether line return has been read.
      IF NOT lv_end = 0 THEN     
        -- Reset the ending substring value and print substring.
        lv_end := INSTR(lv_stmt,CHR(10),lv_start);
        dbms_output.put_line('| '||SUBSTR(lv_stmt,lv_start,lv_end - lv_start));
      ELSE
        -- Print the last substring with a semicolon and exit the loop.      
        dbms_output.put_line('| '||SUBSTR(lv_stmt,lv_start,LENGTH(lv_stmt) - lv_start)||';');
      END IF;
      -- Reset the beginning of the string.
      lv_start := lv_end + 1;      
    END LOOP;    
  END print_code_block;
 
BEGIN
 
  -- Demonstrate good triumps over evil.
  dbms_output.put_line('The good '||lv_super_hero||' beats up the bad '||lv_super_villain||'!');
 
  -- Assign the anonymous block to the local statement variable.
  lv_stmt := 'DECLARE'||CHR(10)
          || '  lv_super_hero     VARCHAR2(20);'||CHR(10)
          || '  lv_super_villain  VARCHAR2(20);'||CHR(10)
          || 'BEGIN'||CHR(10)
          || '  lv_super_hero '||CHR(58)||'= :pv_super_hero;'||CHR(10)
          || '  lv_super_villain '||CHR(58)||'= :pv_super_villain;'||CHR(10)
          || '  :pv_super_hero '||CHR(58)||'= lv_super_villain;'||CHR(10)
          || '  :pv_super_villain '||CHR(58)||'= lv_super_hero;'||CHR(10)
          || 'END;';
 
  -- Run the NDS program.
  EXECUTE IMMEDIATE lv_stmt USING IN OUT lv_super_hero
                                , IN OUT lv_super_villain;
 
  -- Print the diagnostic code block, that's why it used line returns afterall.  
  dbms_output.put_line('--------------------------------------------------');
  print_code_block(lv_stmt);
  dbms_output.put_line('--------------------------------------------------');
 
  -- Demonstrate the world is upside down without Johnny Depp playing Capt'n Jack.
  dbms_output.put_line('The good '||lv_super_hero||' beats up the bad '||lv_super_villain||'!');
 
END;
/

You’ll get the following printed output:

The good Thor beats up the bad Loki!
--------------------------------------------------
| DECLARE
|   lv_super_hero     VARCHAR2(20);
|   lv_super_villain  VARCHAR2(20);
| BEGIN
|   lv_super_hero := :pv_super_hero;
|   lv_super_villain := :pv_super_villain;
|   :pv_super_hero := lv_super_villain;
|   :pv_super_villain := lv_super_hero;
| END;
--------------------------------------------------
The good Loki beats up the bad Thor!

As always, I hope it helps you understand the concept of the USING clause with IN OUT parameters but I hope there’s always better way.

Written by maclochlainn

June 13th, 2012 at 11:52 pm

Posted in Oracle,Oracle 11g,pl/sql

«
»