Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2003
    Location
    Woking
    Posts
    107

    Unanswered: Unable to build a cursor

    HI,
    I Have this SQL.

    SELECT empno,
    ename,
    job,
    sal,
    deptno,
    SUM(sal) OVER(PARTITION BY deptno ORDER BY deptno) sum_sal
    FROM emp a,
    dual b
    WHERE to_char(a.deptno) = b.dummy(+)
    ORDER BY deptno;

    When i run this SQL on SQL*PLUS, the is no problem.
    when i try to create a cursor using the same SQL,
    i get the following Error

    ORA-06550: line 7, column 25:
    PLS-00103: Encountered the symbol "(" when expecting one of the following:

    The Cursor is built as follows

    DECLARE CURSOR C1 IS
    SELECT empno,
    ename,
    job,
    sal,
    deptno,
    SUM(sal) OVER(PARTITION BY deptno ORDER BY deptno) sum_sal
    FROM emp a,
    dual b
    WHERE to_char(a.deptno) = b.dummy(+)
    ORDER BY deptno;
    BEGIN
    NULL;
    END;
    /

    Please help ....
    nn

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Unable to build a cursor

    Originally posted by NoviceNo1
    HI,
    I Have this SQL.

    SELECT empno,
    ename,
    job,
    sal,
    deptno,
    SUM(sal) OVER(PARTITION BY deptno ORDER BY deptno) sum_sal
    FROM emp a,
    dual b
    WHERE to_char(a.deptno) = b.dummy(+)
    ORDER BY deptno;

    When i run this SQL on SQL*PLUS, the is no problem.
    when i try to create a cursor using the same SQL,
    i get the following Error

    ORA-06550: line 7, column 25:
    PLS-00103: Encountered the symbol "(" when expecting one of the following:

    The Cursor is built as follows

    DECLARE CURSOR C1 IS
    SELECT empno,
    ename,
    job,
    sal,
    deptno,
    SUM(sal) OVER(PARTITION BY deptno ORDER BY deptno) sum_sal
    FROM emp a,
    dual b
    WHERE to_char(a.deptno) = b.dummy(+)
    ORDER BY deptno;
    BEGIN
    NULL;
    END;
    /

    Please help ....
    This happens because your PL/SQL engine does not support analytic functions. The PL/SQL engine has always lagged behind when new functionality is added to SQL, catching up a version or so later - though I think that is no longer the case since 9i.

    One solution is to use dynamic SQL: this is not processed by the PL/SQL engine but passed to the SQL engine.

  3. #3
    Join Date
    Jan 2003
    Location
    Woking
    Posts
    107

    Re: Unable to build a cursor

    Originally posted by andrewst
    This happens because your PL/SQL engine does not support analytic functions. The PL/SQL engine has always lagged behind when new functionality is added to SQL, catching up a version or so later - though I think that is no longer the case since 9i.

    One solution is to use dynamic SQL: this is not processed by the PL/SQL engine but passed to the SQL engine.

    Thanks Andrew. Dynamic SQL Worked.
    nn

  4. #4
    Join Date
    Apr 2004
    Posts
    1

    Re: Unable to build a cursor

    Originally posted by NoviceNo1
    Thanks Andrew. Dynamic SQL Worked.
    And how can build this select with dynamic sql????

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Unable to build a cursor

    Use a "ref cursor":
    PHP Code:
    SQL> DECLARE
      
    2    rc SYS_REFCURSOR;
      
    3    v_sql VARCHAR2(100) := 'SELECT ename FROM emp WHERE deptno = :d';
      
    4    v_ename emp.ename%TYPE;
      
    5  BEGIN
      6    OPEN rc 
    FOR v_sql USING 10;
      
    7    LOOP
      8      FETCH rc INTO v_ename
    ;
      
    9      EXIT WHEN rc%NOTFOUND;
     
    10      DBMS_OUTPUT.PUT_LINEv_ename );
     
    11    END LOOP;
     
    12    CLOSE rc;
     
    13  END;
     
    14  /
    CLARK
    KING
    MILLER

    PL
    /SQL procedure successfully completed

Posting Permissions

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