Results 1 to 5 of 5

Thread: pl/sql problem

  1. #1
    Join Date
    Aug 2005
    Location
    Kuala Lumpur, MY
    Posts
    108

    Unanswered: pl/sql problem

    hie all,
    im having a problem here
    i have this script and when i run it via normal sql, its fine but when i add the pl/sql function "begin and end;" im having a problem

    pls advise

    the normal sql:
    Code:
    insert into e01_ers_purc_ord_to_dealer(date_reconcilation, distributor_id, curr_currbal, dist_currbal, dist_amt_uploaded, deal_amt_uploaded)
    select b.date_reconcilation, a.distributor_id, b.current_balance curr_currbal, 
    (select nvl(sum(c.amount_uploaded),0) from e01_ers_purc_ord_to_dealer_dea c 
    where c.distributor_id(+) = a.distributor_id
      and c.date_purchased = b.date_reconcilation) deal_amt_uploaded,
    (select nvl(sum(d.current_balance),0) from e01_ers_purc_ord_to_dealer_dis d 
    where d.distributor_id(+) = a.distributor_id
      and d.date_purchased = b.date_reconcilation) dist_currbal,
    (select nvl(sum(e.amount_uploaded),0) from e01_ers_purc_ord_to_dealer_dis e 
    where e.distributor_id(+) = a.distributor_id
      and e.date_purchased = b.date_reconcilation) dist_amt_uploaded
    from vw_e01_distributor_id a, e01_ers_purc_ord_to_dealer_cur b
    where a.distributor_id = b.distributor_id(+)
    and  (b.date_reconcilation, a.distributor_id) not in (select date_reconcilation, distributor_id
    from e01_ers_purc_ord_to_dealer)

    when i add the pl/sql:
    Code:
    begin
    insert into e01_ers_purc_ord_to_dealer(date_reconcilation, distributor_id, curr_currbal, dist_currbal, dist_amt_uploaded, deal_amt_uploaded)
    select b.date_reconcilation, a.distributor_id, b.current_balance curr_currbal, 
    (select nvl(sum(c.amount_uploaded),0) from e01_ers_purc_ord_to_dealer_dea c 
    where c.distributor_id(+) = a.distributor_id
      and c.date_purchased = b.date_reconcilation) deal_amt_uploaded,
    (select nvl(sum(d.current_balance),0) from e01_ers_purc_ord_to_dealer_dis d 
    where d.distributor_id(+) = a.distributor_id
      and d.date_purchased = b.date_reconcilation) dist_currbal,
    (select nvl(sum(e.amount_uploaded),0) from e01_ers_purc_ord_to_dealer_dis e 
    where e.distributor_id(+) = a.distributor_id
      and e.date_purchased = b.date_reconcilation) dist_amt_uploaded
    from vw_e01_distributor_id a, e01_ers_purc_ord_to_dealer_cur b
    where a.distributor_id = b.distributor_id(+)
    and  (b.date_reconcilation, a.distributor_id) not in (select date_reconcilation, distributor_id
    from e01_ers_purc_ord_to_dealer);
    end;
    /

    the error highlights the select and returns:
    ORA-06550: line 4, column 2:
    PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:

    ( - + mod not null others <an identifier>
    <a double-quoted delimited-identifier> <a bind variable> avg
    count current exists max min prior sql stddev sum variance
    execute forall time timestamp interval date
    <a string literal with character set specification>
    <a number> <a single-quoted SQL string>
    ORA-06550: line 6, column 48:
    PLS-00103: Encountered the symbol "DEAL_AMT_UPLOADED" when expecting one of the following:

    ; return returning and or
    ORA-06550: line 13, column 1:
    PLS-00103: Encountered the symbol "FROM" when expecting one of the following:

    , ; for group having intersect minus order start union where
    connect

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I guess it is about the Oracle DB version you're on ... is it lower than 9i? Perhaps 8.x.x? If so, you're about to get such an error message.

    Possible solution might be something like this (example on Scott's schema): instead of such a query (actually, this is your modified code)
    Code:
    BEGIN
       INSERT INTO BRISIME
                   (deptno, sal)
          SELECT d.deptno, (SELECT SUM (e.sal)
                              FROM EMP e
                             WHERE e.deptno = d.deptno) sal
            FROM DEPT d;
    END;
    rewrite it to look like this:
    Code:
    BEGIN
       INSERT INTO BRISIME
                   (deptno, sal)
          SELECT d.deptno, e1.sal
            FROM DEPT d, (SELECT   e.deptno, SUM (e.sal) sal
                              FROM EMP e
                          GROUP BY e.deptno) e1
           WHERE e1.deptno = d.deptno;
    END;
    Besides, when you get it to work, you might be surprised as this is what you are doing (your simplified query) - the columns do not match:
    Code:
    INSERT INTO e01
                (date_reconcilation, distributor_id, curr_currbal,
                 dist_currbal, dist_amt_uploaded, deal_amt_uploaded
                )
         VALUES (date_reconcilation, distributor_id, current_balance,
                 deal_amt_uploaded, dist__currbal, dist_amt_uploaded
                )

  3. #3
    Join Date
    Aug 2005
    Location
    Kuala Lumpur, MY
    Posts
    108
    little foot,
    this also doesnt work...
    hmm...

    Code:
    begin
    INSERT INTO e01_ers_purc_ord_to_dealer(date_reconcilation, distributor_id, curr_currbal, deal_amt_uploaded)
        ( SELECT date_reconcilation, distributor_id, curr_currbal, deal_amt_uploaded
    FROM
        ( SELECT b.date_reconcilation, a.distributor_id, b.current_balance AS curr_currbal, 
        ( SELECT nvl(sum(c.amount_uploaded),0) 
          from e01_ers_purc_ord_to_dealer_dea c
          WHERE  c.distributor_id(+) = a.distributor_id
          AND    c.date_purchased = b.date_reconcilation
        ) AS deal_amt_uploaded
    FROM  vw_e01_distributor_id a, e01_ers_purc_ord_to_dealer_cur b
    where a.distributor_id = b.distributor_id(+))
    )
    end;
    /

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    "This doesn't work" isn't very descriptive. Did you receive any additional error messages? If so, which ones?

    Besides, the code you wrote wouldn't compile as it has a syntax error (you missed semicolon at the end of the INSERT statement).

    And, would you finally consider posting your working environment?

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Also, he is doing exactly the same thing he did the first time, which you already explained - i.e. using a scalar subquery in a version of PL/SQL that can't handle them.

Posting Permissions

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