# Oracle OCI - The Naked Truth - Post 2

In order to migrate data and process the documents I broke the solution into these steps:

Whilst the OCI client was being set up I worked on the solution which included code to do the following:

*   An initial load of client data from a spreadsheet to store the documents against.
    
*   Get documents from the object storage staging area.
    
*   Load them into the DB into a documents staging table as blobs/filenames etc. on the DB.
    
*   Reconcile the data (needed due to a previous load of some of the files/client data).
    
*   Load the documents to object storage storing them in the application documents table (docs named as a uuid held in data).
    
*   Delete the blobs from the staging tables.
    

I developed the solution and loaded a sample of documents against a small no. of clients as a proof of the solution. To do this I loaded files into the bucket manually via the OCI web application. Once this was done I thought I'd cracked it...

However, when I first tried to run the package procedure to load the full list of documents into the document staging table I found that it did not run in a timely manner. I left it running for 6 hours before killing the process...

This was my original program unit:

```sql
procedure load_oci_files (p_input_id number) is
begin
    FOR rec IN (
        SELECT *
        FROM
        (
            SELECT object_name, file_name, ROWNUM rn
            FROM
                (
                SELECT object_name /*substr/replace*/ file_name,
                       object_name            
                FROM   DBMS_CLOUD.LIST_OBJECTS(credential_name =>                   
                       'OCI_OBJ_STORAGE',
                       location_uri =>
                       g_base_url||g_ra_micro_bucket)
                WHERE BYTES > 0
                ORDER BY object_name
                )
            WHERE FILE_NAME NOT IN ('lfdNr', 'Thumbs.db')
        )  
        WHERE rn BETWEEN (p_Input_id*10000)-9999 AND p_Input_id*10000 
        )  
    LOOP
        l_Blob := DBMS_CLOUD.GET_OBJECT(
                  credential_name => 'MY_CRED_NAME', -- pre-created credential
                  object_uri      => rec.object_uri
                                        );
  
      insert into DOCUMENT_FILE
        (
        ...
        FSIZE,
        FCONTENT,
        ...
        )
        values
        (
        ...
        dbms_lob.getlength(l_Blob),
        l_Blob,
        ...
        );        
      END LOOP;
end load_oci_files;
```

### Parallel Processing

I looked into how I could speed up the program unit and looked into parallel processing (a first for me). Basically parallel processing allows a task to be split and run in parallel by a number of 'workers'. It is generally table based and works by splitting the load down by either row\_id or a number (e.g. id column). Once it is set up then the handling of managing the split of ids and parallel processing jobs etc. is managed by Oracle.

