create or replace function fnc_26ai_embed( p_string in varchar2, 
                                          p_emb_type in varchar2 default 'COHERE',
                                          p_credential in varchar2 default 'OCI_CRED')
return clob
as
/*

  Criado por: fernando.leal@oracle.com
  Data: Oct/2025
  Objetivo: demonstrar casos de uso do Oracle AI Database 26ai

  v1 - embedding de dados com Cohere On-Demand- leal

*/
  v_embedding clob;
  v_string clob;
  v_url    VARCHAR2(700) := 'https://inference.generativeai.sa-saopaulo-1.oci.oraclecloud.com/20231130/actions/embedText';
  v_body json_object_t;
  r        dbms_cloud_types.resp;

begin

   v_string := replace(replace(replace(p_string,chr(13),' '),chr(10),' '),'"','')  ;

   if p_emb_type = 'ONNX' then

     /* Carga previa do ONNX ao banco (pre requisito)
     BEGIN
        DBMS_VECTOR.LOAD_ONNX_MODEL_CLOUD(
                    model_name => 'multilingual_e5_base', 
                    credential => 'OCI_CRED', 
                    uri => 'https://objectstorage.sa-saopaulo-1.oraclecloud.com/n/idi1o0a010nx/b/bucket-poc-rag/o/multilingual-e5-base.onnx',
                    metadata => JSON('{"function" : "embedding", "embeddingOutput" : "embedding" , "input": {"input": ["DATA"]}}')
        );
     END; 

       BEGIN
          DBMS_VECTOR.DROP_ONNX_MODEL( model_name => 'paraphrase_multilingual');
        END;
        
     */

		SELECT VECTOR_EMBEDDING( multilingual_e5_base  USING  v_string  AS data   ) as embedding 
		into v_embedding;

   elsif p_emb_type = 'COHERE' then


/*  CUIDADO: dbms_vector_chain.utl_to_text(  v_string ),  O utl_to_text usa internamente o Oracle Text Filter (processo externo ctxfilt) 
 para converter documentos (PDF, DOCX, etc.) em texto puro. 
 O erro DRG-11225: Third-party filter timed out significa que esse processo 
 demorou mais que o timeout configurado e foi abortado pelo Oracle. */

		select embed_vector
		 into v_embedding
         from dual 
             CROSS JOIN TABLE(
                 dbms_vector_chain.utl_to_embeddings(
                  --   dbms_vector_chain.utl_to_chunks(
                         dbms_vector_chain.utl_to_text(  v_string ),
                       --  json('{"by":"words","max":"400","overlap":40,"split":"sentence","normalize":"all"}')
                    -- ),
					 --
					 -- json para cohere embedding
					 --
                      json('{    "provider": "ocigenai",
                                "credential_name": "' || p_credential || '",
                                "url": "https://inference.generativeai.us-chicago-1.oci.oraclecloud.com/20231130/actions/embedText",
                                "model": "cohere.embed-v4.0"
                            }')
                 )
             )  t 
             CROSS JOIN JSON_TABLE(
                 t.column_value, 
                 '$[*]' COLUMNS (
                     embed_id NUMBER PATH '$.embed_id',
                     text_chunk VARCHAR2(4000) PATH '$.embed_data',
                     embed_vector CLOB PATH '$.embed_vector'
                 )
         ) AS et;

     elsif p_emb_type = 'vLLM' then
				  
                declare
                        params clob;
                        v_embedding2 vector;
				begin
		   
					-- Set host to local to disable credential
					-- The provider value must specify openai
					-- https://docs.oracle.com/en/database/oracle/oracle-database/26/vecse/utl_to_embedding-and-utl_to_embeddings-dbms_vector_chain.html
					
					params := '{
							  "provider": "openai",
							  "url": "https://hub-gpus.DOMINIO.com.br/embed/v1/embeddings",
							  "host": "local",
							  "model": "Qwen/Qwen3-Embedding-4B",
                              "batch_size": 50}';

                    -- dicas: https://docs.oracle.com/en/database/oracle/oracle-database/26/vecse/utl_to_embedding-and-utl_to_embeddings-dbms_vector.html

					v_embedding2 := dbms_vector_chain.utl_to_embedding(  v_string , json(params)) ;

                    v_embedding := TO_CLOB(TO_CHAR( v_embedding2 ) ) ;

					-- Confirmar resultado
					--DBMS_OUTPUT.PUT_LINE('Dimensões : ' || VECTOR_DIMENSION_COUNT(v_embedding));
					--DBMS_OUTPUT.PUT_LINE('Format : ' || VECTOR_DIMENSION_FORMAT(v_embedding));
				exception
					when OTHERS THEN
					   DBMS_OUTPUT.PUT_LINE('Erro: ' || SQLCODE || ' - ' || SQLERRM);
				end;


-- neste tenancy nao tenho o endpoint do COHERE-DEDICATED  - leal 17-10-2025 
/*    elsif p_emb_type = 'COHERE-DEDICATED' then

            v_body := json_object_t('{"servingMode":{"servingType":"DEDICATED",
                                                     "endpointId":"ocid1.generativeaiendpoint.oc1.sa-saopaulo-1.amaaaaaaa2b7yriam5myabesz5cyoxez4f266vx656q3lrfwe3a753y5keoq"
                                                    },
                                       "inputs":["' || v_string || '"],
                                       "compartmentId":"ocid1.compartment.oc1..aaaaaaaa5ewni44wndu5nwrhbqss4jaoel742xvprjkg64kb7vt7es4utzua"}
                                    ');

            r := dbms_cloud.send_request(
                   credential_name => 'OCI_CRED',
                   uri             => v_url,
                   method          => dbms_cloud.method_post,
                   body            => utl_raw.cast_to_raw(v_body.to_clob),
                   headers => json_object('Accept' value 'application/json', 'X-Custom-Header' VALUE 'My-Custom-Value')
                 );

             select jt.embeddings
             into v_embedding
             from dual j,
             json_table( dbms_cloud.get_response_text(r), '$' columns (embeddings clob PATH '$.embeddings[*].vector()') ) jt;
*/
    end if; 

	return v_embedding;

end;
/