DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: re: code not compiling

  1. #1
    Join Date
    Jan 2004
    Posts
    58

    Question re: code not compiling

    hi,

    the oracle version I am working on is
    SQL*Plus: Release 9.2.0.1.0 - Production on Wed Nov 24 12:06:01 2004

    Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


    Connected to:
    Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
    With the Partitioning, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.4.0 - Production


    The code I am trying to run is
    DECLARE
    TYPE RecTabTyp IS TABLE OF tab1%ROWTYPE
    INDEX BY BINARY_INTEGER;
    TYPE NumTabTyp IS TABLE OF NUMBER
    INDEX BY BINARY_INTEGER;
    TYPE CharTabTyp IS TABLE OF VARCHAR2(20)
    INDEX BY BINARY_INTEGER;
    CURSOR c1 IS SELECT col1, col2 FROM tab2;
    rec_tab RecTabTyp;
    num_tab NumTabTyp := NumTabTyp(2,5,8,9);
    char_tab CharTabTyp := CharTabTyp('Tim', 'Jon', 'Beth', 'Jenny');
    BEGIN
    FORALL i IN 1..4
    INSERT INTO tab1 VALUES(num_tab(i), char_tab(i));

    SELECT col1, col2 BULK COLLECT INTO rec_tab FROM tab1
    WHERE col1 < 9;

    FORALL i IN rec_tab.FIRST..rec_tab.LAST
    INSERT INTO tab2 VALUES rec_tab(i);

    FOR i IN rec_tab.FIRST..rec_tab.LAST LOOP
    rec_tab(i).col1 := rec_tab(i).col1 + 100;
    END LOOP;

    FORALL i IN rec_tab.FIRST..rec_tab.LAST
    UPDATE tab1 SET (col1, col2) = rec_tab(i) WHERE col1 < 8;

    OPEN c1;
    FETCH c1 BULK COLLECT INTO rec_tab;
    CLOSE c1;
    END;

    Its complaining about the following lines
    -----------------------------------------------------------
    UPDATE tab1 SET (col1, col2) = rec_tab(i) WHERE col1 < 8;
    ERROR at line 4:
    ORA-06550: line 27, column 38:
    PL/SQL: ORA-01767: UPDATE ... SET expression must be a subquery
    ORA-06550: line 27, column 7:
    PL/SQL: SQL Statement ignored
    ------------------------------------------------------------
    num_tab NumTabTyp := NumTabTyp(2,5,8,9);
    char_tab CharTabTyp := CharTabTyp('Tim', 'Jon', 'Beth', 'Jenny');

    num_tab NumTabTyp := NumTabTyp(2,5,8,9);
    *
    ERROR at line 10:
    ORA-06550: line 10, column 26:
    PLS-00222: no function with name 'NUMTABTYP' exists in this scope
    ORA-06550: line 10, column 13:
    PL/SQL: Item ignored
    ORA-06550: line 11, column 27:
    PLS-00222: no function with name 'CHARTABTYP' exists in this scope
    ORA-06550: line 11, column 13:
    PL/SQL: Item ignored
    ORA-06550: line 14, column 31:
    PLS-00320: the declaration of the type of this expression is incomplete or
    malformed
    ORA-06550: line 14, column 31:
    PL/SQL: ORA-00904: : invalid identifier
    ORA-06550: line 14, column 7:
    PL/SQL: SQL Statement ignored
    ---------------------------------------------------------------
    the other thing is its not letting me initialize in the declaration
    section
    ex: rec_tab RecTabTyp := RecTabTyp();

    rec_tab RecTabTyp := RecTabTyp();
    *
    ERROR at line 9:
    ORA-06550: line 9, column 26:
    PLS-00222: no function with name 'RECTABTYP' exists in this scope
    ORA-06550: line 9, column 13:
    PL/SQL: Item ignored
    -----------------------------------------------------------------

    What am I missing .. is it the version ???

    Can somebody Please enlighten me as why the problems are occuring
    and how do I fix them ?

    thanks a lot for your help ,
    Harish
    thanks,
    harish

  2. #2
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    Hi Harish

    What are you trying to do in the declaration section? You want to declare rec_tab as a type of RecTabTyp?

    If so try:

    rec_tab RecTabTyp%TYPE;

  3. #3
    Join Date
    Jan 2004
    Posts
    58

    Question let me make it simple ...

    oracle is not letting me initialize the table ..
    num_tab NumTabTyp := NumTabTyp(2,5,8,9);
    ------------------------------------------------
    DECLARE
    TYPE RecTabTyp IS TABLE OF tab1%ROWTYPE
    INDEX BY BINARY_INTEGER;
    TYPE NumTabTyp IS TABLE OF NUMBER
    INDEX BY BINARY_INTEGER;
    TYPE CharTabTyp IS TABLE OF VARCHAR2(20)
    INDEX BY BINARY_INTEGER;
    CURSOR c1 IS SELECT col1, col2 FROM tab2;
    rec_tab RecTabTyp;
    num_tab NumTabTyp := NumTabTyp(2,5,8,9);
    --------------------------------------------

    thanks,
    harish
    thanks,
    harish

  4. #4
    Join Date
    Nov 2004
    Location
    Chennai
    Posts
    38
    Hi,
    check ur UPDATE syntax .It shud b like ...

    set tab_name
    (column, column,...) = (subquery)

    Akila Rajakumar

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width