Prototyping an apex solution for a friend, using a free apex workspace...
Application Express 4.2.4.00.08
I would like to run this insert statement below as a button click on a form... the statement runs as expected in sqlworkshop/sqlcommands...
insert into resid_alerts ( ID_1,
select CUSTOMER_PRODUCTS.CUSTOMER_ID as ID_1,
CUSTOMER_PRODUCTS.PRODUCT_ID as ID_2,
CUSTOMER_PRODUCTS.CREATE_ACTIVITY_PERIOD as ID_3,
CUSTOMER_PRODUCTS.LAST_ACTIVITY_PERIOD as ID_4,
'CUSTOMER_PRODUCTS' as ALERT_TYPE,
when CUSTOMER_PRODUCTS_LOAD_VIEW.DISCOUNT_APPLY_TYPE <> CUSTOMER_PRODUCTS.DISCOUNT_APPLY_TYPE THEN 'DISCOUNT_APPLY_TYPE'
end ||' '||...
The entire statement is too long to store as a dynamic action (ORA-01461: can bind a LONG value only for insert into a LONG column)...
I've tried storing as a database procedure as scripted with "create or replace procedure create_rate_change_alerts is"... I believe the script is ok as the object browser shows "This object has no errors.", yet I am unable to execute the script from sqlcommands with execute create_rate_change_alerts as I receive "ORA-00900: invalid SQL statement"...
I'm not finding much on procedures or functions that don't return a value... First question, is it bad to have a procedure or function that doesn't return a value but simply inserts or manipulates data? Second question, what is the proper calling method for a database procedure from sqlworkshop/sqlcommands? Third question, what is the proper calling method for a database procedure for a button click/dynamic action?