This discussion is archived
1 2 Previous Next 26 Replies Latest reply: Jul 11, 2013 5:22 AM by user11986871 RSS

can't copy record that contains "#" using branching

gmac Newbie
Currently Being Moderated
Greetings Everyone - I'm using APEX 4.1 and have implemented a "Copy Record" button that copies selected fields from the record back to the same page to create a new record. This is done using branching (got the idea from this forum!) and setting the items (e.g., P2_NAME,P2_ADDRESS1,P2_ADDRESS2, etc...) with these values (e.g., &P2_NAME.,&P2_ADDRESS1.,&P2_ADDRESS2., etc...)

It has worked perfectly in other APEX applications -- up until now. The issue is that the new database contains several fields that contain the "#" character. When the "#" character is encountered during the copying process, any data after the # are not copied (but all of the data before the # are copied OK).

Is there a way to get around this? Removing the "#" characters is not an option, as it is part of numerous manufacturer part numbers that we track.

Any guidance is appreciated. Thanks in advance for your help - Gail
  • 1. Re: can't copy record that contains "#" using branching
    Bob37 Pro
    Currently Being Moderated
    I would have it create a record in an Apex collection after submit, then read the collection record back into your page items (and delete the collection/record) in a before header process - when the collection record exists. You can put anything you want into the collection columns.
  • 2. Re: can't copy record that contains "#" using branching
    902143 Explorer
    Currently Being Moderated
    Hi,

    I don't get exactly what you are doing but yes when you try to pass values etc the string comes after # will be lost. One work around i use is to replace any special characters before the point where it loses the value and after getting the value to the right page etc then replacing back to original character.

    So what you should do is ot replace # with say underscore and once you got the value again replace back to #

    use the Pl/sql replace function
    http://www.techonthenet.com/oracle/functions/replace.php

    Hope this helps. If this still does't solve your problem write in detail what you are trying to do.

    Cheers
    kuru.
  • 3. Re: can't copy record that contains "#" using branching
    gmac Newbie
    Currently Being Moderated
    Great -- Thanks for the quick responses! I'll try the APEX Collection approach first and see how it goes ... Thanks - Gail
  • 4. Re: can't copy record that contains "#" using branching
    fac586 Guru
    Currently Being Moderated
    >

    Please update your forum profile with a real handle instead of "user12099650".
    Greetings Everyone - I'm using APEX 4.1 and have implemented a "Copy Record" button that copies selected fields from the record back to the same page to create a new record. This is done using branching (got the idea from this forum!)
    It's not a good one...
    and setting the items (e.g., P2_NAME,P2_ADDRESS1,P2_ADDRESS2, etc...) with these values (e.g., &P2_NAME.,&P2_ADDRESS1.,&P2_ADDRESS2., etc...)

    It has worked perfectly in other APEX applications -- up until now. The issue is that the new database contains several fields that contain the "#" character. When the "#" character is encountered during the copying process, any data after the # are not copied (but all of the data before the # are copied OK).
    As well as this problem you'll hit similar ones with other URL-reserved characters, and could hit problems with limits on URL length (IE only allows for about 2K).
    Is there a way to get around this? Removing the "#" characters is not an option, as it is part of numerous manufacturer part numbers that we track.

    Any guidance is appreciated. Thanks in advance for your help - Gail
    Do it properly using a page process.
  • 5. Re: can't copy record that contains "#" using branching
    gmac Newbie
    Currently Being Moderated
    Actually, I did update my handle to "gmac" before I created this thread ... Not sure why it created the thread using the old, creepy handle ... Thanks for the suggestions - Gail
  • 6. Re: can't copy record that contains "#" using branching
    gmac Newbie
    Currently Being Moderated
    Greetings Bob37 - Can you please expand on your suggestion below? I haven't worked with APEX Collections before (I just reviewed the documentation, but am still a bit confused about the steps to implement) ...

    Your suggestion:
    "I would have it create a record in an Apex collection after submit, then read the collection record back into your page items (and delete the collection/record) in a before header process - when the collection record exists. You can put anything you want into the collection columns. "

    Here are the steps as I see them (Please make any corrections or fill in things if I have missed them!):
    1. I should start by deleting the collection if it exists by putting the APEX_COLLECTION.DELETE_COLLECTION procedure in the "Processes" section of my APEX page to run before the header to get rid of the collection

    2. Then use APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY where the query contains a select statement that has the clause "where id = :P2_ID" so that it copies the correct record into the collection. This code should go in the "Process" section of my APEX page and run after Submit.

    3. Then I should access the collection by querying the view APEX_COLLECTIONS and putting those values into my page items (essentially, my form). Then the user would save the new record.

    Is that the correct process or have I missed something? Thanks for your help - Gail
  • 7. Re: can't copy record that contains "#" using branching
    Bob37 Pro
    Currently Being Moderated
    Hi Gail,

    I haven't tested this code but it should work with changes to accomodate your actual page items

    I envision it working this way

    This is the Before Header PL/SQL Process
    BEGIN
    IF wwv_flow_collection.collection_exists('MY_COL') THEN
    
    -- Get the record I wrote using Apex_Collection.Add_Member in the after submit process 
    --    if the collection exists and put the data into the page items
        SELECT c001,  c002, ....
        INTO :P1_ITEM1, :P1_ITEM2,..... 
        FROM apex_collections
        WHERE collection_name = 'MY_COL' AND SEQ_ID=1;
    
    --  Toss the collection
           wwv_flow_collection.delete_collection('MY_COL');
    END IF;
    END;
    The after submit PL/SQL process to write the collection record is:
    BEGIN
    -- Even though the collection shouldn't be there, get rid of it if it is.
    IF wwv_flow_collection.collection_exists('MY_COL') THEN
           wwv_flow_collection.delete_collection('MY_COL');
    END IF;
    
     -- Create the Collection
      apex_collection.create_or_truncate_collection('MY_COL');
    
    --   Add one record to the collection loading the page item data into the VARCHAR2 columns
    --  Note there are 5 columns available for numbers, a clob, and a blob, see below 
    --   SEQ_ID (See collection columns list below) will be set to 1
      apex_collection.add_member('MY_COL', p_c001=>:P1_ITEM1, p_c002=>:P1_ITEM2,... );
    
    **** Edit
    **** This could also be loaded using the Create collection from query,which after re-reading your original request is likely what you're looking for
    END;
    The collection record consists of these columns
    
    COLLECTION_NAME   NOT NULL VARCHAR2(255)
    SEQ_ID            NOT NULL NUMBER 
    C001              VARCHAR2(4000)
    C002              VARCHAR2(4000)
    C003              VARCHAR2(4000)   
    C004              VARCHAR2(4000)   
    C005              VARCHAR2(4000)  
    ...
    C050              VARCHAR2(4000)
    N001              NUMBER
    N002              NUMBER
    N003              NUMBER
    N004              NUMBER
    N005              NUMBER     
    CLOB001           CLOB
    BLOB001           BLOB  
    XMLTYPE001        XMLTYPE
    MD5_ORIGINAL      VARCHAR2(4000)  
    Edited by: Bob37 on Aug 15, 2012 10:54 AM
  • 8. Re: can't copy record that contains "#" using branching
    Bob37 Pro
    Currently Being Moderated
    PS - Note the edit I made above and also this is the documentation I was using for collection processing - http://docs.oracle.com/cd/E23903_01/doc/doc.41/e21676/apex_collection.htm#AEAPI651
  • 9. Re: can't copy record that contains "#" using branching
    gmac Newbie
    Currently Being Moderated
    Thanks, Bob ... This is certainly enough to get me started in the right direction ... Thanks for your help ... I'll let you know how it works out ... Gail
  • 10. Re: can't copy record that contains "#" using branching
    gmac Newbie
    Currently Being Moderated
    Bob - I tried implementing the code that you provided (filling in my item names, collection, etc.), but I can't get it to work ... I left me "Copy Record" button in place (which submits the page) and changed the branch on it to return to my original page ...

    Here's what should happen:
    1. I start out on page 30, which contains an Interactive Report
    2. The user clicks the "Edit" button for the desired record and moves to pg. 31, which contains the populated data-entry form for the selected record
    3. The user can edit the record if they like, and then click "Apply Changes" to save the edits to the database
    4. There is also a "Copy Record" button on that page that the user can click to copy certain fields of the existing record to a new record back on that same page (as originally described in my post, this worked fine until we came across a record that had a "#" character)
    5. Only the selected fields (e.g., NOT serial number) should be shown in the form for the new record.
    6. The user could make additional edits, as needed and then click "Save" to save the new record to the database

    Here's what actually happens with your revised code in place:
    1-3, same as above
    4. Clicking the "Copy Record" button (without Clear Cache on the branching) causes the page to blink, but all of the records are exactly as before (including the records like SerialNumber that I did not include in the Collection code) ... If I check Clear Cache in the branching back to this page, I get a blank form

    Clearly I am missing something ... I tried several variations, but haven't been able to come up with anything that works ... The code compiles, so it appears that I have all of my Collections columns and page items matched up correctly ... Thanks for your patience in helping me work through this ... Gail
  • 11. Re: can't copy record that contains "#" using branching
    Bob37 Pro
    Currently Being Moderated
    Ok, so you have a Copy Record button on page 31. Clicking it submits the page and you return to page 31 where you expect to have all page items cleared and then selected page items/fields repopulated from the collection.

    You do need the clear cache on the branch for page 31 to clear the serial number and any other desired fields.

    I don't know exactly when during page rendering the cache gets cleared.. one would think it would be before anything happens, So,
    I wonder if the Before Header process to repopulate the page items needs to be reset to be an After Header process. You can edit the process and make that change.

    You can also run the page in debug and watch as page items get cleared and reset. This would also validate that the collection is getting populated so the data is there to rest the page items. You could also comment out the deletion of the collection in the After Header process temporarily and create a simple classic report region for test purposes that just does SELECT C001, c002, c003 FROM Apex_Collections WHERE Colllection_name = 'MY_COL' and see that the collection both exists and is populated.

    If you would try that.
  • 12. Re: can't copy record that contains "#" using branching
    gmac Newbie
    Currently Being Moderated
    OK - Here's what I did:
    1. I put the "Clear Cache" back in the branching
    2. Changed the first code block to run after header
    3. Added classic report region with simple query, as suggested
    4. Ran in debug mode and examined the results

    Here are the results when clicking the "Copy Record" button:
    1. The form gets cleared out -- completely blank
    2. The report gets populated, and with the correct values from the collection
    3. Debug review confirms that the first code block runs and session state shows the value being saved into the page item
    (e.g., ..Session State: Saved Item "P31_MANUFACTURERPARTNUMBER" New Value="123456" )
    4. After it lists the session state for all of the items that are part of the collection, then it just lists all of the regular page items and if they are popup_lov, text_field, etc.
    5. Towards the end, there is "..Execute Statement: select c001, c002 ... from apex_collections where collection_name='MY_COL' "

    So - it seems that I am close, because the collection is being created and populated properly... It just isn't putting the data back into the form after it is cleared out ... Thanks for all of your help - Gail
  • 13. Re: can't copy record that contains "#" using branching
    Bob37 Pro
    Currently Being Moderated
    You're welcome.

    So, bullet point point 3 is where the SELECT INTO is happening and View Debug shows the value changing for the page items being loaded ?

    And bullet point 5 is where the select is occurring for the classic test sql report ?

    When the form is blank, I'd expect to see somewhere in the debug output after point 3 when they show as "Session State: Saved Item... " that the page items are being reset to "".

    When you ediit the page items; What is the Source Type of the page items? (static, database, ...) and are they set Source Used to Always, Replacing existing values, or Only when Null ?

    Are there any other processes ?

    Mainly, if the item are null when the form region is rendered, I'd really expect to see somewhere in the debug output where they're being cleared out.

    ** Edit, I think I'm confused about what is happening in # 5 with the Select from collection.. Is that the report? It seems from re-readig the report is running first, then the page items get loaded, then along comes another select in bullet point 5.. ?

    Edited by: Bob37 on Aug 15, 2012 4:20 PM

    ** Edit # 2, I would uncomment out the deletion of the collection and make sure the report is running first to list the contents of the collection before loading the page items and deleting it. That would get your setup as close as possible to how you'll have it for production. Then once it's working, remove the report and it's ready to go.

    Edited by: Bob37 on Aug 15, 2012 4:26 PM
  • 14. Re: can't copy record that contains "#" using branching
    gmac Newbie
    Currently Being Moderated
    OK - checked out the debug again ... there is no reference (after my point 3), that indicates that the records are being cleared out (or reset again) ...

    As suggested, I checked the definitions of the individual page items ... here are the settings (likely for all of the items, although I only checked a few and they were all the same) :
    Source Used: "Always, replacing any existing values in session state"
    Source Type: "Database Column"

    Maybe that's it ... I'll do some testing and let you know ... Gail
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points