Forum Stats

  • 3,853,602 Users
  • 2,264,244 Discussions
  • 7,905,405 Comments

Discussions

inserting a row into table over db_link

782530
782530 Member Posts: 122
edited Oct 5, 2010 5:32AM in APEX Discussions
Hi all,

Is it possible to insert rows (one at a time) on to a remote database over a db_link from an apex form page?

if so how can i go about doing this?

I have tried creating a trigger, and a On-Submit Page Process (pl/sql) but cant seem to get either working..

Any help will be greatly appreciated!

Thanks,

Ashleigh
Tagged:
«1

Answers

  • 773431
    773431 Member Posts: 298
    Ashleigh,

    I think you may be making things harder on yourself than you need to.

    Once you create a DB Link, you can treat the remote tables (almost) as if they were local. (Be careful with large cross-database joins, however--Oracle's not always great at figuring out which database should be the master in that sort of situation.) I've found that a simple way to deal with remote tables in APEX is to create wrapper views:
    create or replace view table_a as select * from [email protected]_db;
    Once you do this, you can see the view (and its data) in the object browser and use it as the base of reports and forms--including tabular forms, which I think may be what you're wanting to do. You shouldn't need to worry about whether or not the data is on the remote database.

    -David

    (Note: this assumes, of course, that the account used by the database link has sufficient privileges on the remote database.)
  • 782530
    782530 Member Posts: 122
    edited Sep 30, 2010 10:00AM
    Hi David,

    thanks for your reply, im not sure this is what i want to do.. i can already select from the remote database over the db_link, i just want to be able to add a row to the table using a form on apex.

    when creating a form on a view on apex, it likes to complicate things by making sure there is a PK, in this instance there isnt really a PK, only a FK. (which i want to be able to create mutiple rows in this table... for instance: the FK table_ID '5' may have 10 rows.. )

    maybe im not creating my triggers properly? (im not that familiar with them really)

    Thanks again,

    Ashleigh

    Edited by: crewcab88 on Sep 30, 2010 3:00 PM - spelling
  • 773431
    773431 Member Posts: 298
    You can get around the PK restraints a couple of ways, but the simplest is probably something like:
     create or replace view table_a as select ta.rowid pk_col, ta.* from [email protected]_db ta
    Then, when APEX asks you for the PK column, point it at the pk_col of the view, and tell it that it uses an existing trigger. This should satisfy APEX's requirements.

    Note: rowid's are mostly constant, but not entirely constant. For general APEX use (identifying rows for update or delete), however, they should be constant enough.

    -David
  • 782530
    782530 Member Posts: 122
    I am still not able to insert into the remote table.. :(
  • 782530
    782530 Member Posts: 122
    can anyone enlighten me on this?

    is there any way i can write a page process (pl/sql?) to do this?

    Thanks
  • 773431
    773431 Member Posts: 298
    Ashleigh,

    What behavior are you getting? Is it throwing an error? If so, what's the message?

    -David
  • Joel_C
    Joel_C Member Posts: 467
    When you say that you still can't insert into the remote table, can you possibly elaborate?

    Did you implement the view as per the previous poster's suggestion? If so, I would think you would need to create an additional 'instead of' trigger for the view (by default, you can't insert, update or delete to/from a view). Triggers might seem scary at first, but they are pretty straightforward to understand, once you are aware of the potential pitfalls of using them.

    See the following link for an example from Oracle Documentation (10g):

    http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_7004.htm#i2064426

    In addition, have you set up the appropriate privileges on your DB link? You may only have select privileges for example.

    If none of the above applies, how does the issue manifest itself? Do you encounter an error message? What behaviour do you witness that makes you think it hasn't worked? The more information you can provide, the more likely it is that someone will divine the solution.
  • 773431
    773431 Member Posts: 298
    797874 wrote:
    (by default, you can't insert, update or delete to/from a view).
    Actually, if the view is of a single table, you can* do inserts, updates, and deletes without creating triggers.

    If you're wondering why you'd want a view of a single table, consider the possibility of reducing the number of columns visible to the user. Or hiding the fact that it's across a database link (as in the current case).

    -David

    * Ok, this generalization isn't always true, but few are. If the view manipulates the data of the underlying table, for instance, you may need to build triggers to "back out" the manipulations.
  • 782530
    782530 Member Posts: 122
    ive tried creating triggers but to no avail,

    i just tried to create a Form and Report page on the view that selects from the remote table (with the added pk column in the view) using the wizard

    wont let me create it, errors:

    ORA-20001: Unable to create query and update page. ORA-20001: Unable to create query and update page. ORA-00936: missing expression

    Yes it does display ORA-20001 error twice :S
  • 782530
    782530 Member Posts: 122
    edited Oct 1, 2010 9:24AM
    and thats only because of the where clause i added which passes values to the page items.
    once removed i can create it and then when i run it, it says:

    ORA-04098: trigger trigger_name is invalid and failed re-validation

    Trigger: (and ive tried many different triggers but as stated before i am not upto scratch with them...)
    CREATE OR REPLACE TRIGGER  "TRIGGER_NAME" 
    instead of insert on VIEW_NAME
    for each row
    
    begin
    
     :new.my_id := p14_my_id;
     :new.my_prog := p_14_myl_prog;
     :new.my_date := SYSDATE;
    END;
    Edited by: crewcab88 on Oct 1, 2010 2:21 PM
This discussion has been closed.