Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2008
    Posts
    4

    Unanswered: ORA-06532: Subscript outside of limit

    Hello,

    I am trying to call a query that displays each item with its suppliers (code and names).
    I am using a function that takes the item code as parameter to read its suppliers and display them in one row. I dont now if this is possible but i am trying to find a way and this is what i am getting:


    select itm_id , itm_name

    , csrdba.get_suppliers_test( itm_id )

    from ref_item where itm_id IN ( 151769, 152085 )


    ORA-06532: Subscript outside of limit
    ORA-06512: at "CSRDBA.GET_SUPPLIERS_TEST", line 47



    /
    CREATE OR REPLACE TYPE ind_tab_supplier0 AS OBJECT
    (ITP_SUPPLIER_CODE VARCHAR2(50),
    ITP_SUPPLIER_NAME VARCHAR2(50))
    /

    /
    CREATE OR REPLACE TYPE ind_tab_supplier1 IS VARRAY(6) OF VARCHAR2(30)
    /

    /
    CREATE OR REPLACE TYPE ind_tab_supplier_table AS TABLE OF ind_tab_supplier1
    /



    CREATE OR REPLACE FUNCTION "GET_SUPPLIERS_TEST" (itemCode in ref_item.itm_id%TYPE)

    RETURN ind_tab_supplier_table PIPELINED

    IS

    cnt number := -1;

    v_itemCode NUMBER (10);

    TYPE ref0 IS REF CURSOR;
    cur0 ref0;


    out_rec0 ind_tab_supplier0
    := ind_tab_supplier0(NULL,NULL);

    out_rec1 ind_tab_supplier1
    := ind_tab_supplier1(NULL);


    BEGIN

    v_itemCode := itemCode;


    OPEN cur0 FOR
    ' select t.ven_id , (select s.ven_name from ref_ven_vendor s '||
    'where s.ven_id = t.ven_id) "supplier_name" '||
    ' from ref_item_vendor t where t.itm_id = :1 '||
    ' AND t.ven_id IN (1330, 6858, 6918) '
    USING v_itemCode;
    LOOP
    FETCH cur0 INTO out_rec0.itp_supplier_code, out_rec0.itp_supplier_name;

    -- TO AVOID ENDLESS LOOP AND THERFORE AVOIDING ENDLESS EXECUTING
    IF cur0%NOTFOUND

    THEN
    PIPE ROW(out_rec1);
    RETURN;

    ELSE
    cnt := cnt + 1;

    out_rec1.extend;
    out_rec1(cnt) := out_rec0.itp_supplier_code; // error here

    cnt := cnt + 1;

    out_rec1.extend;
    out_rec1(cnt) := out_rec0.itp_supplier_name; // error here

    END IF;

    END LOOP;

    PIPE ROW(out_rec1);

    CLOSE cur0;

    RETURN;
    END GET_SUPPLIERS_TEST;


    If u can help me on my need or tell me if this is possible or not to display each item and their suppliers (as an example) in one row in oracle.

    Thank you in Advance.

  2. #2
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    A PIPELINE function is to be used as a table in a FROM clause, not in the SELECT clause : it is as if you were doing "SELECT ref_item ..."

    I think what you want to do is something like this :

    Code:
    CREATE OR REPLACE TYPE ind_tab_supplier0 AS OBJECT
    (ITP_SUPPLIER_CODE VARCHAR2(50),
    ITP_SUPPLIER_NAME VARCHAR2(50))
    /
    CREATE OR REPLACE TYPE ind_tab_supplier_table AS TABLE OF ind_tab_supplier0
    /
    CREATE OR REPLACE TYPE varchar2_table AS TABLE OF VARCHAR2(4000)
    /
    
    SELECT i.itm_id, 
        i.itm_name,
        CAST(COLLECT(ind_tab_supplier0(t.ven_id, s.ven_name)) AS ind_tab_supplier_table) AS supplier_obj_list,
        CAST(COLLECT(t.ven_id||'|'||s.ven_name)) AS varchar2_table) AS supplier_varchar2_list
    FROM 
        (
            SELECT 151769 AS itm_id, 'item1' AS itm_name FROM DUAL
            UNION ALL
            SELECT 152085 AS itm_id, 'item2' AS itm_name FROM DUAL
        ) /* ref_item */ i,
        (
            SELECT 151769 AS itm_id, 1330 AS ven_id FROM DUAL
            UNION ALL
            SELECT 151769 AS itm_id, 6858 AS ven_id FROM DUAL
            UNION ALL
            SELECT 151769 AS itm_id, 6918 AS ven_id FROM DUAL
            UNION ALL
            SELECT 152085 AS itm_id, 1330 AS ven_id FROM DUAL
        ) /* ref_item_vendor */ t,
        (
            SELECT 1330 AS ven_id, 'vendor1' AS ven_name FROM DUAL
            UNION ALL
            SELECT 6858 AS ven_id, 'vendor2' AS ven_name FROM DUAL
            UNION ALL
            SELECT 6918 AS ven_id, 'vendor3' AS ven_name FROM DUAL
        ) /* ref_ven_vendor */ s
    WHERE t.itm_id = i.itm_id
    AND s.ven_id = t.ven_id
    AND i.itm_id IN (151769, 152085)
    AND t.ven_id IN (1330, 6858, 6918)
    GROUP BY i.itm_id, 
        i.itm_name;
    
    151769	item1	HR.IND_TAB_SUPPLIER0(HR.IND_TAB_SUPPLIER0,HR.IND_TAB_SUPPLIER0,HR.IND_TAB_SUPPLIER0)	VARCHAR(1330|vendor1,6858|vendor2,6918|vendor3)
    152085	item2	HR.IND_TAB_SUPPLIER0(HR.IND_TAB_SUPPLIER0)	VARCHAR(1330|vendor1)
    supplier_obj_list is an array of supplier objects

    supplier_varchar2_list is an array of strings having the vendor id and vendor name separated by '|'

    With this you get only ONE line by item and the array of suppliers concerning this item, isn't it what you want ?
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  3. #3
    Join Date
    Jul 2008
    Posts
    4

    Thank u RBARAER concerning (ORA-06532: Subscript outside of limit)

    Thank you again for your help and consideration, I copied you suggested code and will try it and will be replying to you.

    In this time i was trying to call the query from ibatis (i am using ibatis)

    select itm_id , itm_name

    , csrdba.get_suppliers_test( itm_id )

    from ref_item where itm_id IN ( 151769, 152085 )


    and it was generating the following error on my server:

    ORA-01031:Insufficient Privileges


    I dont know if you are familiar with ibatis and if you can help me with it but isn't how we call a function and why it is giving this error althought the count query is returning the right number of rows.

    I will put a new thread about this in this forum or in other concerning ibatis.

    Thank u again very much.

  4. #4
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Je ne connaissais pas Ibatis mais je viens de jeter un coup d'oeil à son principe sur Wikipédia.

    Je pense que l'erreur que tu as viens du fait que l'utilisateur Oracle que tu utilises pour te connecter à la base via Ibatis n'a pas le droit d'exécution sur la fonction PIPELINE.

    De toute façon tu aurais eu l'erreur précédente .

    Je ne sais pas si tu vas pouvoir récupérer les tableaux que je t'ai proposés dans Ibatis... Si tu n'y arrives pas tu peux toujours convertir le tableau de varchar2 (le 2ème) en chaine de caractères avec un séparateur d'enregistrements. J'ai une procédure stockée qui fait ça si ça t'intéresse.
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  5. #5
    Join Date
    Jul 2008
    Posts
    4

    merci de nouveau RBARAER

    Ne t'en fais pas de la derniere error, et encore je n'est pas implementer votre code. Mais je vais le faire apres.
    But if you can help me with the following:

    I have the following query in ibatis:


    <sql id="testFunctionId">

    select '2' as action_no, '0' as nodePId, itm_id , itm_name

    , {call CSRDBA.GET_SUPPLIERS_TEST( itm_id )} colFields

    from ref_item where itm_id IN ( 151769, 152085 )

    </sql>

    <resultMap id="getSupplierResultList" class="SelectionDetailVO">
    <!--result property="supplier.id" column="ITP_SUPPLIER_CODE" /-->
    <!--result property="supplier.name" column="ITP_SUPPLIER_NAME" /-->
    <result property="supplier.id" columnIndex = "1" />
    <result property="supplier.name" columnIndex = "2" />
    </resultMap>

    <resultMap id="getTestFunctionGridResultList"class="Selection DetailVO" >
    <result property="actionNo" column="action_no" />
    <result property="nodePId" column="nodePId" />
    <result property="item.id" column="itm_id" />
    <result property="item.name" column="itm_name" />
    <!-- result property="suppliers" javaType="java.util.List" select="CSRDBA.GET_SUPPLIERS_TEST" column="itm_id" /-->
    <result property="suppliers" javaType="java.util.List" resultMap="CSR_SelectionDetail.getSupplierResultLi st" columnIndex="5"
    <!-- column="colFields" --> />
    </resultMap>



    <select id="getTestFunctionGridIbatisFlip"resultMap="getTe stFunctionGridResultList"parameterClass="Selection DetailSC">
    <include refid="CSR_Common.csrGridWrapperBefore" />
    <include refid="testFunctionId" />
    <include refid="CSR_Common.csrGridWrapperAfter" />
    </select>
    <select id="getTestFunctionGridManualFlipCount" resultClass="int"parameterClass="SelectionDetailSC ">
    <include refid="CSR_Common.csrGridWrapperBeforeCount" />
    <include refid="testFunctionId" />
    <include refid="CSR_Common.csrGridWrapperAfterCount" />
    </select>


    If i leave the column="colFields" in the result:

    <result property="suppliers" javaType="java.util.List" resultMap="CSR_SelectionDetail.getSupplierResultLi st" columnIndex="5"
    column="colFields" />


    It gives me the error:

    No type handler could be found to map the property 'suppliers' to the column 'colFields'. One or both of the types, or the combination of types is not supported.

    WHAT COULD BE THE TYPE OF THE COLUMN 'colFields' ?
    I TRIED THE COLLECTION TYPE BUT DID NOT WORK, AND MANY OTHER TYPES (ARRAY, ...).



    The following is the implementation of the function:

    CREATE OR REPLACE TYPE ind_tab_supplier0 AS OBJECT
    (ITP_SUPPLIER_CODE VARCHAR2(50),
    ITP_SUPPLIER_NAME VARCHAR2(50))


    CREATE OR REPLACE TYPE ind_tab_supplier_table AS TABLE OF ind_tab_supplier0


    CREATE OR REPLACE FUNCTION "GET_SUPPLIERS_TEST" (itemCode in ref_item.itm_id%TYPE)

    RETURN ind_tab_supplier_table PIPELINED

    IS

    v_itemCode NUMBER (10);

    TYPE ref0 IS REF CURSOR;
    cur0 ref0;

    out_rec0 ind_tab_supplier0
    := ind_tab_supplier0(NULL,NULL);

    BEGIN

    v_itemCode := itemCode;


    OPEN cur0 FOR
    ' select t.ven_id , (select s.ven_name from ref_ven_vendor s '||
    'where s.ven_id = t.ven_id) "supplier_name" '||
    ' from ref_item_vendor t where t.itm_id = :1 '||
    ' AND t.ven_id IN (1330, 6858, 6918) '
    USING v_itemCode;
    LOOP
    FETCH cur0 INTO out_rec0.itp_supplier_code, out_rec0.itp_supplier_name;

    -- TO AVOID ENDLESS LOOP AND THERFORE AVOIDING ENDLESS EXECUTING
    IF cur0%NOTFOUND THEN
    RETURN;
    END IF;

    PIPE ROW(out_rec0);

    END LOOP;

    PIPE ROW(out_rec0);

    CLOSE cur0;

    RETURN;
    END GET_SUPPLIERS_TEST;

    The query run in PLSQL the following result attached:

    select itm_id , itm_name

    , csrdba.get_suppliers_test( itm_id ) colFields

    from ref_item where itm_id IN ( 151769, 152085 )



    I want to know if it is possible to map the result of the function , to have the supplierVO with Id and Name mapped to the suppliers list.
    I tried to use columnIndex and resultMap as attribute to the result tag.
    I will appreciate it if someone could help.

    Regards.

    Et merci encore RBARAER DE VOTRE AIDE.
    Attached Thumbnails Attached Thumbnails q1.bmp   q2.bmp  
    Last edited by demar_quiz; 07-22-08 at 15:47.

  6. #6
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Sorry for the post in french , I'm switching between english-speaking and french-speaking forums and there I didn't make the appropriate switch

    I didn't know that calling the pipelined function in the SELECT clause worked... It seems like it returns the entire collection.

    Did you try "IND_TAB_SUPPLIER_TABLE" as type for ColFileds since it is the type returned by the pipelined function ? Maybe you will need to put the schema of the owner before (or maybe a synonym would work, not sure with such APIs, in PHP for example it does not when mapping collections) : "OWNER.IND_TAB_SUPPLIER_TABLE", replace OWNER with the appropriate schema. Keep upper case here because ibatis will certainly go to the database and get metadata of the table and object types... Well I'm just guessing here since I'm not an Ibatis expert at all (never used it )...

    HTH
    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
  •