Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Mar 2006
    Posts
    47

    Unanswered: function in query where clause.. possible to pass whole row?

    All,

    We've done some testing to establish that we can do the following

    /* shorthand */

    function func1(val){
    if val == 'bob' then
    return 'ok';
    end if;
    }

    SELECT a,b,c,d,e,f,g,h FROM table WHERE func1(a)='ok';


    My question is, is it possible to pass a reference to that function that contains a...h (i.e. the entire row) without explicitly defining them?

    I could do "SELECT a,b,c,d,e,f,g,h FROM table WHERE func1(a,b,c,d,e,f,g,h)='ok'"

    but I'd prefer "SELECT a,b,c,d,e,f,g,h FROM table WHERE func1(*)='ok'"

    Thanks in advance,
    --james

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    Pass in the table name to the function, and then code the function to query the system tables for a list of columns, and then dynamically test your condition.

    Since you'd be performing SQL against ALL_TAB_COLS in the function, you wouldn't want to use an approach like this if your main query were returning a ton of rows. Oracle has to run those SQL stmts once per row returned in the main query, which would become expensive in the wrong situation.

    -Chuck

  3. #3
    Join Date
    Mar 2006
    Posts
    47

    Thanks

    Yeah, the table I'm querying is going to havea bunch of rows in it. Basically what we're trying to achieve is to filter certain rows out based on a table defined set of permissions for a user. I just read an article about a PIPELINED function, and the example they used was exactly what we're trying to accomplish. I'm going to look into that and see if it will work for our needs.

    --james

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    The PIPELINED construct returns back data from the function in real-time, instead of waiting for the fucntion to finish completely, so it shouldn't help you in this situation.

    There is a way to pass an entire row into a function or procedure, if you look into ROWTYPE, but that will only work in a PL/SQL context. Since you're working in an SQL context, I don't think it will work for you.

    Post your solution if you find it to work for you, as I'm definitely still realtively new to Oracle, and am continually surprised by the misinformation that I've got in my head.

    -Chuck

    Code:
    SQL> declare
      2    therow na_division%ROWTYPE;
      3  begin
      4    select *
      5    into therow
      6    from na_division where division_code = '08';
      7    
      8    dbms_output.put_line(therow.division_code||', '||therow.division_description);
      9    
     10  end;
     11  /
    
    08, Information Management Division

  5. #5
    Join Date
    Mar 2006
    Posts
    47

    hmmm

    This is what we're trying to accomlish. (simplified for brevity of course)

    We have a table:

    PRODUCTS(prodnum NUMBER,name VARCHAR2(50),location NUMBER)

    We have a user list:
    USERS(userid NUMBER,name VARCHAR2(50))

    Finally we have a "permission list" of what products that user can see (basically can only see products in their location, or if someone has explicitly granted them permission).

    PERMS(userid NUMBER,prodnum NUMBER)

    Data:

    Products
    1 Shirt
    2 Shoes
    3 Socks

    USERS
    1 Me
    2 You
    3 Them


    Perms
    1 1
    1 2
    1 3
    2 2
    3 1
    3 3


    My first inclination was to handle it like this:

    SELECT * FROM PRODUCTS WHERE myFunction(userid,prodnum)='someValue'

    For each row, it would execute a function that would check the PERMS table, and see if they are able to view that product.

    From what I understand about PIPELINED functions, it basically allows you to query a function like a table.

    For instance, we could make a view for each user, consisting of the products table joined with the perms table. PIPELINING, basically allows us to kind of simulate a view, just with on the fly processing.

    At least I think thats how it suppsoed to work. Is there a better way?


    --james

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You could do:

    SELECT a,b,c,d,e,f,g,h FROM table WHERE func1(ROWID)='ok';

    The function would select the row using the ROWID to get the values it needed to check.

    Whichever way you do it this won't perform terribly well on a largish table, because calling functions is slow, and here there will be a function call for every row in the table. Why not just use a view? :-

    create view v as
    select a,b,c,d,e,f,g,h
    from table
    where a = 'bob'
    and b < c
    and d between e and f;

  7. #7
    Join Date
    Mar 2006
    Posts
    47

    yeah

    Quote Originally Posted by andrewst
    You could do:

    SELECT a,b,c,d,e,f,g,h FROM table WHERE func1(ROWID)='ok';

    The function would select the row using the ROWID to get the values it needed to check.

    Whichever way you do it this won't perform terribly well on a largish table, because calling functions is slow, and here there will be a function call for every row in the table. Why not just use a view? :-

    create view v as
    select a,b,c,d,e,f,g,h
    from table
    where a = 'bob'
    and b < c
    and d between e and f;

    I hate the performance hit we're going to take, but I don't see a way around it. We use views for several things, but in this case. We have several hundred users, and ~ 100,000 products. The permission table is dynamic in the sense that an admin user (i.e. non DBA or really even admin quality) can generate permissions based upon any field in the product table. For example, user JOE1, has been given the VIEW permission for any product where location=3 OR location=16 AND prodnum is between 1 and 12000 (use your imagination on what someone might come up with). And these changes need to be reflected in real time. Also the user creation is handled by them. If there is someway to create a dynamic view, that would be awesome, but I haven't seen anyway to do that. (Basically, a view based off PL/SQL or something)

    Thanks for the input thought, thats definitely an option and answers my first question.

    --james

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I presume you must have considered using Fine-Grained Access Control? It does what you are trying to do, but a lot more slickly than you can using functions or views.

  9. #9
    Join Date
    Mar 2006
    Posts
    47

    I was under the impression

    I was under the impression that that type of Oracle access controlled approach was reliant upon the oracle useres.

    In my case this is a web application, and the same Oracle user is being used for all access to the database. (So for the connection string web_user1 and web_user2 are both signing into oracle with web@my_instance).

    Is this not the case?

    --james

  10. #10
    Join Date
    Dec 2003
    Posts
    1,074
    What's wrong with performing this all within the same query, then? You're going to have to pass in the username somehow, so perhaps something like

    Code:
    select * 
    from products
    where exists (select 1
                  from   users, perms
                  where  users.userid = perms.userid and
                         perms.prodnum = products.prodnum and
                         users.name = parm_USERNAME)
    -cf

  11. #11
    Join Date
    Mar 2006
    Posts
    47

    problems with same query

    There are a couple of issues with using one query.

    A major annoyance is that this is a rewrite of an application already in place, so a simple change will be the most acceptable. (i.e. being able to grep for "product_table" and replacing it with "product_function").

    Secondly, I made the example simple so as not to get to in depth. In reality we've got 6 or so tables dedicated to join certain roles to users, and joining certain filters to each role. basically it allow N number of predicates to be added to any role.

    Also, this same premission structure is needed for 5 more tables. (ie, for eaech table, blah can be constrained for blah user).


    In some of our queries, we have these monster joins. In those cases I'd have to rewrite the query to include the subquery in there. while not difficult, very tedious. If I could just stick a function/pipelined table in place of the table name, it would simplify things greatly (especially when joining two constrained tables).

    For all intents and purposes, we're storing a database-ilized version of a select query (like a view), but we have to dynamically build the view before each query. If i made regualr views for each pairing of product(or whatever) and user i'd be at this for weeks, and even then the minute a new user is added, i have to do it again.

    I made a simple test case with the PIPELINED methodolgy. It works as I intended it to. My only concern now is the impact on speed and the load this will put on the server.

  12. #12
    Join Date
    Dec 2003
    Posts
    1,074
    "I made a simple test case with the PIPELINED methodolgy."

    What's it look like?
    -cf

  13. #13
    Join Date
    Dec 2003
    Posts
    1,074
    Could you search and replace on the table name, inserting an inline view, instead of your pipelined table?
    -cf

  14. #14
    Join Date
    Mar 2006
    Posts
    47
    PHP Code:
    /*Set up framework for pipelined info*/
    CREATE TYPE PRODUCT_ITEM_FILT AS OBJECT
    (  PRODNUM                NUMBER(22,0)  NULL,
       
    USERPERM VARCHAR2(20NULL
    );
    /

    CREATE TYPE PRODUCT_ITEM_FILT_TAB AS TABLE OF PRODUCT_ITEM_FILT;

    FUNCTION 
    productFilter (tablename VARCHAR2,USER_id NUMBER) RETURN PRODUCT_ITEM_FILT_TAB  PIPELINED;



    FUNCTION 
    productFilter RETURN PRODUCT_ITEM_FILT_TAB PIPELINED IS
    CURSOR PI_cur
    IS
    SELECT PRODNUM from PRODUCT_ITEM WHERE prodnum IN 
    (1,2,3);
    PI_row PI_cur%ROWTYPE;
    BEGIN

    IF NOT PI_cur%ISOPEN
    THEN
       OPEN PI_cur
    ;
    END IF;
    LOOP

    --get individual row
    FETCH PI_cur INTO PI_row
    ;
    EXIT 
    WHEN PI_cur%NOTFOUND;
    PIPE ROW (PRODUCT_ITEM_FILT.PRODNUM,'somePermission'));
    END LOOP;
    CLOSE PI_cur;
        RETURN;
    END productFilter

    Then I'm querying with something like...

    PHP Code:
    SELECT FROM TABLE(productFilter('PRODUCT_ITEM',1)) 
    Thats paraphrased of course, but the premise is the same.

    Instead of hardcoding the query in, I'll dynamically generate it, and it will return a table...Well it will return an object that can be queried like a table.

    It was mostly to prove I could actually query a function like that.

    --james

  15. #15
    Join Date
    Mar 2006
    Posts
    47
    Quote Originally Posted by chuck_forbes
    Could you search and replace on the table name, inserting an inline view, instead of your pipelined table?
    -cf
    I could grep for that, but I mentioned the problem with static view's earlier.

    I'm looking up the specifics of an inline view now. If it's possible to pass a value to a view (or some way to control the dynamic specifics of the view, then that would be a viable option).

    Is there a way to do that?

    Thanks for the help by the way.

    --james

Posting Permissions

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