0 Replies Latest reply on Mar 14, 2014 10:40 PM by user2037909

    apex button dynamic action ORA-01461 LONG, best practice for a work around?

    user2037909

      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,

      ID_2,

      ID_3,

      ID_4,

      ALERT_TYPE,

      TAG,

      DATE_CREATED)

      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,

      case

      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?

       

      Thanks for any help.