{"id":3322,"date":"2024-03-23T09:26:00","date_gmt":"2024-03-23T09:26:00","guid":{"rendered":"https:\/\/www.bohort.nl\/?p=3322"},"modified":"2024-08-06T10:22:45","modified_gmt":"2024-08-06T10:22:45","slug":"introducing-oracle-select-ai","status":"publish","type":"post","link":"https:\/\/www.bohort.nl\/en\/2024\/03\/23\/introducing-oracle-select-ai\/","title":{"rendered":"Introducing Oracle Select AI"},"content":{"rendered":"\n

Last week a presented on APEX World24 where I introduced Oracle Select AI to the audience. The first part of the session was all about what is Select AI, how to you set it up, and how to use it. In this blogpost I will share my findings on how Select AI works and how you can set it up on readily prepared data from Oracle, so you can start playing around. Using Oracle Select AI\u2019s NLP feature, a marketing manager can generate insights using simple language:<\/p>\n\n\n\n

\"Show me the top 10 products by sales in the last quarter.\"<\/code><\/pre>\n\n\n\n
Oracle Select AI translates this request into an SQL query, executes it, and presents the results in an easy-to-understand format. In order to understand this, please have a look at the image below.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Setup Oracle select ai<\/h2>\n\n\n\n
It is very easy to setup Oracle Select AI in you database. There are just a few steps involved. Let me explain them. <\/p>\n\n\n\n
Oracle Autonomous Database uses a large language model (LLM) to translate natural language to SQL. You can choose the LLM to use for your application. I choose to use my OpenAI account. If you do not have any, you can sign up for one. In either case, you would need a to create a new API secret key. The secret key is used to sign requests to OpenAI’s API. You will need it when creating a credential later. <\/p>\n\n\n\n
The first thing you need to do is grant the moviestream<\/strong> user permissions to make REST calls to api.open.com<\/strong>. This is done by adding the user to the network Access Control List (ACL). For more details, see the DBMS_NETWORK_ACL_ADMIN PL\/SQL Package<\/a> documentation.<\/p>\n\n\n\n
<\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
BEGIN<\/span><\/span>\nDBMS_NETWORK_ACL_ADMIN<\/span>.<\/span>APPEND_HOST_ACE<\/span> (<\/span><\/span>\n  <\/span>HOST<\/span>         <\/span>=><\/span> <\/span>'api.openai.com'<\/span>,<\/span><\/span>\n  <\/span>LOWER_PORT<\/span>   <\/span>=><\/span> <\/span>443<\/span>,<\/span><\/span>\n  <\/span>UPPER_PORT<\/span>   <\/span>=><\/span> <\/span>443<\/span>,<\/span><\/span>\n  <\/span>ACE<\/span>          <\/span>=><\/span> <\/span>xs$ace_type<\/span>(<\/span><\/span>\n      <\/span>PRIVILEGE_LIST<\/span> <\/span>=><\/span> <\/span>xs$name_list<\/span>(<\/span>'http'<\/span>),<\/span><\/span>\n      <\/span>PRINCIPAL_NAME<\/span> <\/span>=><\/span> <\/span>'MOVIESTREAM'<\/span>,<\/span><\/span>\n      <\/span>PRINCIPAL_TYPE<\/span> <\/span>=><\/span> <\/span>xs_acl<\/span>.<\/span>ptype_db<\/span>));<\/span><\/span>\nEND<\/span>;<\/span><\/span>\n\/<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Next grant the EXECUTE<\/code> privilege on the DBMS_CLOUD_AI<\/code> package to the user who wants to use Select AI.By default, only ADMIN user is granted the EXECUTE<\/code> privilege. The ADMIN user can grant EXECUTE<\/code> privilege to other users.<\/p>\n\n\n\n
<\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
GRANT<\/span> <\/span>EXECUTE<\/span> <\/span>ON<\/span> <\/span>DBMS_CLOUD_AI<\/span> <\/span>TO<\/span> <<\/span>USER<\/span>>;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Then create a credential to enable access to your AI provider<\/p>\n\n\n\n
<\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
BEGIN<\/span><\/span>\n  <\/span>DBMS_CLOUD<\/span>.<\/span>DROP_CREDENTIAL<\/span> (<\/span><\/span>\n   <\/span>credential_name<\/span>  <\/span>=><\/span> <\/span>'OPENAI_CRED'<\/span>);<\/span><\/span>\n<\/span>\n  <\/span>DBMS_CLOUD<\/span>.<\/span>CREATE_CREDENTIAL<\/span>(<\/span><\/span>\n    <\/span>credential_name<\/span> <\/span>=><\/span> <\/span>'OPENAI_CRED'<\/span>,<\/span><\/span>\n    <\/span>username<\/span> <\/span>=><\/span> <\/span>'OPENAI'<\/span>,<\/span><\/span>\n    <\/span>password<\/span> <\/span>=><\/span> <\/span>'enter-your-LLM-secret-key-here'<\/span> );<\/span><\/span>\nEND<\/span>;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Finally you DBMS_CLOUD_AI.CREATE_PROFILE to create an AI profile. The following example with the OpenAI provider creates an AI profile called openai_gpt35. <\/p>\n\n\n\n
<\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
BEGIN<\/span> <\/span><\/span>\nDBMS_CLOUD_AI<\/span>.<\/span>CREATE_PROFILE<\/span>( <\/span><\/span>\n   <\/span>profile_name<\/span> <\/span>=><\/span> <\/span>'openai_gpt35\u2019,<\/span> <\/span><\/span>\n   <\/span>attributes<\/span> <\/span>=><\/span> <\/span>'{"provider": "openai\u201d,<\/span> <\/span><\/span>\n                 <\/span>"credential_name"<\/span>: <\/span>"OPENAI_CRED"<\/span>, <\/span><\/span>\n                 <\/span>"object_list"<\/span>: <\/span><\/span>\n                [{<\/span>"owner"<\/span>:<\/span> <\/span>"MOVIESTREAM"<\/span>, <\/span>"name"<\/span>:<\/span> <\/span>"movies"<\/span>}, <\/span><\/span>\n                 \u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026<\/span><\/span>\n                 {<\/span>"owner"<\/span>:<\/span> <\/span>"MOVIESTREAM"<\/span>, <\/span>"name"<\/span>:<\/span> <\/span>"pizza_shop"<\/span>},<\/span><\/span>\n                 {<\/span>"owner"<\/span>:<\/span> \u201d<<\/span>OWNER<\/span>-<\/span>NAME<\/span>><\/span>", "<\/span>name<\/span>": \u201d<OBJECT-NAME"<\/span>},<\/span><\/span>\n                 {<\/span>"owner"<\/span>:<\/span> <\/span>"] }\u2019<\/span> <\/span><\/span>\n); <\/span><\/span>\nEND<\/span>; <\/span><\/span>\n\/<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
As you can have multiple Select AI<\/strong> profiles. You need to pick the one to use for your session. Use the DBMS_CLOUD_AI.SET_PROFILE<\/strong> procedure to specify which profile to use as follows:<\/p>\n\n\n\n
<\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
BEGIN<\/span><\/span>\n    <\/span>DBMS_CLOUD_AI<\/span>.<\/span>SET_PROFILE<\/span>(<\/span>profile_name<\/span> <\/span>=><\/span> <\/span>'openai_gpt35'<\/span>);<\/span><\/span>\nEND<\/span>;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
You can use the AI keyword in a query with Oracle clients such as SQL Developer. Use AI as the keyword in a SELECT statement for interacting with the database using natural language prompts. The AI keyword in a SELECT statement instructs the SQL execution engine to use the LLM identified in the active AI profile to process natural language and to generate SQL.<\/p>\n\n\n\n
Note that you can use several different keywords with Select AI:<\/p>\n\n\n\n