Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Location
    Chennai, India
    Posts
    18

    Unanswered: Pl/sql That Works In Sqlplus But Not In Forms

    Hi,

    This is a PL/SQL that I wrote and was able to execute this in sqlplus. But when I used the same in a trigger in Oracle forms it gives a error.

    DECLARE

    TYPE CV_TYPE IS REF CURSOR;
    CV CV_TYPE;

    PARTICIPANT_KEY VARCHAR2 (100);
    SALES_REP_CD VARCHAR2 (100);
    SALES_REP_KEY VARCHAR2 (100);
    STR VARCHAR2(100);
    STR1 VARCHAR2(100);
    STR2 VARCHAR2(100);

    BEGIN
    STR2 := 'a.PARTICIPANT_KEY = 10002';
    OPEN CV FOR
    'SELECT A.PARTICIPANT_KEY PARTICIPANT_KEY, B.SALES_REP_CD,B.SALES_REP_KEY FROM PARTICIPANT_MASTER A,PARTICIPANT_TERRITORY B WHERE A.PARTICIPANT_KEY = B.PARTICIPANT_KEY AND ' || STR2;

    STR:='SALES_REP_CD';
    LOOP
    FETCH CV INTO PARTICIPANT_KEY, SALES_REP_CD, SALES_REP_KEY;
    EXIT WHEN CV%NOTFOUND;
    STR1 := NULL;
    IF STR = 'PARTICIPANT_KEY' THEN STR1 := STR1||','||PARTICIPANT_KEY; END IF;
    IF STR = 'SALES_REP_CD' THEN STR1 := STR1||','||SALES_REP_CD; END IF;
    IF STR = 'SALES_REP_KEY' THEN STR1 := STR1||','||SALES_REP_KEY; END IF;
    DBMS_OUTPUT.PUT_LINE(STR1);
    END LOOP;
    END;

    Error Message :

    Error 103 at line 16,column 10
    Encountered the symbol "SELECT A.PARTICIPANT_KEY..." when expecting one of the following

    select

    Is it not possible to use referential cursors in Forms?

    This is an urgent requirement.
    Palaniappan.S
    Chennai.

  2. #2
    Join Date
    Sep 2003
    Location
    Hanoi- Vietnam
    Posts
    27
    May be invokle privileges. You should test user use in form or Check synonym.

  3. #3
    Join Date
    Mar 2004
    Location
    Chennai, India
    Posts
    18
    Originally posted by hungdq
    May be invokle privileges. You should test user use in form or Check synonym.
    The select statement alone works fine in forms. It says that in the cursor statement 'for' keyword should be followed by a select rather than 'select.. (select within quotes)
    Palaniappan.S
    Chennai.

Posting Permissions

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