I had a ‘conversation’ with AI via a popular search engine which recommended the code. I reworked this into my package whilst also verifying and learning from this page: [DBMS\_PARALLEL\_EXECUTE](https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_PARALLEL_EXECUTE.html#GUID-18C01BED-25F5-4B87-BA77-170EDFB70423). I added a separate program unit to load the filenames and URIs into a STAGING\_LOAD\_DOCS table, to allow the ROWID split, and the remaining code now looked like this:

```sql
function load_oci_files_sql (p_input_id number) return clob is
  l_sql CLOB;
BEGIN
  l_sql := q'[
    DECLARE
      l_blob blob;
    BEGIN
      FOR rec IN (
        SELECT input_id, object_uri, file_name, object_name
        FROM STAGING_LOAD_DOCS
        WHERE ROWID BETWEEN :start_id AND :end_id
          AND input_id = ]'||p_input_id||q'[
      ) LOOP
        l_Blob := DBMS_CLOUD.GET_OBJECT(
                      credential_name => 'MY_CRED_NAME', -- pre-created credential
                      object_uri      => rec.object_uri
                                        );
      insert into DOCUMENT_FILE
        (
        ...
        FSIZE,
        FCONTENT,
        ...
        )
        values
        (
        ...
        dbms_lob.getlength(l_Blob),
        l_Blob,
        ...
        );        
      END LOOP;
     
    END;
  ]';

  return l_sql;
end load_oci_files_sql;

procedure create_and_run_load_task(p_input_id number) is
    l_sql CLOB;
BEGIN

    -- Drop if exists to avoid wrong state
    BEGIN
        DBMS_PARALLEL_EXECUTE.DROP_TASK(k_Task_Name);
        EXCEPTION
        WHEN OTHERS THEN NULL; -- Ignore if task doesn't exist
    END;

    DBMS_PARALLEL_EXECUTE.CREATE_TASK(k_Task_Name);

    -- Chunk by ROWID so each chunk processes a subset of rows
    DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID(
        task_name   => k_Task_Name,
        table_owner => USER,
        table_name  => k_Task_Name,
        by_row      => TRUE,
        chunk_size  => 1  -- one file per chunk
    );

  l_sql := load_oci_files_sql(p_input_id);

  DBMS_PARALLEL_EXECUTE.RUN_TASK(
    task_name      => k_Task_Name,
    sql_stmt       => l_sql,
    language_flag  => DBMS_SQL.NATIVE,
    parallel_level => 4  -- number of concurrent workers
  );
END create_and_run_load_task;
```

* * *

The key sections in the code are this line in the select statement:

```sql
WHERE ROWID BETWEEN :start_id AND :end_id
```

And also these parameters: for example:  
`chunk_size => 100 -- 100 files per chunk`  
`parallel_level => 8 -- number of concurrent workers`

These are used to split the load into multiple sections. E.g. for this example:

8 workers in parallel will process 100 rows each.

## Lessons Learnt and Outstanding Questions

AI suggested a chunk size of 1 which seemed extreme so I asked AI to explain this. The reasoning was that the dbms\_cloud.get\_object call and inserting the blobs was the most expensive operation. Also it mentioned that this would give the least lag time compared to grouping a set number of different file types.

BUT when I re-examined this whilst writing this blog post then I was given findings to the contrary:

![](https://cdn.hashnode.com/uploads/covers/6a8b310b5f947d0eb679e4f5/93521a0c-6dfd-4748-a176-0a1dfa74d0a2.png align="center")

In my case I proceeded with a chunk size of 1.

Upon further reading about DBMS\_PARALLEL\_EXECUTE I've realised that the examples are primarily for sql update statements (inserts and merges are also mentioned) and that the criteria are focused on this. Indeed the DBMS\_PARALLEL\_EXECUTE.RUN\_TASK parameter is sql\_stmt. PL/SQL code can be used though, clearly so I am interested to learn more about this.

Another point is around error handling - the following code (the WHILE loop) is in the examples:

```sql
L_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS('mytask'); WHILE(l_try < 2 and L_status != DBMS_PARALLEL_EXECUTE.FINISHED) LOOP 
  L_try := l_try + 1; DBMS_PARALLEL_EXECUTE.RESUME_TASK('mytask');
  L_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS('mytask'); 
END LOOP;
```

In my case had no such error handling in the code but this was an overseen, one off process of running the scripts for the migration and I was running the following to check for errors (there were none).

```sql
SELECT *
FROM   user_parallel_execute_chunks
WHERE  task_name = 'RA_MICRO_LOAD_DOCS'
ORDER BY chunk_id;
```

In the case of reusable production code then the error handling is likely to be essential.

## Use of AI in Coding Solutions

As mentioned earlier, I have been partially assisted by AI (via an internet search engine in this case) in providing this solution. There are some obvious pitfalls to this and likely some non-obvious ones too. In closing this blog post here are some of my initial concerns (although I'm barely scratching the surface - more to come on this topic):

*   Not fully understanding the solution provided by AI (in reality this is going to happen regularly).
    
*   AI not understanding the problem, the problem being miscommunicated and/or the solution being incomplete. Solutions should be thoroughly tested.
    
*   With normal, manually typed code the practice of thinking about and writing the code means the code is more likely to be understood by the programmer. AI provided code means there is a layer of separation between the programmer and the solution.
    
*   As the use of AI coding becomes more widespread us coders are inevitably going to have diminished skills in writing code although code reading and diagnostic skills are still a strong requirement currently.