1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to return group of characters.,?

Discussion in 'SQL PL/SQL' started by Vicky, Nov 24, 2015.

  1. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Actually, I've written the below function for returning group of numbers using ARRAY.,?

    Code (SQL):

    CREATE OR REPLACE
    FUNCTION f1(a IN NUMBER)
      RETURN array
      pipelined
      AS
      j NUMBER;
      BEGIN
      FOR i IN 1..a loop
      pipe ROW(i);
      END loop;
      RETURN;
      END;
     
    Likewise, Instead of ARRAY, Which collection data type, I've to use to return group of characters.,?
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Main link : Table Functions
    Additional link : : https://oracle-base.com/articles/misc/pipelined-table-functions

    For example :

    Code (SQL):
    CREATE OR REPLACE FUNCTION test_array (a pls_integer)
    RETURN sys.odcinumberlist pipelined
    AS
    BEGIN
        FOR z IN 1 .. a
        loop
            pipe ROW(z);
        END loop;
        RETURN;
    END;
    /

    SELECT * FROM TABLE(test_array(7));

    DROP FUNCTION test_array;


    SQL>
    FUNCTION created
    COLUMN_VALUE
    ------------
      1
      2
      3
      4
      5
      6
      7
    7 ROWS selected
    FUNCTION dropped


     

    or


    Code (SQL):

    CREATE OR REPLACE TYPE  array IS TABLE OF NUMBER;
    /
    CREATE OR REPLACE FUNCTION test_array (a pls_integer)
    RETURN array pipelined
    AS
    BEGIN
        FOR z IN 1 .. a
        loop
            pipe ROW(z);
        END loop;
        RETURN;
    END;
    /

    SELECT * FROM TABLE(test_array(7));

    DROP FUNCTION test_array;
    DROP  TYPE array;


    SQL>
    TYPE created
    FUNCTION created
    COLUMN_VALUE
    ------------
               1
               2
               3
               4
               5
               6
               7
    7 ROWS selected
    FUNCTION dropped
    TYPE dropped
     
    or
    Code (SQL):

    CREATE OR REPLACE TYPE  array IS TABLE OF anydata;
    /
    CREATE OR REPLACE FUNCTION test_array (a pls_integer)
    RETURN array pipelined
    AS
    BEGIN
        FOR z IN 1 .. a
        loop
            pipe ROW(anydata.ConvertVarchar2(chr(64+z)));
        END loop;
        RETURN;
    END;
    /

    SELECT anydata.AccessVarchar2(column_value) chars FROM TABLE(test_array(7));

    DROP FUNCTION test_array;
    DROP  TYPE array;


    SQL>
    TYPE created
    FUNCTION created
    CHARS
    --------------------------------------------------------------------------------
    A
    B
    C
    D
    E
    F
    G
    7 ROWS selected
    FUNCTION dropped
    TYPE dropped



     
     
    Last edited: Nov 24, 2015
    Vicky likes this.
  3. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Tnx Sergy.,

    Is der any inbuilt type like sys.odcinumberlist(for number) for returning varchar list.?
     
    Last edited: Nov 25, 2015
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation

    sys.odcivarchar2list

    It is declared as : "
    CREATE OR REPLACE NONEDITIONABLE TYPE "SYS"."ODCIVARCHAR2LIST"
    AS VARRAY(32767) OF VARCHAR2(4000);"
     
    Vicky likes this.
  5. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Tnx sergry,. And is there any possibility to return more than 1 column.?
     
  6. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Yes, of course.

    See this link : PL/SQl Record
     
    Last edited: Nov 27, 2015