Oracle Forms

Hi,

I'm new to PL/SQL coding and am currently working on some application development in Oracle Forms via the 10g Developer's Suite.

I'm having a problem getting a rowcount (# of rows affected by last dml command) to register after using forms_ddl

PROCEDURE P_SQL_UPDATE IS

  L_string1 			VARCHAR2(250);
  L_string2 			VARCHAR2(250);
  L_string3 			VARCHAR2(250);
  L_string4 			VARCHAR2(250);
  L_rowcount 			Pls_integer;
  L_count   			NUMBER;
  L_alert  			 	NUMBER;

BEGIN

  L_string1 := 'UPDATE ' || :global.G_table_nam || ' SET ';
  L_string2 := NULL;
  L_string3 := ' WHERE ';
  L_string4 := NULL;

...
... building strings 2 and 3
...

:global.final_sql := L_string1 || L_string2 || L_string3 || L_string4;

  FORMS_DDL(:global.final_sql);
  L_rowcount := SQL%ROWCOUNT;

  if FORM_SUCCESS then
    set_alert_property('ALT_COMMIT_ROLLBACK',ALERT_MESSAGE_TEXT, 'The # of rows affected: ' || L_rowcount || CHR(10) || 'Commit or Rollback?');
    L_alert := show_alert('ALT_COMMIT_ROLLBACK');
    if L_alert = ALERT_BUTTON1 then
      commit;
    else
    	rollback;
    end if;
  else
    set_alert_property('ALT_INFO',ALERT_MESSAGE_TEXT, 'Error: ' || DBMS_ERROR_CODE  || ' - ' ||DBMS_ERROR_TEXT);
    L_alert := show_alert('ALT_INFO');
  end if;
   
END;

I've tried replacing the global variable with a hardcoded string of 1 potential SQL, I've changed L_rowcount to numeric, tried to directly display SQL%rowcount via dbms_output.put_line ...L_rowcount just comes up empty during the Alert Pop-up.

Another alternative was L_rowcount := DBMS_SQL.LAST_ROW_COUNT; ... This gives a slightly different result in the Alert Window, instead of being blank it returns 0. So something is definitely not acting right. Other than doing this, it always returned null/a blank field on the alert pop-up.

I've run tests on SQL%rowcount in the TOAD editor and got results, but when moving over to the actual form L_rowcount is always null after running the procedure (with exception of being 0 using the dbms_sql.last_row_count).


I should note that the change isn't committed without the extra commit in that FORM_SUCCESS at the end...I'd read that Forms_DDL is an implicit commit and that it will commit the SQL without anything else happening...this seemed a bit odd as well.

Before forms_ddl I attempted to use execute, exec, or execute immediate on the SQL but to no avail. I'm thinking another option could be to pass this SQL to an entire new datablock and use execute query on that block? Not sure how to go about that though.



The goal of this form is for users to enter values in a generic form and upon meeting validation in the field triggers, they will press submit and ideally before the update is committed I want users to be notified of the # of rows that would be affected by their proposed change....so if the # is huge they'll know they did something wrong before they affect the entire database.


Any input would be appreciated,
Travis