7 Replies Latest reply on Feb 5, 2013 9:31 AM by 989130

    Labelling in plsql

    989130
      Hi i am new to plsql

      i am trying to execute a simple program using labels,but its throwing error

      program :

      <<label1>>
      declare
      x varchar2(10) := 'hello';
      begin
      declare
      x varchar2(10) := 'there';
      begin
      dbms_output.put_line(label1.x || x);
      end;
      end label1;
      /

      Error:


      <<label1>>
      declare
      x varchar2(10) := 'hello'

      ORA-06550: line 5, column 0:
      PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

      * & = - + ; < / > at in is mod remainder not rem
      <an exponent (**)> <> or != or ~= >= <= <> and or like like2
      like4 likec between || multiset member submultiset

      8 /

      begin
      declare
      x varchar2(10) := 'there';
      begin
      dbms_output.put_line(label1.x || x);
      end;
      end label1;

      ORA-06550: line 5, column 33:
      PLS-00201: identifier 'LABEL1.X' must be declared
      ORA-06550: line 5, column 12:
      PL/SQL: Statement ignored


      it is working fine.if i wont use label in this program.
      someone please help me out to resolve this problem..
        • 1. Re: Labelling in plsql
          971895
          Why are you going with label ,it simple use plsql.
          • 2. Re: Labelling in plsql
            971895
            i tried your working fine...
            <<label1>>
            declare
            x varchar2(10) := 'hello';
            begin
            declare
            x varchar2(10) := 'there';
            begin
            dbms_output.put_line(label1.x || x);
            end;
            end label1;
            /
            • 3. Re: Labelling in plsql
              Billy~Verreynne
              Works fine for me on 11.2.0.1 using sqlplus from Oracle InstantClient 11g.
              SQL> <<label1>>
                2  declare
                3          i       integer := 123;
                4  begin
                5          declare
                6                  i       integer := 999;
                7          begin
                8                  dbms_output.put_line( 'i='||i );
                9                  dbms_output.put_line( 'i='||label1.i );
               10          end;
               11  end;
               12  /
              i=999
              i=123
              
              PL/SQL procedure successfully completed.
              
              SQL> 
              1 person found this helpful
              • 4. Re: Labelling in plsql
                989130
                jus wanna know labelling concepts, so tried with basic example
                • 5. Re: Labelling in plsql
                  Billy~Verreynne
                  986127 wrote:
                  jus wanna know labelling concepts, so tried with basic example
                  I've never used it in many years of PL/SQL programming.

                  Labelling, I think, comes from writing spaghetti code - nested blocks, using goto statements, etc.

                  What labelling does show though, is the importance of explicit scope. When you have object name collisions (same name used by different code blocks/objects), you need to rather explicitly specify the scope, than leave it for the compiler to figure it out (and potentially incorrectly).

                  However, actual labels are not needed. Simplistic example. Table object uses empno as column name and stored proc uses empno as variable name:
                  SQL> create or replace procedure GetEmployee( c out sys_refcursor, empno emp.empno%Type ) is
                    2  begin
                    3          --// uses explicit scope to distinguish between empno as column and
                    4          --// empno as parameter
                    5          open c for
                    6          select
                    7                  e.*
                    8          from    emp e
                    9          where   e.empno = GetEmployee.empno;
                   10  end;
                   11  /
                  
                  Procedure created.
                  
                  SQL> 
                  SQL> var c refcursor
                  SQL> exec GetEmployee( :c, 7902 );
                  
                  PL/SQL procedure successfully completed.
                  
                  SQL> print c
                  
                       EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
                  ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
                        7902 FORD       ANALYST         7566 1981/12/03 00:00:00       3000                    20
                  
                  SQL> 
                  There is a somewhat misguided standard used in PL/SQL, based on (outdated and flawed) Hungarian notation, that states that parameters need to be prefixed using "+p_+" and variables using "+v_+".

                  But is a superfluous standard. The PL/SQL language supports explicit scope resolution. There's no reason to reinvent that, and reinvent it poorly and inconsistently, using a Hungarian notation style standard.
                  • 6. Re: Labelling in plsql
                    Billy~Verreynne
                    A more comprehensive example using a package - where there are 3 name reference collisions:
                    SQL> create or replace package FooLib as
                      2          empno emp.empno%Type default null;
                      3          procedure GetEmployee( c out sys_refcursor, empno emp.empno%Type default null );
                      4  end;
                      5  /
                    
                    Package created.
                    
                    SQL> 
                    SQL> create or replace package body FooLib as
                      2  
                      3  procedure GetEmployee( c out sys_refcursor, empno emp.empno%Type default null ) is
                      4  begin
                      5          open c for
                      6          select
                      7                  e.*
                      8          from    emp e
                      9          where   e.empno = nvl( GetEmployee.empno, FooLib.empno );
                     10  end;
                     11  
                     12  
                     13  end;
                     14  /
                    
                    Package body created.
                    
                    SQL> 
                    SQL> var c refcursor
                    SQL> exec FooLib.GetEmployee( :c, 7902 );
                    
                    PL/SQL procedure successfully completed.
                    
                    SQL> print c
                    
                         EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
                    ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
                          7902 FORD       ANALYST         7566 1981/12/03 00:00:00       3000                    20
                    
                    SQL> 
                    SQL> exec FooLib.empno := 7782; FooLib.GetEmployee( :c );
                    
                    PL/SQL procedure successfully completed.
                    
                    SQL> print c
                    
                         EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
                    ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
                          7782 CLARK      MANAGER         7839 1981/06/09 00:00:00       2450                    10
                    
                    SQL> 
                    • 7. Re: Labelling in plsql
                      989130
                      Thanks for your information Billy Verreynne.. my problem got solved,its because of the way i run..