Using a select in the select list of a FOR loop
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Using a select in the select list of a FOR loop

Hybrid View

  1. #1
    Join Date
    Dec 2002
    Location
    US
    Posts
    2

    Using a select in the select list of a FOR loop

    This is what i am trying :

    abc is a list of values

    FORALL i in abc.First..abc.last
    INSERT INTO table1 (SELECT a.1,b.1,b.2-(SELECT NVL(SUM(x.1) FROM x WHERE ...),c.1
    FROM a,b,c
    where ....;

    If i execute the SQL part alone , it works.. But in a package , gives
    compile errors.. "PLS-00103 : Encountered the symbol "SELECT" ...."

    Any ideas???

    Vijay.

  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    What version of oracle u are using?
    Probably u have to use dynamic sql for execute ur insert statment.

  3. #3
    Join Date
    Dec 2002
    Location
    US
    Posts
    2
    we use 8i.

    Could you please elaborate if possible?

    The select in the select list list uses values from the select list for joining and not the value in the FORALL list!

    Thanx for the reply..

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    well that scalar subquery probabky is not supported in PL/SQL until 9i, in 8i they use seperate engines for SQL and PL/SQL so things that works in SQL doesnt mean they would work in PL/SQL and the way to achieve your goal is use dynamic SQL ad shestakov suggested

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