Typical configuration method for LLM model for Oracle AI applications

1. Configure the Embedding model

In the Text2SQL RAG open source project of colleague Hysun, some very practical configuration methods are provided for everyone to use. This article takes this open source project as an example.

The Embedding model I temporarily chose the BAAI/bge-large-zh-v1.5 model provided by the SiliconFlow platform:

--use SiliconFlow Embedding：
BEGIN
  CUSTOM_SELECT_AI.CREATE_EMBEDDING_CONF(
        p_conf_id     =>    'EMBEDDING',
		p_provider    =>    'OpenAI',
        p_model       =>    'BAAI/bge-large-zh-v1.5',
		p_endpoint    =>    '/v1/embeddings',
		p_credential  =>    'ALFRED_SILICON_FLOW'
	);
END;
/

At first I directly wrote the API Key into p_credential, but found that it didn't work, but it was replaced with a custom one.ALFRED_SILICON_FLOWAfter that, when importing through dbms_vector.create_credential, initially, it was thought that the JSON format parameters were directly transmitted, but the result was not allowed.

Also, note herep_conf_idI will use the name of the future. At first, I didn’t realize it, and only after consulting my colleagues did I know the corresponding relationship. Later, in order to make everyone better understand, my colleagues also modified the help document and made clear tips.

2. Special syntax transfer parameter JSON format

Go through the official documentation and find this special syntax, specifically used for passing in JSON format.

--Special syntax，IncomingJSONFormat method
declare
  jo json_object_t;
begin
  jo := json_object_t();
  ('access_token', 'sk-xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');
  dbms_vector.create_credential(
    credential_name   => 'ALFRED_SILICON_FLOW',
    params            => json(jo.to_string));
end;
/

The API Key has been desensitized, just replace it according to your key.

After configuration, you can query this table:

select * from CUSTOM_SELECT_AI_EMBEDDING_CONF;

3. Test Embedding is effective

Use the provided test cases to test Embedding effectiveness:

--EMBEDDINGinterface - Text turn vector
select CUSTOM_SELECT_AI.EMBEDDING(
    p_text => 'Convert text into vector',
    p_embedding_conf => 'EMBEDDING'
);

Pay attention to thisp_embedding_confNeed to be created beforep_conf_idCorrespondingly, there is this correspondence relationship for later use, so special attention is needed.

The test returns an error:

ORA-06502: PL/SQL: Value or conversion error: String buffer is too small
ORA-06512: in line 1

/error-help/db/ora-06502/06502. 00000 -  "PL/SQL: value or conversion error%s"
*Cause:    An arithmetic, numeric, string, conversion, or constraint error
           occurred. For example, this error occurs if you attempt to
           assign the value NULL to a variable declared NOT NULL, or if you
           attempt to assign an integer greater than 99 to a variable
           declared NUMBER(2).
*Action:   To resolve the issue, change the data, the way the data is
           manipulated, or the data variable declaration.
*Params:   1) error_info
           occurred.

4. Modify MAX_STRING_SIZE

The above error is obvious. The document also mentioned that MAX_STRING_SIZE needs to be set to EXTENDED.
Reference steps for modification (please carefully evaluate the feasibility in the production environment):

--1.View the current value of the parameter
SHOW PARAMETER MAX_STRING_SIZE;
--2.set upMAX_STRING_SIZE=EXTENDED
ALTER SYSTEM SET MAX_STRING_SIZE=EXTENDED SCOPE=SPFILE;
--3.Close the database
SHUTDOWN IMMEDIATE;
--4.start upupgrademodel，Execute scripts
STARTUP UPGRADE;
@$ORACLE_HOME/rdbms/admin/
--5.Restart the database
SHUTDOWN IMMEDIATE;
STARTUP;
--6.QueryMAX_STRING_SIZEParameters have been modified
SHOW PARAMETER MAX_STRING_SIZE;

Run the Embedding test case again and the result is successfully returned.

5. LLM configured as DeepSeek

It mainly uses two CUSTOM_SELECT_AI.CREATE_PROVIDER and CUSTOM_SELECT_AI.CREATE_PROFILE.

----- Create service provider，deepseek
BEGIN
  CUSTOM_SELECT_AI.CREATE_PROVIDER(
		p_provider    =>    'OpenAI',
		p_endpoint    =>    '/chat/completions',
		p_auth        =>    'sk-xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
	);
END;
/
----- Create profile
BEGIN
	CUSTOM_SELECT_AI.CREATE_PROFILE(
      p_profile_name    =>'HKE_DEMO',
      p_description     => 'SelectAI DEMO for HKE',
      p_attributes      => '{
          "provider": "OpenAI",
          "model" : "deepseek-chat",
          "object_list": [{"owner": "TPCH", "name": "HKE_PROD_DEFECT"},
                          {"owner": "TPCH", "name": "HKE_PROD_OUT_YIELD_QTY"}
                          ]
      }'
    );
END;
/

After creation, you can query:

--CreatedPROVIDERandPROFILECan query tables：
select * from TPCH.CUSTOM_SELECT_AI_PROVIDERS;
select * from TPCH.CUSTOM_SELECT_AI_PROFILES;

If the configuration is wrong or no longer needed, you can delete it like this:

--Delete what is no longer neededPROVIDERandPROFILE：
BEGIN
  CUSTOM_SELECT_AI.DROP_PROVIDER(
		p_provider    =>    'OpenAI'
	);
END;
/
BEGIN
  CUSTOM_SELECT_AI.DROP_PROFILE(
		 p_profile_name    =>'HKE_DEMO'
	);
END;
/

6. Testing Chat and Showsql to be effective

Test Chat and Showsql functionality as described in the open source project documentation:

--CHATinterface - Directly with LLM chat
select CUSTOM_SELECT_AI.CHAT(
    p_profile_name  => 'HKE_DEMO',
    p_user_text     => 'Who are you?  ',
    p_system_text   => 'You are a positive, positive AI assistant.  '
);

Note: Chat configured with LLM should return normally. If an error is reported, ORA-29273 is shown in the figure below:

• 4-sys.utl_http.png

It is necessary to relax the corresponding database users' specific or all external accesses:

BEGIN
    DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
        host => '*',  -- Or specify the specific domain name，like ''
        ace  => xs$ace_type(privilege_list => xs$name_list('connect'),
                            principal_name => 'TPCH', 
                            principal_type => xs_acl.ptype_db));
END;
/

Continue to test the Showsql function:

--SHOWSQLinterface - Natural language generationSQL
select CUSTOM_SELECT_AI.SHOWSQL(
  	p_profile_name => 'HKE_DEMO',
    p_embedding_conf => 'EMBEDDING',
  	p_user_text => 'Query the proportion of each YIELD small-level meeting the conditions (i.e. the sum of YIELD_QTY/OUT_QTY), and the conditions are: the company name is COMPANY1, the factory name is FACTORYNAME1, and the product name is PRODUCT1.  The proportion is expressed in percentage and sorted, and returned in Chinese alias.  '
);

SHOWSQL needs to import the successful table data and vectorize it in accordance with the Demo requirements. Herep_embedding_confPay attention to the same name as the Embedding configured previously.

This is the end of this article about the typical configuration of the LLM model for Oracle AI applications. For more information about Oracle LLM model, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!