Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2003
    Location
    Southfield, MI, USA
    Posts
    5

    Unanswered: Need to multiselect from a dynamically generated view name

    Hi,

    I need to select multiple rows from a dynamically generated view. The view name is dynamically generated (a session_id is appended to the end of the view name, e.g. myview_12345) and the number of columns and their definitions change depending on the report type selected.

    I have explored using native dynamic SQL but ran into the problem of not being able to define the cursor because the view definition is unknown at runtime. So I cannot do something like

    report_rec myview_12345%TYPE;

    since I do not know what the '12345' is going to be.

    Here is some code I was working on:
    #################
    PROCEDURE open_report (
    p_session_id_in IN session_table.session_id%TYPE,
    p_creating_user_in IN session_table.creating_user%TYPE
    )

    IS

    v_view_name VARCHAR2(64);
    v_report_view VARCHAR2(64);
    sql_stmt VARCHAR2(1000);

    -- How do I get this:
    report_rec myview_12345%ROWTYPE;
    --where 12345 is the session_id

    BEGIN
    sql_stmt := 'SELECT * FROM '||'eaicl_v_report_' || p_session_id_in;
    DBMS_OUTPUT.PUT_LINE(sql_stmt);

    EXECUTE IMMEDIATE sql_stmt INTO report_rec;

    -- Process the cursor here or return cursor to a java vector

    session_package.close( p_session_id_in, p_creating_user_in);
    END;
    ###################

    Should I be using the dbms_sql package instead to describe the dynamic view and then parse it for the column names to create a temp table to select into? looking for ward to your suggestions.

    I hope that makes some sense. Thanks for you time and effort.

  2. #2
    Join Date
    Sep 2003
    Location
    Colombo, Sri Lanka
    Posts
    63
    Instead of the view name in the FROM clause use the select that is used to make the view, and make it an inline view. By doing so you can use dynamic sql and get the results. The select will be something like

    SELECT *
    FROM (SELECT ......)


    Cheers,
    Suren.

  3. #3
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    is the view definition always the same ?
    If so, you can create a standardview and reference that one in the procedure.
    Otherwise try using dynamic pl/sql

    Hope this helps
    Edwin van Hattem
    OCP DBA / System analyst

  4. #4
    Join Date
    Jun 2003
    Location
    Southfield, MI, USA
    Posts
    5
    Evan,

    The view definition changes with the report selected. So report_id 1 may generate a view with 4 columns (char, number, varchar2, number) and report_id 2 could have 3 columns (char, char, number) and so on. The view is created through an API and I do not have access to the source code. Once the view is generated though, I can look at the source through TOAD and perhaps run through all the report possibilities and build the definitions. Currently there are 11 reports.

    Suren,

    Could you please elaborate on your suggestion a little. I'm not quite sure I understand.

    Thanks,
    -Sri

  5. #5
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    try using dynamic PL/SQL. In that manner you will be able to build the code dynamicly, even the declaration, which in your case is the problem.
    Edwin van Hattem
    OCP DBA / System analyst

  6. #6
    Join Date
    Jun 2003
    Location
    Southfield, MI, USA
    Posts
    5
    Thanks Edwin. I've already figured out that I need to use Dynamic SQL but I'm not sure how to get around the fact that the view name and definition are unknown until runtime. Specifilcally I need to know how to get around this problem:

    -- How do I get this:
    report_rec myview_12345%ROWTYPE;
    --where 12345 is the session_id and is unknown until runtime.

  7. #7
    Join Date
    Sep 2003
    Location
    Colombo, Sri Lanka
    Posts
    63
    In the select statement you have written you have put the view name in the FROM clause (FROM '||'eaicl_v_report_' || p_session_id_in).

    Instead of that you can put the select statement you are using to create the view.

    If the select for the view is "SELECT emp_no, name FROM employees" when build your report select to be

    SELECT * FROM (SELECT emp_no, name FROM employees)

    As I said this builds an inline view when the select is executed instead of a static view.

    I hope this solves your requirement.

    Suren.


    Originally posted by tablespace
    Evan,

    The view definition changes with the report selected. So report_id 1 may generate a view with 4 columns (char, number, varchar2, number) and report_id 2 could have 3 columns (char, char, number) and so on. The view is created through an API and I do not have access to the source code. Once the view is generated though, I can look at the source through TOAD and perhaps run through all the report possibilities and build the definitions. Currently there are 11 reports.

    Suren,

    Could you please elaborate on your suggestion a little. I'm not quite sure I understand.

    Thanks,
    -Sri

  8. #8
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    using dynamic PL/SQL instead of dynamic SQL, use cacn create an anonoymous PL/SQL block this this :

    begin
    lv_viewname := myview||personal_id||'%ROWTYPE';
    lv_start := 'DECLARE lr_rec '|| lv_viewname;
    lv_begin := 'BEGIN '||your_code_here||' END;'||CHR(10);
    lv_total_string := lv_start||lv_begin;
    execute immediate lv_total_string etc......
    end;

    good luck.
    Edwin van Hattem
    OCP DBA / System analyst

Posting Permissions

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