Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2004
    Posts
    3

    Unanswered: Dynamic sql, pl/sql functions and index-by tables.

    Long time reader, first time poster


    I need to execute a function (dynamically derived at runtime) which returns a pl/sql table, a la:

    DECLARE

    ef_cur BINARY_INTEGER;
    ef_run_str VARCHAR2(2000) := 'BEGIN :gusr_tab := '||rf_rec.exc_func||';';
    ef_feedback INTEGER;

    gusr_tab DBMS_SQL.NUMBER_TABLE;

    BEGIN

    ef_cur := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(ef_cur, ef_run_str, DBMS_SQL.NATIVE);
    DBMS_SQL.BIND_ARRAY(ef_cur, 'gusr_tab', DBMS_SQL.NUMBER_TABLE);
    ef_feedback := DBMS_SQL.EXECUTE (ef_cur);
    DBMS_SQL.CLOSE_CURSOR (ef_cur);

    END;



    At compile time, I get:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    268/13 PL/SQL: Statement ignored
    268/53 PLS-00330: invalid use of type name or subtype name

    which relates to line:

    DBMS_SQL.BIND_ARRAY(ef_cur, 'gusr_tab', DBMS_SQL.NUMBER_TABLE);




    Any ideas?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    The third parameter to that procedure should be the "value" not the datatype of the value, e.g.:

    DBMS_SQL.BIND_ARRAY(ef_cur, 'gusr_tab', gusr_tab);

  3. #3
    Join Date
    Sep 2004
    Posts
    3
    You, my friend, are an absolute diamond

  4. #4
    Join Date
    Aug 2001
    Posts
    66

    Exclamation

    Unfortunately (speaking from bitter experience) that is not what DBMS_SQL.BIND_ARRAY is for. Rather it is for binding the individual elements of an array one at a time to repeated executions of a dynamic SQL or PL/SQL statement (similar to FORALL statement in PL/SQL).

    In other words (see comments in code)...
    Code:
    Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
    With the Partitioning, OLAP and Data Mining options
    
    SQL> SET SERVEROUTPUT ON;
    SQL> -- this dynamic example...
    SQL> DECLARE 
      2    v_cur INTEGER := DBMS_SQL.OPEN_CURSOR;
      3    v_arr DBMS_SQL.NUMBER_TABLE;
      4    v_exe INTEGER := 0;
      5  BEGIN
      6    FOR i IN 1..5 LOOP
      7      v_arr (i) := i;
      8    END LOOP;
      9    
     10    DBMS_SQL.PARSE (v_cur, 
     11      'BEGIN ' || 
     12      '  DBMS_OUTPUT.PUT_LINE (:1); ' ||
     13      'END;', DBMS_SQL.NATIVE);
     14    DBMS_SQL.BIND_ARRAY (v_cur, ':1', v_arr);
     15    v_exe := DBMS_SQL.EXECUTE (v_cur);
     16  END;
     17  /
    1
    2
    3
    4
    5
    
    PL/SQL procedure successfully completed.
    
    SQL> -- is equivalent to this native dynamic example...
    SQL> DECLARE
      2    v_arr DBMS_SQL.NUMBER_TABLE;
      3  BEGIN
      4    FOR i IN 1..5 LOOP
      5      v_arr (i) := i;
      6    END LOOP;
      7    
      8    FORALL i IN v_arr.FIRST .. v_arr.LAST
      9      EXECUTE IMMEDIATE 
     10        'BEGIN ' ||
     11        '  DBMS_OUTPUT.PUT_LINE (:1); ' ||
     12        'END;' 
     13        USING v_arr (i);
     14  END;
     15  /
    1
    2
    3
    4
    5
    
    PL/SQL procedure successfully completed.
    
    SQL> -- or perhaps this static example...
    SQL> DECLARE
      2    v_arr DBMS_SQL.NUMBER_TABLE;
      3  BEGIN
      4    FOR i IN 1..5 LOOP
      5      v_arr (i) := i;
      6    END LOOP;
      7    
      8    FOR i IN v_arr.FIRST .. v_arr.LAST LOOP
      9      DBMS_OUTPUT.PUT_LINE (v_arr (i));
     10    END LOOP;
     11  END;
     12  /
    1
    2
    3
    4
    5
    
    PL/SQL procedure successfully completed.
    
    SQL> -- but *NOT* this static example...
    SQL> DECLARE
      2    v_arr DBMS_SQL.NUMBER_TABLE;
      3  BEGIN
      4    FOR i IN 1..5 LOOP
      5      v_arr (i) := i;
      6    END LOOP;
      7    
      8    DBMS_OUTPUT.PUT_LINE (v_arr);
      9  END;
     10  /
      DBMS_OUTPUT.PUT_LINE (v_arr);
      *
    ERROR at line 8:
    ORA-06550: line 8, column 3:
    PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'
    ORA-06550: line 8, column 3:
    PL/SQL: Statement ignored
    
    
    SQL>
    Padderz
    SYSOP, Quest Software / RevealNet Labs PL/SQL Pipeline

  5. #5
    Join Date
    Sep 2004
    Posts
    3
    Oooooookay, so that's why I get:

    PLS-00382: expression is of wrong type

    when executing:

    CREATE OR REPLACE PROCEDURE test_func
    IS
    ef_cur BINARY_INTEGER;
    ef_run_str VARCHAR2(2000);
    ef_feedback INTEGER;

    gusr_tab DBMS_SQL.NUMBER_TABLE;
    gusr_id NUMBER;

    BEGIN

    ef_run_str := 'BEGIN :gusr_tab := gwf_pkg.ef_pat(30150); end;';
    ef_cur := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(ef_cur, ef_run_str, DBMS_SQL.NATIVE);
    DBMS_SQL.BIND_ARRAY(ef_cur, 'gusr_tab', gusr_tab);
    ef_feedback := DBMS_SQL.EXECUTE (ef_cur);
    DBMS_SQL.CLOSE_CURSOR (ef_cur);

    END;

    ????



    Why is it not possible to receive an index-by table back from a dynamically derived function? I don't believe it's not possible!

  6. #6
    Join Date
    Aug 2001
    Posts
    66
    > I don't believe it's not possible!

    Believe it. Dynamic SQL (whether DBMS_SQL or NDS flavour) supports binding of SQL datatypes only. So no binding of booleans, record types or associative arrays (latest name for PL/SQL tables / INDEX-BY tables) and this can be a big problem when writing dynamic PL/SQL as you are finding out.

    Nevertheless SQL does have its own collection types and these can be bound to dynamic SQL (NDS flavour only). In the first instance I would suggest you create a collection type similar to your index-by array and convert your function to populate and return that type instead, e.g.
    Code:
    Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
    With the Partitioning, OLAP and Data Mining options
    
    SQL> CREATE OR REPLACE TYPE collection_type_name AS TABLE OF NUMBER;
      2  /
    
    Type created.
    
    SQL> CREATE OR REPLACE FUNCTION function_name
      2    RETURN collection_type_name
      3  IS
      4  BEGIN
      5    RETURN collection_type_name (1, 2, 3, 4, 5);
      6  END;
      7  /
    
    Function created.
    
    SQL> SET SERVEROUTPUT ON;
    SQL> DECLARE
      2    function_name VARCHAR2 (30) := 'FUNCTION_NAME';
      3    collection_type collection_type_name;
      4  BEGIN 
      5    EXECUTE IMMEDIATE 
      6      'BEGIN :1 := ' || function_name || '; END;' 
      7      USING OUT collection_type;
      8    
      9    FOR i IN 1..collection_type.COUNT LOOP
     10      DBMS_OUTPUT.PUT_LINE (collection_type (i));
     11    END LOOP; 
     12  END;
     13  /
    1
    2
    3
    4
    5
    
    PL/SQL procedure successfully completed.
    
    SQL>
    If you cannot change the return type of the function you could create a wrapper for the function that converts the return type into a type you can bind you dynamic SQL. Failing that you could declare a package global variable for the collection type and not bind it at all, just assign the function's return value to the global variable within the dynamic PL/SQL block. Failing that you could write the entire processing of the function call and consumption of the return type in a dynamic PL/SQL block.

    Does this help?
    Padderz
    SYSOP, Quest Software / RevealNet Labs PL/SQL Pipeline

Posting Permissions

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