Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2012

    Unanswered: Arrays in Stored Procedure

    Hi all,
    I am trying to create a array using db2 pl/sql stored procedure. Here is the sample code.

    CREATE TYPE phonenumbers AS VARCHAR(12) ARRAY[1000]
    -- Procedure find_customers searches for numbers in
    -- numbers_in that begin with the given area_code,
    -- and reports them in numbers_out.
    -- Phone numbers are strings of the form 416-413-9394

    CREATE PROCEDURE find_customers(
    IN numbers_in phonenumbers,
    IN area_code CHAR(3),
    OUT numbers_out phonenumbers)
    SET numbers_out =
    FROM UNNEST(numbers_in) AS T(num)
    WHERE substr(T.num, 1, 3) = area_code);

    I am using IBM data studio software and trying to run this code in the specified software. The problem is that it is indicating the error on line no 1 i.e. CREATE TYPE phonenumbers AS VARCHAR(12) ARRAY[1000]. The error is "statement_terminator missing". Wen i give semicolon as statement terminator, stil it gives the same error. Is there any other way to declare or use the arrays in pl/sql stored procedure. Or is there any other alternative to pass multiple values into a variable as input. Please help.

  2. #2
    Join Date
    Jan 2003
    Provided Answers: 5
    You cannot use the semicolon as the statement terminator because of the Stored Procedure definition. You need to use something else. Most people use the @. You also need to tell Data Studio what the terminator character is.


Posting Permissions

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