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

Thread: Stored Procedure - Syntax

  1. #1
    Join Date
    Oct 2001
    Location
    Hornchurch, Essex UK
    Posts
    132
    One of the developers has written a sql script which he want to run in Oracle. This has to be in PL/SQL syntax. Currently when this code is run it comes back as invalid.

    Could someone tell me what's wrong this code.

    BEGIN

    SELECT CODE.CLIENT_CODE_ID,
    CLIENT.NAME,
    TaskCount,
    ProcessCount
    FROM
    EP3R.EP_CLIENT_CODE CODE,
    EP3R.EP_CLIENT_NAME CLIENT,
    (SELECT TBL1.CLIENT_CODE_ID AS CLIENTCODE, count(*) AS TaskCount FROM EP3R.EP_CLIENT_CODE TBL1,
    WORKFLOW3R.WF2_TASK_ENTITY_VALUES TBL4,
    WORKFLOW3R.WF2_TASK_INSTANCE TBL5
    WHERE TBL1.ClIENT_CODE_ID = (TO_NUMBER(TBL4.TEV_VALUE))
    AND TBL4.TEV_TASK_INSTANCE_ID = TBL5.TI_ID
    AND TBL4.TEV_ENTITY_ID = 99
    GROUP BY TBL1.CLIENT_CODE_ID) PART1,
    (SELECT TBL6.CLIENT_CODE_ID AS CLIENTCODE2, count(*) AS ProcessCount from EP3R.EP_CLIENT_CODE TBL6,
    WORKFLOW3R.WF2_COLLECTION_ENTITY_VALUES TBL2,
    WORKFLOW3R.WF2_COLLECTION_INSTANCE TBL3
    WHERE TBL6.CLIENT_CODE_ID = (TO_NUMBER(TBL2.CEV_VALUE))
    AND TBL2.CEV_COLLECTION_INSTANCE_ID = TBL3.CI_ID
    AND TBL2.CEV_ENTITY_ID = 99
    GROUP BY TBL6.CLIENT_CODE_ID) PART2;

    END IF;


    Any help will be greatly appreciated.

    Thanks

    J

  2. #2
    Join Date
    Nov 2001
    Location
    UK
    Posts
    152
    You need to select into some variables e.g.

    SELECT CODE.CLIENT_CODE_ID,
    CLIENT.NAME,
    TaskCount,
    ProcessCount
    into v_client_name, v_taskcount, v_process_count
    from ...

    However, if you get back more than one row it will raise an error. You will need to rewrite it as a cursor in that case.

    I suggest you spend some time reading the PL/SQL manual as you obviously don't know much about the language.

  3. #3
    Join Date
    Jan 2002
    Posts
    33
    first, i will start copy and paste each segment of the code and test them seperately.
    second, you have a begin but no end.
    third, you have an ending end if without a corresponding beginning if statement.
    fourth, you cannot run this query in pl/sql as it is unless of course you use a cursor.
    I am sure there are more problems with it but those stand out.

  4. #4
    Join Date
    Oct 2001
    Location
    Hornchurch, Essex UK
    Posts
    132
    Thanks for all your help.

    J

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