Home » SQL & PL/SQL » SQL & PL/SQL » Variable is NULL (ORACLE 11G)
Variable is NULL [message #585002] Tue, 21 May 2013 04:43 Go to next message
Johnqiu
Messages: 9
Registered: May 2013
Junior Member
Hi, I can't figure out why my variables are not filled up?
Any help is welcome. Thanks

TEST CASE

CREATE TABLE LIST_STEP_LINK
(
FAL_SCHEDULE_STEP_ID NUMBER(12) ,
FAL_SCHEDULE_PLAN_ID NUMBER(12) ,
SCS_STEP_NUMBER NUMBER(9) ,
SCS_LONG_DESCR VARCHAR2(4000 BYTE),
SCS_FREE_DESCR VARCHAR2(4000 BYTE),
FAL_TASK_ID NUMBER(12)
)

Insert into LIST_STEP_LINK
(FAL_SCHEDULE_STEP_ID,
FAL_SCHEDULE_PLAN_ID, SCS_STEP_NUMBER, SCS_LONG_DESCR, SCS_FREE_DESCR, FAL_TASK_ID)
Values
(1906484, 1906480, 20, 'Gicler
GIC 127', NULL,
1385435);
Insert into LIST_STEP_LINK
(FAL_SCHEDULE_STEP_ID,
FAL_SCHEDULE_PLAN_ID, SCS_STEP_NUMBER, SCS_LONG_DESCR, SCS_FREE_DESCR, FAL_TASK_ID)
Values
(1906485, 1906480, 30, 'GAL 35', NULL,
1379370);
Insert into LIST_STEP_LINK
(FAL_SCHEDULE_STEP_ID,
FAL_SCHEDULE_PLAN_ID, SCS_STEP_NUMBER, SCS_LONG_DESCR, SCS_FREE_DESCR, FAL_TASK_ID)
Values
(1906487, 1906480, 40, 'ZAP 007', NULL,
1379371);
Insert into LIST_STEP_LINK
(FAL_SCHEDULE_STEP_ID,
FAL_SCHEDULE_PLAN_ID, SCS_STEP_NUMBER, SCS_LONG_DESCR, SCS_FREE_DESCR, FAL_TASK_ID)
Values
(1906488, 1906480, 50, NULL, NULL,
1379372);
Insert into LIST_STEP_LINK
(FAL_SCHEDULE_STEP_ID,
FAL_SCHEDULE_PLAN_ID, SCS_STEP_NUMBER, SCS_LONG_DESCR, SCS_FREE_DESCR, FAL_TASK_ID)
Values
(1906489, 1906480, 60, 'DEC 1016', NULL,
1379373);
Insert into LIST_STEP_LINK
(FAL_SCHEDULE_STEP_ID,
FAL_SCHEDULE_PLAN_ID, SCS_STEP_NUMBER, SCS_LONG_DESCR, SCS_FREE_DESCR, FAL_TASK_ID)
Values
(1906490, 1906480, 80, NULL, NULL,
1906472);
Insert into LIST_STEP_LINK
(FAL_SCHEDULE_STEP_ID,
FAL_SCHEDULE_PLAN_ID, SCS_STEP_NUMBER, SCS_LONG_DESCR, SCS_FREE_DESCR, FAL_TASK_ID)
Values
(1906482, 1906480, 10, 'LAV 002', NULL,
1632562);
Insert into LIST_STEP_LINK
(FAL_SCHEDULE_STEP_ID,
FAL_SCHEDULE_PLAN_ID, SCS_STEP_NUMBER, SCS_LONG_DESCR, SCS_FREE_DESCR, FAL_TASK_ID)
Values
(1906492, 1906480, 70, NULL, NULL,
1392737);
Insert into LIST_STEP_LINK
(FAL_SCHEDULE_STEP_ID,
FAL_SCHEDULE_PLAN_ID, SCS_STEP_NUMBER, SCS_LONG_DESCR, SCS_FREE_DESCR, FAL_TASK_ID)
Values
(8601735, 1906480, 90, NULL, NULL,
2354112);
COMMIT;

SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
   VAR_SCS_LONG_DESCR   FAL_LIST_STEP_LINK.SCS_LONG_DESCR%type;
   VAR_SCS_FREE_DESCR   FAL_LIST_STEP_LINK.SCS_FREE_DESCR%type;
   Var_cpt              NUMBER (12) := 1;
   CURSOR c1
   IS
          SELECT SCS_LONG_DESCR            
           INTO VAR_SCS_LONG_DESCR
            FROM FAL_LIST_STEP_LINK
           WHERE    FAL_SCHEDULE_STEP_ID = 1906482 
      FOR UPDATE OF SCS_FREE_DESCR;
BEGIN
   FOR r1 IN c1
   LOOP
      var_cpt := var_cpt + 1;

    UPDATE FAL_LIST_STEP_LINK
         SET SCS_FREE_DESCR =  VAR_SCS_LONG_DESCR
       WHERE CURRENT OF c1;
    IF VAR_SCS_LONG_DESCR is null
    then 
      DBMS_OUTPUT.put_line ('VAR_SCS_LONG_DESCR is empty');
    else  
      DBMS_OUTPUT.put_line (VAR_SCS_LONG_DESCR ||'  CHR (13) ' || VAR_SCS_FREE_DESCR);
    end if;
   END LOOP;
END;
/


Result:

SQL>
SQL> SET SERVEROUTPUT ON SIZE 1000000;
SQL> DECLARE
2 VAR_SCS_LONG_DESCR FAL_LIST_STEP_LINK.SCS_LONG_DESCR%type;
3 VAR_SCS_FREE_DESCR FAL_LIST_STEP_LINK.SCS_FREE_DESCR%type;
4 Var_cpt NUMBER (12) := 1;
5 CURSOR c1
6 IS
7 SELECT SCS_LONG_DESCR
8 INTO VAR_SCS_LONG_DESCR
9 FROM FAL_LIST_STEP_LINK
10 WHERE FAL_SCHEDULE_STEP_ID = 1906482
11 FOR UPDATE OF SCS_FREE_DESCR;
12 BEGIN
13 FOR r1 IN c1
14 LOOP
15 var_cpt := var_cpt + 1;
16
17 UPDATE FAL_LIST_STEP_LINK
18 SET SCS_FREE_DESCR = VAR_SCS_LONG_DESCR
19 WHERE CURRENT OF c1;
20 IF VAR_SCS_LONG_DESCR is null
21 then
22 DBMS_OUTPUT.put_line ('VAR_SCS_LONG_DESCR is empty');
23 else
24 DBMS_OUTPUT.put_line (VAR_SCS_LONG_DESCR ||' CHR (13) ' || VAR_SCS_FREE_DESCR);
25 end if;
26 END LOOP;
27 END;
28 /
VAR_SCS_LONG_DESCR is empty

PL/SQL procedure successfully completed.

SQL>
Re: Variable is NULL [message #585003 is a reply to message #585002] Tue, 21 May 2013 04:53 Go to previous messageGo to next message
Littlefoot
Messages: 19320
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
There's no use in SELECT INTO when declaring a cursor. You are selecting its values into R1 (implicitly declared record variable whose type equals a record returned by cursor's SELECT statement.
Therefore, you'd
SQL> select scs_free_descr from list_step_link
  2  where fal_schedule_step_id = 1906482;

SCS_FREE_DESCR
------------------------------------------------------------------------


SQL> declare
  2     cursor c1
  3     is
  4            select scs_long_descr
  5              from list_step_link
  6             where fal_schedule_step_id = 1906482
  7        for update of scs_free_descr;
  8  begin
  9     for r1 in c1
 10     loop
 11        update list_step_link
 12           set scs_free_descr = r1.scs_long_descr
 13         where current of c1;
 14     end loop;
 15  end;
 16  /

PL/SQL procedure successfully completed.

SQL> select scs_free_descr from list_step_link
  2  where fal_schedule_step_id = 1906482;

SCS_FREE_DESCR
------------------------------------------------------------------------
LAV 002

SQL>
Re: Variable is NULL [message #585004 is a reply to message #585003] Tue, 21 May 2013 05:32 Go to previous messageGo to next message
Johnqiu
Messages: 9
Registered: May 2013
Junior Member
Hi Littlefoot,
I din't see that.
Thank you. Embarassed
Re: Variable is NULL [message #585009 is a reply to message #585004] Tue, 21 May 2013 06:39 Go to previous messageGo to next message
Johnqiu
Messages: 9
Registered: May 2013
Junior Member
NOw I have to add a subquery to find out the value of TAS_REF who has the same task_id as the current record of list_step_link but unsuccessful. ?
Any idea?

TEST_CASE2

CREATE TABLE TASK
(
FAL_TASK_ID NUMBER(12) NOT NULL,
TAS_REF VARCHAR2(30 BYTE) NOT NULL,
TAS_SHORT_DESCR VARCHAR2(50 BYTE)
)


Insert into TASK
(FAL_TASK_ID,
TAS_REF, TAS_SHORT_DESCR)
Values
(1379370, 'Mettre en couleur', 'Principale');
Insert into TASK
(FAL_TASK_ID,
TAS_REF, TAS_SHORT_DESCR)
Values
(1379371, 'Zaponner final', 'Principale');
Insert into TASK
(FAL_TASK_ID,
TAS_REF, TAS_SHORT_DESCR)
Values
(1379372, 'Visiter TF', 'Principale');
Insert into TASK
(FAL_TASK_ID,
TAS_REF, TAS_SHORT_DESCR)
Values
(1379373, 'Décalquer', 'Principale');
Insert into TASK
(FAL_TASK_ID,
TAS_REF, TAS_SHORT_DESCR)
Values
(1385435, 'Gicler', 'Principale');
Insert into TASK
(FAL_TASK_ID,
TAS_REF, TAS_SHORT_DESCR)
Values
(1632562, 'Laver TF', 'Principale');
COMMIT;

declare
 cursor c1
 is
         select scs_long_descr,
         (select TAS_REF from TASK FT, LIST_STEP_LINK LSL2
         where FT.FAL_TASK_ID = LSL2.FAL_TASK_ID
         AND LSL.LIST_STEP_LINK_ID =LSL2.LIST_STEP_LINK_ID
         )
            from list_step_link lsl
           where fal_schedule_step_id = 1906482
       for update of scs_free_descr;
 begin
   for r1 in c1
   loop
      update list_step_link
         set scs_free_descr = r1.TAS_REF || CHR(13) ||  r1.scs_long_descr
       where current of c1;
   end loop;
end;
/


Result:
SQL> declare
2 cursor c1
3 is
4 select scs_long_descr,
5 (select TAS_REF from TASK FT, LIST_STEP_LINK LSL2
6 where FT.FAL_TASK_ID = LSL2.FAL_TASK_ID
7 AND LSL.LIST_STEP_LINK_ID =LSL2.LIST_STEP_LINK_ID
8 )
9 from list_step_link lsl
10 where fal_schedule_step_id = 1906482
11 for update of scs_free_descr;
12 begin
13 for r1 in c1
14 loop
15 update list_step_link
16 set scs_free_descr = r1.TAS_REF || CHR(13) || r1.scs_long_descr
17 where current of c1;
18 end loop;
19 end;
20 /
AND LSL.LIST_STEP_LINK_ID =LSL2.LIST_STEP_LINK_ID
*
ERROR at line 7:
ORA-06550: line 7, column 37:
PL/SQL: ORA-00904: "LSL2"."LIST_STEP_LINK_ID": invalid identifier
ORA-06550: line 4, column 10:
PL/SQL: SQL Statement ignored
ORA-06550: line 16, column 57:
PLS-00364: loop index variable 'R1' use is invalid
ORA-06550: line 16, column 57:
PL/SQL: ORA-00904: "R1"."SCS_LONG_DESCR": invalid identifier
ORA-06550: line 15, column 7:
PL/SQL: SQL Statement ignored
Re: Variable is NULL [message #585012 is a reply to message #585009] Tue, 21 May 2013 06:56 Go to previous messageGo to next message
cookiemonster
Messages: 10846
Registered: September 2008
Location: Rainy Manchester
Senior Member
1) All code, including create table, insert statements and output from sqlplus should be in code tags.
2) the error message is clear:
PL/SQL: ORA-00904: "LSL2"."LIST_STEP_LINK_ID": invalid identifier

There is no column called LIST_STEP_LINK_ID in the table with the alias LSL2 - LIST_STEP_LINK
Re: Variable is NULL [message #585015 is a reply to message #585012] Tue, 21 May 2013 07:20 Go to previous messageGo to next message
Johnqiu
Messages: 9
Registered: May 2013
Junior Member
You right Shocked

Addition to test case 2

alter table LIST_STEP_LINK
add LIST_STEP_LINK_ID number(12)

Update LIST_STEP_LINK set LIST_STEP_LINK_ID = 1906484 where FAL_SCHEDULE_STEP_ID = 1906484;
Update LIST_STEP_LINK set LIST_STEP_LINK_ID = 1906485 where FAL_SCHEDULE_STEP_ID = 1906485;
Update LIST_STEP_LINK set LIST_STEP_LINK_ID = 1906487 where FAL_SCHEDULE_STEP_ID = 1906487;
Update LIST_STEP_LINK set LIST_STEP_LINK_ID = 1906488 where FAL_SCHEDULE_STEP_ID = 1906488;
Update LIST_STEP_LINK set LIST_STEP_LINK_ID = 1906489 where FAL_SCHEDULE_STEP_ID = 1906489;
Update LIST_STEP_LINK set LIST_STEP_LINK_ID = 1906490 where FAL_SCHEDULE_STEP_ID = 1906490;
Update LIST_STEP_LINK set LIST_STEP_LINK_ID = 1906482 where FAL_SCHEDULE_STEP_ID = 1906482;

Result:
SQL> declare
2 cursor c1
3 is
4 select scs_long_descr,
5 (select TAS_REF from TASK FT, LIST_STEP_LINK LSL2
6 where FT.FAL_TASK_ID = LSL2.FAL_TASK_ID
7 AND LSL2.LIST_STEP_LINK_ID =LSL.LIST_STEP_LINK_ID
8 )
9 from list_step_link lsl
10 where fal_schedule_step_id = 1906482
11 for update of scs_free_descr;
12 begin
13 for r1 in c1
14 loop
15 update list_step_link
16 set scs_free_descr = r1.TAS_REF || CHR(13) || r1.scs_long_descr
17 where current of c1;
18 end loop;
19 end;
20 /
set scs_free_descr = r1.TAS_REF || CHR(13) || r1.scs_long_descr
*
ERROR at line 16:
ORA-06550: line 16, column 34:
PLS-00302: component 'TAS_REF' must be declared
ORA-06550: line 16, column 31:
PL/SQL: ORA-00904: "R1"."TAS_REF": invalid identifier
ORA-06550: line 15, column 7:
PL/SQL: SQL Statement ignored


SQL>
Re: Variable is NULL [message #585017 is a reply to message #585015] Tue, 21 May 2013 07:30 Go to previous messageGo to next message
Littlefoot
Messages: 19320
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This:
5 (select TAS_REF from TASK FT, LIST_STEP_LINK LSL2
6 where FT.FAL_TASK_ID = LSL2.FAL_TASK_ID
7 AND LSL2.LIST_STEP_LINK_ID =LSL.LIST_STEP_LINK_ID
8 )
should have an alias (TAS_REF, as you put it later in line 16).
Re: Variable is NULL [message #585018 is a reply to message #585012] Tue, 21 May 2013 07:32 Go to previous messageGo to next message
cookiemonster
Messages: 10846
Registered: September 2008
Location: Rainy Manchester
Senior Member
cookiemonster wrote on Tue, 21 May 2013 12:56
1) All code, including create table, insert statements and output from sqlplus should be in code tags.

Your sub-query needs an alias. If you run that select in sqlplus you'll see the column title isn't tas_ref.
Re: Variable is NULL [message #585019 is a reply to message #585017] Tue, 21 May 2013 07:35 Go to previous messageGo to next message
Johnqiu
Messages: 9
Registered: May 2013
Junior Member
Hi Little_foot,

could you put an example, please?
Re: Variable is NULL [message #585020 is a reply to message #585019] Tue, 21 May 2013 07:37 Go to previous messageGo to next message
Littlefoot
Messages: 19320
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
   CURSOR c1
   IS
          SELECT scs_long_descr,
                 (SELECT TAS_REF
                    FROM TASK FT, LIST_STEP_LINK LSL2
                   WHERE FT.FAL_TASK_ID = LSL2.FAL_TASK_ID
                         AND LSL2.LIST_STEP_LINK_ID = LSL.LIST_STEP_LINK_ID
                 ) TAS_REF                                                   --> this is an alias
            FROM list_step_link lsl
           WHERE fal_schedule_step_id = 1906482
      FOR UPDATE OF scs_free_descr;
Re: Variable is NULL [message #585021 is a reply to message #585019] Tue, 21 May 2013 07:37 Go to previous messageGo to next message
cookiemonster
Messages: 10846
Registered: September 2008
Location: Rainy Manchester
Senior Member
         select scs_long_descr,
         (select TAS_REF from TASK FT, LIST_STEP_LINK LSL2
         where FT.FAL_TASK_ID = LSL2.FAL_TASK_ID
         AND LSL.LIST_STEP_LINK_ID =LSL2.LIST_STEP_LINK_ID
         ) tas_ref
            from list_step_link lsl
           where fal_schedule_step_id = 1906482


But if you don't know how to alias things you may want to spend some time reading the documentation.
Re: Variable is NULL [message #585022 is a reply to message #585021] Tue, 21 May 2013 07:43 Go to previous messageGo to next message
Johnqiu
Messages: 9
Registered: May 2013
Junior Member
Ok, OK, .. Embarassed
Thanks everybody.
Re: Variable is NULL [message #585035 is a reply to message #585022] Tue, 21 May 2013 08:34 Go to previous messageGo to next message
Michel Cadot
Messages: 58602
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And next time, do NOT forget to format your post.

Regards
Michel
Re: Variable is NULL [message #585038 is a reply to message #585035] Tue, 21 May 2013 08:38 Go to previous message
Johnqiu
Messages: 9
Registered: May 2013
Junior Member
OK, will do Sad
Previous Topic: utl_file error
Next Topic: Cursor and variable issue.
Goto Forum:
  


Current Time: Mon Jul 28 08:43:30 CDT 2014

Total time taken to generate the page: 0.17865 seconds