Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2005
    Posts
    6

    Unanswered: ROW-based function call from view ?

    Hi,

    I have a view which is built like this

    CREATE OR REPLACE VIEW foo AS
    SELECT a.*, b.*
    FROM someView1 a
    , someView2 b
    WHERE (join etc)

    I need do do some procedural plausibility checks on A, so I'd like to pass the entire Row to a PL/SQL-Function and return a -1/0-Flag which symbolize plausibility of data (means another column in view foo calling the function an pass the row)

    How can I pass the entire a row to a function and which datatype does the function need to have ? I tried with rowtype, but i cannot define cursors in views (ora 8.1.7).

    Can anybody help ?

    Thanks a Lot ! I am quite flabbergasted

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You can just pass the individual column values as parameters. The return type of the function will be INTEGER. Assuming the function is deterministic, you could create a function-based index to speed up queries on the view.

    If you don't like the idea of passing each column value as a separate parameter then you will have to pass just the primary key (or ROWID) instead - but that will require an extra SELECT in the function, which will impact performance, and the function will no longer be deterministic so you can't index it.

Posting Permissions

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