Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2004
    Posts
    2

    Unanswered: handling unknown number of parameters passed to anonymous pl/sql block

    I have to write an anonymous block of sql which can accept an unknown number parameters. I've done this for 1 parameter (see below). The pl/sql will be stored in a file and the user will run it, supplying up to 10 policy_seqences to be updated. I don't know how to amend it to handle an unknown number of params Can anyone help me? Thanks



    DECLARE

    T_POLICY_SEQUENCE number(9) := &1;

    BEGIN

    UPDATE T_SCHED_ACTIVITIES SET ERROR_CODE=' ' WHERE POLICY_SEQUENCE=T_POLICY_SEQUENCE
    COMMIT;

    END;
    /

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    SQL Plus really isn't up to building applications, it is a tool for developers and DBAs. You should really build an application with something more sophisticated.

    However, this will allow a more or less unlimited number of parameters:
    Code:
    BEGIN
    
    UPDATE T_SCHED_ACTIVITIES SET ERROR_CODE=' ' WHERE POLICY_SEQUENCE IN (&1);
    COMMIT;
    
    END;
    /
    The user has to type in the values separated by a comma all on one line like this:

    SQL> @myscript 123,456,777,492

  3. #3
    Join Date
    Nov 2004
    Posts
    2
    Thanks very much , that worked a treat.

Posting Permissions

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