Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2005
    Posts
    276

    Unanswered: Collections and Functions

    Hi all,

    I'm working on collections and this is the first time and hence its very confusing. Can anyone please help me with the below.

    I have a collection defined as below-
    TYPE C_TYPE AS OBJECT
    (clnt_c VARCHAR2(10),
    clnt_did NUMBER)

    CREATE OR REPLACE
    TYPE c_list As table of C_TYPE
    And a function in which the above collection is passed as a parameter.

    FUNCTION f_get_c (
    cpc IN VARCHAR2,
    cpc_tab IN c_list)
    RETURN VARCHAR2

    IS

    a VARCHAR2 (10) := cpc;
    ret_key VARCHAR2(10);
    begin
    SELECT clnt_c
    INTO ret_key
    FROM TABLE (CAST (cpc_tab AS c_list))
    WHERE clnt_c not in ('usa','can');
    RETURN (ret_key);
    END f_get_c;
    I want to execute the above function but how do I pass the parameter for the collection? All I'm interested in is the clnt_c field in the collection c_list.

  2. #2
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    If it is just for a test, call the constructor of the table and then the constructor for each object of the collection, for example :

    Code:
    C_LIST(C_TYPE('foo', 1), C_TYPE('bar', 2))
    If you are using an API in another language, then it depends on the API
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

Posting Permissions

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