Location>code7788 >text

Typical configuration of LLM model for Oracle AI applications

Popularity：732 ℃/2025-03-25 14:22:12

Recently, I have been doing some tests of some AI application based on Oracle. AI is definitely inseparable from configuration LLM. Although it is a simple configuration class, I still encounter some stuck points. I will record it for future reference.

  • 1. Configure the Embedding model
  • 2. Special syntax transfer parameter JSON format
  • 3. Test Embedding is effective
  • 4. Modify MAX_STRING_SIZE
  • 5. LLM configured as DeepSeek
  • 6. Testing Chat and Showsql to be effective

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 is 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, passing in JSON format method
 declare
   jo json_object_t;
 Begin
   jo := json_object_t();
   ('access_token', 'sk-xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx);
   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:

--EMBEDDING interface - text steering vector
 select CUSTOM_SELECT_AI.EMBEDDING(
     p_text => 'Convert text to 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. Check the current value of the parameter
 SHOW PARAMETER MAX_STRING_SIZE;

 --2. Set MAX_STRING_SIZE=EXTENDED
 ALTER SYSTEM SET MAX_STRING_SIZE=EXTENDED SCOPE=SPFILE;

 --3. Close the database
 SHUTDOWN IMMEDIATE;

 --4. Start the upgrade mode and execute the script
 STARTUP UPGRADE;
 @$ORACLE_HOME/rdbms/admin/

 --5. Restart the database
 SHUTDOWN IMMEDIATE;
 STARTUP;

 --6. Query MAX_STRING_SIZE parameter has 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:

-- Create PROVIDER and PROFILE to 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 PROVIDER and PROFILE that are no longer needed:
 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:

--CHAT interface - Chat directly with LLM
 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 access:

BEGIN
     DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
         host => '*', -- or specify a specific domain name, such as ''
         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:

--SHOWSQL interface - Natural language generation SQL
 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 that meets 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.

<Previous Next>