What is << some text >> in oracle

Question

I found this character while reading some blog of pl sql << some text >> .

I found this character from following blog http://www.oracle-base.com/articles/8i/collections-8i.php

Answer 1

As others have said, <<some_text>> is a label named "some_text". Labels aren't often used in PL/SQL but can be helpful in a variety of contexts.

As an example, let's say you have several nested loops, execution has reached the very inner-most level, and the code needs to exit from all the nested loops and continue after the outer-most one. Here a label can be used in the following fashion:

<<outer_most_loop>>
LOOP
  ...
  <<next_inner_loop>>
  LOOP
    ...
    <<inner_most_loop>>
    LOOP
      ...
      IF something <> something_else THEN
        EXIT outer_most_loop;
      END IF;
      ...
    END LOOP inner_most_loop;
    ...
  END LOOP next_inner_loop;
  ...
END LOOP outer_most_loop;

-- Execution continues here after EXIT outer_most_loop;

something := something_else;
...

Next, let's say that you've got some code with nested blocks, each of which declares a variable of the same name, so that you need to instruct the compiler about which of the same-named variables you intend to use. In this case you could use a label like this:

<<outer>>
DECLARE
  nNumber  NUMBER := 1;
BEGIN
  <<inner>>
  DECLARE
    nNumber  NUMBER := 2;
  BEGIN
    DBMS_OUTPUT.PUT_LINE('outer.nNumber=' || outer.nNumber);
    DBMS_OUTPUT.PUT_LINE('inner.nNumber=' || inner.nNumber);
  END inner;
END outer;

Labels can also be useful if you insist on giving a variable the same name as a column in a table. As an example, let's say that you have a table named PEOPLE with a non-nullable column named LASTNAME and you want to delete everyone with LASTNAME = 'JARVIS'. The following code:

DECLARE
  lastname VARCHAR2(100) := 'JARVIS';
BEGIN
  DELETE FROM PEOPLE
    WHERE LASTNAME = lastname;
END;

will not do what you intended - instead, it will delete every row in the PEOPLE table. This occurs because in the case of potentially ambiguous names, PL/SQL will choose to use the column in the table instead of the local variable or parameter; thus, the above is interpreted as

DECLARE
  lastname VARCHAR2(100) := 'JARVIS';
BEGIN
  DELETE FROM PEOPLE p
    WHERE p.LASTNAME = p.lastname;
END;

and boom! Every row in the table goes bye-bye. :-) A label can be used to qualify the variable name as follows:

<<outer>>
DECLARE
  lastname VARCHAR2(100) := 'JARVIS';
BEGIN
  DELETE FROM PEOPLE p
    WHERE p.LASTNAME = outer.lastname;
END;

Execute this and only those people with LASTNAME = 'JARVIS' will vanish.

And yes - as someone else said, you can GOTO a label:

FUNCTION SOME_FUNC RETURN NUMBER
IS
  SOMETHING       NUMBER := 1;
  SOMETHING_ELSE  NUMBER := 42;
BEGIN
  IF SOMETHING <> SOMETHING_ELSE THEN
    GOTO HECK;
  END IF;

  RETURN 0;

  <<HECK>>

  RETURN -1;
END;

(Ewwwww! Code like that just feels so wrong..!)

Share and enjoy.

Answer 2

It's often used to label loops, cursors, etc.

You can use that label in goto statements. Else, it is just 'comment'.

Sample from Oracle:

DECLARE
  p        VARCHAR2(30);
  n        PLS_INTEGER := 37; -- test any integer > 2 for prime
BEGIN
  FOR j in 2..ROUND(SQRT(n)) LOOP
    IF n MOD j = 0 THEN -- test for prime
      p := ' is not a prime number'; -- not a prime number
      GOTO print_now; -- << here is the GOTO
    END IF;
  END LOOP;
  p := ' is a prime number';
<<print_now>> -- << and it executes this
  DBMS_OUTPUT.PUT_LINE(TO_CHAR(n) || p);
END;
/