| Please help me! [message #81762] |
Tue, 18 March 2003 10:13  |
SA
Messages: 37
Registered: March 2002
|
Member |
|
|
I’m so confused in dealing the following situation and I hope someone can help me. I need to update and insert a record. First, I display the related info; then when user chooses a record, and click a button, I need to create a new record in the same line (blind out the old info and put some new info in some fields and let user input in some field, then when user hit the save button, the old record is updated and a new record inserted. I have all these logic done. But my problem now is if I use commit-form built-in, it will look at all records in the form and go to on-insert and try to execute the code in on-insert for each record. But I only want to deal with one record. So instead of using commit-form, I created a procedure, it seems work, but it didn’t really do commit. So when my session is gone, the new info is not saved. If I put “commit” in my code, it'll go thru the code triggered by the button pressed, but when it comes to commit, it will trigger on-insert in the data block (I put null; there). Then go back to the code triggered by the button pressed. I don't know why. How this commit work? Even I put some condition to prevent execute the code twice, I still got "frm-40400: transaction complete: n records applied and saved", where n is number of records displayed on my form. Is there any other way to do my task? Please give me some advice. Thanks,
Sa
|
|
|
|
| Re: Please help me! [message #81765 is a reply to message #81762] |
Tue, 18 March 2003 13:01  |
magnetic
Messages: 324
Registered: January 2003
|
Senior Member |
|
|
you should work with DBMS_SQL [[maybe also EXECUTE_IMMEDIATE]]
try next code in your trigger:
v_sql varchar2(300):='update table set col1='||:field1||',col2='||:field2
where...
...
...
commit;';
-------
create a database procedure like
PROCEDURE do_sql (p_statement in varchar2) IS
BEGIN
declare
c integer;
c_exec integer;
l_dummy date;
begin
c:=dbms_sql.open_cursor;
dbms_sql.parse(c,p_statement,dbms_sql.native);
c_exec:=dbms_sql.execute(c);
--commit;-- possible that commit should be done here, just try out
end;
END;
now back to your form trigger:
v_sql is filled in,next command should be
do_sql(v_string);
|
|
|
|
|
|
| Re: Please help me! [message #81781 is a reply to message #81765] |
Thu, 20 March 2003 03:03 |
magnetic
Messages: 324
Registered: January 2003
|
Senior Member |
|
|
one of the goals of using dynamic sql is to define a statement as a string and execute this statement at the database. that means the procedure [[i called it do_sql?]] must be created as a database procedure and not a forms programunit!
In your case you dont want to commit in forms, so this is the solution for you.
examine package sys.dbms_sql
be sure that a that you have the grant to execute sys.dbms_sql package
|
|
|
|
| Re: Please help me! [message #81809 is a reply to message #81765] |
Mon, 24 March 2003 08:40 |
SA
Messages: 37
Registered: March 2002
|
Member |
|
|
Thank you very much for your help. As a newbie, I don't know where to find the package. can you tell me where I can find that package(sys.dbms_sql package)? Is it in database or in form? How do I set up? Thanks,
Sa
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
