SelectAI4SQL -0- Prerequisites and Setup.dsnb

[{"layout":"zeppelin","isRunnable":true,"template":"dsrgmn3y","templateConfig":"{\"visualization\":{\"filters\":[{\"_id\":1583324064459,\"type\":\"styling\",\"enabled\":true,\"conditions\":{\"operator\":\"and\",\"conditions\":[{\"property\":\"hiddenConnection\",\"operator\":\"*\",\"value\":\"\"}]},\"component\":\"edge\",\"target\":\"edge\",\"properties\":{\"colors\":[\"rgba(0, 0, 0, 0.1)\"],\"style\":[\"dashed\"],\"legendTitle\":[\"Hidden Connection\"]}},{\"_id\":1590499315755,\"type\":\"aggregation\",\"enabled\":true,\"conditions\":{\"operator\":\"and\",\"conditions\":[]},\"component\":\"vertex\",\"target\":\"vertex\",\"properties\":{},\"aggregation\":[{\"source\":\"\",\"type\":\"average\"}]}],\"version\":4}}","isEditable":true,"name":"SelectAI4SQL -0- Prerequisites and Setup","description":null,"type":"low","paragraphs":[{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":null,"title":null,"message":["%md"," "],"enabled":true,"result":{"startTime":1759422019053,"interpreter":"md.low","endTime":1759422019479,"results":[],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":0,"hideResult":true,"dynamicFormParams":"{}","row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":null,"message":["%md\r","\r","# Oracle Select AI: Setting up the environment\r","\r","This notebook prepares your Autonomous Database instance to use Oracle Select AI. \r","\r","This setup must be completed before using NL2SQL, RAG, Synthetic Data Generation, and other Select AI features.\r","\r","#### What is Select AI?\r","\r","Select AI enables SQL and PL/SQL access to Large Language Models (LLMs) and transformers from a range of AI providers. \r","It supports SQL query generation from natural language prompts against your database data (NL2SQL), retrieval augmented generation (RAG), and synthetic data generation (SDG), among other features.  \r","\r","See this <a href=\"https://www.youtube.com/watch?v=rnsnIYYZzr4\" target=\"_blank\">video<\/a> for an introduction to Select AI.\r","\r","#### Setup Overview\r","\r","To configure Select AI, both the ADMIN and a non-ADMIN users are required. You will need to specify access credentials to the AI provider(s) you want to use and, for RAG, cloud storage. \r","\r","**ADMIN Tasks:**\r","- Grant permissions to OML users\r","- Configure network access (if needed)\r","\r","**Select AI User Tasks:**\r","- Create cloud authentication credentials\r","- Create AI profiles\r","- Create vector indexes as needed in support of RAG\r","\r","\r","> **Note:** This notebook includes examples based on OCI Generative AI Service. However  multiple other AI providers are supported. \r","Users must have an account with a compatible AI provider API and provide access to Autonomous Database via database credentials.\r","Consult the <a href=\"https://docs.oracle.com/en-us/iaas/autonomous-database-serverless/doc/sql-generation-ai-autonomous.html\" target=\"_blank\">Select AI documentation<\/a> for details.\r","\r","Copyright (c) 2025 Oracle Corporation \r","###### <a href=\"https://oss.oracle.com/licenses/upl/\" target=\"_blank\">The Universal Permissive License (UPL), Version 1.0<\/a>\r","---"],"enabled":true,"result":{"startTime":1759422044453,"interpreter":"md.low","endTime":1759422044901,"results":[{"message":"<h1 id=\"oracle-select-ai-setting-up-the-environment\">Oracle Select AI: Setting up the environment<\/h1>\n<p>This notebook prepares your Autonomous Database instance to use Oracle Select AI.<\/p>\n<p>This setup must be completed before using NL2SQL, RAG, Synthetic Data Generation, and other Select AI features.<\/p>\n<h4 id=\"what-is-select-ai\">What is Select AI?<\/h4>\n<p>Select AI enables SQL and PL/SQL access to Large Language Models (LLMs) and transformers from a range of AI providers.\nIt supports SQL query generation from natural language prompts against your database data (NL2SQL), retrieval augmented generation (RAG), and synthetic data generation (SDG), among other features.<\/p>\n<p>See this <a href=\"https://www.youtube.com/watch?v=rnsnIYYZzr4\" target=\"_blank\">video<\/a> for an introduction to Select AI.<\/p>\n<h4 id=\"setup-overview\">Setup Overview<\/h4>\n<p>To configure Select AI, both the ADMIN and a non-ADMIN users are required. You will need to specify access credentials to the AI provider(s) you want to use and, for RAG, cloud storage.<\/p>\n<p><strong>ADMIN Tasks:<\/strong><\/p>\n<ul>\n<li>Grant permissions to OML users<\/li>\n<li>Configure network access (if needed)<\/li>\n<\/ul>\n<p><strong>Select AI User Tasks:<\/strong><\/p>\n<ul>\n<li>Create cloud authentication credentials<\/li>\n<li>Create AI profiles<\/li>\n<li>Create vector indexes as needed in support of RAG<\/li>\n<\/ul>\n<blockquote>\n<p><strong>Note:<\/strong> This notebook includes examples based on OCI Generative AI Service. However  multiple other AI providers are supported.\nUsers must have an account with a compatible AI provider API and provide access to Autonomous Database via database credentials.\nConsult the <a href=\"https://docs.oracle.com/en-us/iaas/autonomous-database-serverless/doc/sql-generation-ai-autonomous.html\" target=\"_blank\">Select AI documentation<\/a> for details.<\/p>\n<\/blockquote>\n<p>Copyright (c) 2025 Oracle Corporation<\/p>\n<h6 id=\"the-universal-permissive-license-upl-version-10\"><a href=\"https://oss.oracle.com/licenses/upl/\" target=\"_blank\">The Universal Permissive License (UPL), Version 1.0<\/a><\/h6>\n<hr />\n","type":"HTML"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":12,"hideResult":false,"dynamicFormParams":"{}","row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":"For more information...","message":["%md","","<a href=\"https://docs.oracle.com/en/cloud/paas/autonomous-database/serverless/adbsb/select-ai-get-started.html#GUID-E9872607-42A6-43FA-9851-7B60430C21B7\" target=\"_blank\">Getting Started with Select AI<\/a>","","<a href=\"https://docs.oracle.com/en/cloud/paas/autonomous-database/serverless/adbsb/select-ai-manage-profiles.html#GUID-842C70F5-A3BE-42A1-B189-64341A9BAAED\" target=\"_blank\">Select AI Prerequisites and Requirements<\/a>","","<a href=\"https://docs.oracle.com/en/database/oracle/oracle-database/23/arpls/DBMS_NETWORK_ACL_ADMIN.html#GUID-2512526D-0B2A-44BF-890D-03B5BBCB8442\" target=\"_blank\">ADMIN: Configure Permissions and Network Access<\/a>","","<a href=\"https://docs.oracle.com/en/database/oracle/oracle-database/23/arpls/dbms_cloud.html#GUID-EC41553D-C4FA-47C1-92BC-BC1A5CD0CF1F\" target=\"_blank\">OML User: Create Cloud Credentials<\/a>","","<a href=\"https://docs.oracle.com/en/database/oracle/oracle-database/23/arpls/dbms_cloud_ai1.html#GUID-ED604770-5A80-4335-8AB7-202E2A1969FB\" target=\"_blank\">OML User: Configure and Activate AI Profile<\/a>"],"enabled":true,"result":{"startTime":1759422048654,"interpreter":"md.low","endTime":1759422049014,"results":[{"message":"<p><a href=\"https://docs.oracle.com/en/cloud/paas/autonomous-database/serverless/adbsb/select-ai-get-started.html#GUID-E9872607-42A6-43FA-9851-7B60430C21B7\" target=\"_blank\">Getting Started with Select AI<\/a><\/p>\n<p><a href=\"https://docs.oracle.com/en/cloud/paas/autonomous-database/serverless/adbsb/select-ai-manage-profiles.html#GUID-842C70F5-A3BE-42A1-B189-64341A9BAAED\" target=\"_blank\">Select AI Prerequisites and Requirements<\/a><\/p>\n<p><a href=\"https://docs.oracle.com/en/database/oracle/oracle-database/23/arpls/DBMS_NETWORK_ACL_ADMIN.html#GUID-2512526D-0B2A-44BF-890D-03B5BBCB8442\" target=\"_blank\">ADMIN: Configure Permissions and Network Access<\/a><\/p>\n<p><a href=\"https://docs.oracle.com/en/database/oracle/oracle-database/23/arpls/dbms_cloud.html#GUID-EC41553D-C4FA-47C1-92BC-BC1A5CD0CF1F\" target=\"_blank\">OML User: Create Cloud Credentials<\/a><\/p>\n<p><a href=\"https://docs.oracle.com/en/database/oracle/oracle-database/23/arpls/dbms_cloud_ai1.html#GUID-ED604770-5A80-4335-8AB7-202E2A1969FB\" target=\"_blank\">OML User: Configure and Activate AI Profile<\/a><\/p>\n","type":"HTML"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":12,"hideResult":false,"dynamicFormParams":"{}","row":0,"hasTitle":true,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":"ADMIN: Grant 'EXECUTE' on DBMS_CLOUD packages","message":["%md","","Grant the `EXECUTE` privilege on the `DBMS_CLOUD` package to users who need to access Oracle Cloud services and create credentials. ","The `EXECUTE` privilege is also required for `DBMS_CLOUD_AI` for Select AI functionality. ","Additionally, the `DBMS_CLOUD_PIPELINE` privilege is required for SELECT AI RAG. However, OML users in OML Notebooks already have this privilege through `DWROLE`.","","The ADMIN user must grant these privileges to Select AI users. Replace `SELECT_AI_USER` with your username.","","    GRANT EXECUTE ON DBMS_CLOUD to SELECT_AI_USER;","    GRANT EXECUTE ON DBMS_CLOUD_AI to SELECT_AI_USER;","    GRANT EXECUTE ON DBMS_CLOUD_PIPELINE to SELECT_AI_USER;"],"enabled":true,"result":{"startTime":1759422051308,"interpreter":"md.low","endTime":1759422051657,"results":[{"message":"<p>Grant the <code>EXECUTE<\/code> privilege on the <code>DBMS_CLOUD<\/code> package to users who need to access Oracle Cloud services and create credentials.\nThe <code>EXECUTE<\/code> privilege is also required for <code>DBMS_CLOUD_AI<\/code> for Select AI functionality.\nAdditionally, the <code>DBMS_CLOUD_PIPELINE<\/code> privilege is required for SELECT AI RAG. However, OML users in OML Notebooks already have this privilege through <code>DWROLE<\/code>.<\/p>\n<p>The ADMIN user must grant these privileges to Select AI users. Replace <code>SELECT_AI_USER<\/code> with your username.<\/p>\n<pre><code>GRANT EXECUTE ON DBMS_CLOUD to SELECT_AI_USER;\nGRANT EXECUTE ON DBMS_CLOUD_AI to SELECT_AI_USER;\nGRANT EXECUTE ON DBMS_CLOUD_PIPELINE to SELECT_AI_USER;\n<\/code><\/pre>\n","type":"HTML"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":12,"hideResult":false,"dynamicFormParams":"{}","row":0,"hasTitle":true,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":"ADMIN: Configure network access to Oracle Cloud Services","message":["%md","","The ADMIN user must also configure network access control to allow Select AI users to access Oracle Cloud services. ","","This step is not needed if using the OCI Generative AI Service. ","","As before, replace `SELECT_AI_USER` with your username.","","    BEGIN  ","        DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(","            host => '*.oraclecloud.com',","            ace  => xs$ace_type(privilege_list => xs$name_list('http'),","                           principal_name => 'SELECT_AI_USER',  ","                           principal_type => xs_acl.ptype_db)","     );","    END;"],"enabled":true,"result":{"startTime":1759422164607,"interpreter":"md.low","endTime":1759422164956,"results":[{"message":"<p>The ADMIN user must also configure network access control to allow Select AI users to access Oracle Cloud services.<\/p>\n<p>This step is not needed if using the OCI Generative AI Service.<\/p>\n<p>As before, replace <code>SELECT_AI_USER<\/code> with your username.<\/p>\n<pre><code>BEGIN  \n    DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(\n        host =&gt; '*.oraclecloud.com',\n        ace  =&gt; xs$ace_type(privilege_list =&gt; xs$name_list('http'),\n                       principal_name =&gt; 'SELECT_AI_USER',  \n                       principal_type =&gt; xs_acl.ptype_db)\n );\nEND;\n<\/code><\/pre>\n","type":"HTML"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":12,"hideResult":false,"dynamicFormParams":"{}","row":0,"hasTitle":true,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":null,"message":["%md","","### Select AI User: Create cloud authentication credential","","The Select AI user must establish authentication credentials to their AI provider. Here, we show using Oracle Cloud Infrastructure (OCI) services.","","You use the `DBMS_CLOUD.create_credential` procedure. This securely stores your OCI identity information (user OCID, tenancy OCID, private key, and API key fingerprint). ","","The parameters include:","- `credential_name`: A unique identifier for this set of credentials (\"OCI_CRED\" in this example)","- `user_ocid`: Your OCI user's unique identifier","- `tenancy_ocid`: Your OCI tenancy's unique identifier ","- `private_key`: Your RSA private key (in PEM format) that authenticates API requests","- `fingerprint`: The fingerprint of the public key uploaded to your OCI user account","","This credential can be referenced in subsequent operations that require OCI authentication, such as when accessing Cloud Object Storage buckets for data loading, ","external tables, or data movement operations. For Select AI, this enables secure access to your data stored in OCI Cloud Storage via a built-in automated pipeline.","","This is a standard pattern for setting up secure API access to Oracle Cloud services from within an Oracle Database. The credentials are stored securely within the ","database and can be managed using various `DBMS_CLOUD` procedures.","",">Note: Replace all placeholder values with your specific values."],"enabled":true,"result":{"startTime":1759422181182,"interpreter":"md.low","endTime":1759422181540,"results":[{"message":"<h3 id=\"select-ai-user-create-cloud-authentication-credential\">Select AI User: Create cloud authentication credential<\/h3>\n<p>The Select AI user must establish authentication credentials to their AI provider. Here, we show using Oracle Cloud Infrastructure (OCI) services.<\/p>\n<p>You use the <code>DBMS_CLOUD.create_credential<\/code> procedure. This securely stores your OCI identity information (user OCID, tenancy OCID, private key, and API key fingerprint).<\/p>\n<p>The parameters include:<\/p>\n<ul>\n<li><code>credential_name<\/code>: A unique identifier for this set of credentials (&quot;OCI_CRED&quot; in this example)<\/li>\n<li><code>user_ocid<\/code>: Your OCI user's unique identifier<\/li>\n<li><code>tenancy_ocid<\/code>: Your OCI tenancy's unique identifier<\/li>\n<li><code>private_key<\/code>: Your RSA private key (in PEM format) that authenticates API requests<\/li>\n<li><code>fingerprint<\/code>: The fingerprint of the public key uploaded to your OCI user account<\/li>\n<\/ul>\n<p>This credential can be referenced in subsequent operations that require OCI authentication, such as when accessing Cloud Object Storage buckets for data loading,\nexternal tables, or data movement operations. For Select AI, this enables secure access to your data stored in OCI Cloud Storage via a built-in automated pipeline.<\/p>\n<p>This is a standard pattern for setting up secure API access to Oracle Cloud services from within an Oracle Database. The credentials are stored securely within the\ndatabase and can be managed using various <code>DBMS_CLOUD<\/code> procedures.<\/p>\n<blockquote>\n<p>Note: Replace all placeholder values with your specific values.<\/p>\n<\/blockquote>\n","type":"HTML"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":12,"hideResult":false,"dynamicFormParams":"{}","row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":null,"title":"Create cloud authentication credential for OCI GenAI Services","message":["%script","","BEGIN","  DBMS_CLOUD.create_credential(","    credential_name => 'OCI_CRED',","    user_ocid       => 'ocid1.user.oc1...',      -- Replace with your user OCID","    tenancy_ocid    => 'ocid1.tenancy.oc1...',   -- Replace with your tenancy OCID","    private_key     => 'MIIEogIBAAK...',         -- Replace with your private key","    fingerprint     => '12:34:56:78:9a:bc:...'   -- Replace with your fingerprint","  );","END;"],"enabled":false,"result":null,"sizeX":0,"hideCode":false,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":null},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Verify the cloud credential is defined","message":["%sql","","SELECT OWNER, CREDENTIAL_NAME, ENABLED FROM ALL_CREDENTIALS WHERE CREDENTIAL_NAME='OCI_CRED';"],"enabled":true,"result":{"startTime":1759424123432,"interpreter":"sql.low","endTime":1759424123837,"results":[{"message":"OWNER\tCREDENTIAL_NAME\tENABLED\nSELECT_AI_USER\tOCIAI_CRED\tTRUE\n","type":"TABLE"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":12,"hideResult":false,"dynamicFormParams":"{}","row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":null,"message":["%md","","### AI Profile Overview","","Users can create different profiles for various Select AI scenarios, such as using different credentials, AI providers, LLMs, transformers, or configuration parameters. ","","Here, we configure and activate two profiles: ","","1. **OCI_GENAI**: A primary profile for chat, NL2SQL, and SDG","2. **OCI_GENAI_RAG**: A specialized profile for RAG","","After creating each profile, the code sets the newly created profile as the active profile for the current stateful session, making it the default profile for subsequent Select AI invocations.","","This is included for completeness here, but you will need to set the appropriate profile again when working with the chat, NL2SQL, SDG, and RAG notebooks, as these will be separate database sessions.","","> Note, the annotations feature referenced in each profile requires Autonomous Database 23ai. Select AI RAG also requires 23ai."],"enabled":true,"result":{"startTime":1759422188656,"interpreter":"md.low","endTime":1759422189011,"results":[{"message":"<h3 id=\"ai-profile-overview\">AI Profile Overview<\/h3>\n<p>Users can create different profiles for various Select AI scenarios, such as using different credentials, AI providers, LLMs, transformers, or configuration parameters.<\/p>\n<p>Here, we configure and activate two profiles:<\/p>\n<ol>\n<li><strong>OCI_GENAI<\/strong>: A primary profile for chat, NL2SQL, and SDG<\/li>\n<li><strong>OCI_GENAI_RAG<\/strong>: A specialized profile for RAG<\/li>\n<\/ol>\n<p>After creating each profile, the code sets the newly created profile as the active profile for the current stateful session, making it the default profile for subsequent Select AI invocations.<\/p>\n<p>This is included for completeness here, but you will need to set the appropriate profile again when working with the chat, NL2SQL, SDG, and RAG notebooks, as these will be separate database sessions.<\/p>\n<blockquote>\n<p>Note, the annotations feature referenced in each profile requires Autonomous Database 23ai. Select AI RAG also requires 23ai.<\/p>\n<\/blockquote>\n","type":"HTML"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":12,"hideResult":false,"dynamicFormParams":"{}","row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":"Create an AI profile for chat, NL2SQL, and SDG","message":["%md","","We'll create an AI profile named `OCI_GENAI` to support chat interactions, querying the database using natural language, and generating synthetic data.","","This profile uses OCI Generative AI Service as the AI provider and the `meta.llama-3.1-70b-instruct` LLM.","","In this profile, we enable Select AI features including conversation support, comments, constraints, and annotations.","","The object_list in the profile can be specified to determine which database tables the AI features can access. It highlights the owner field (e.g., SH) to specify","the schema. You can optionally list individual tables, as shown here with `CUSTOMERS`, `SALES`, `PRODUCTS`, and `COUNTRIES`.","","With this profile, you'll be able to:","","* Run natural language queries like 'Show me the top 3 baby boomer big spending customers' and have it generate the corresponding SQL query (NL2SQL)","* Engage in interactive chat conversations with the LLM (Chat)","* Generate realistic synthetic data based on your existing table structures and data patterns (SDG)"],"enabled":true,"result":{"startTime":1759422195910,"interpreter":"md.low","endTime":1759422196257,"results":[{"message":"<p>We'll create an AI profile named <code>OCI_GENAI<\/code> to support chat interactions, querying the database using natural language, and generating synthetic data.<\/p>\n<p>This profile uses OCI Generative AI Service as the AI provider and the <code>meta.llama-3.1-70b-instruct<\/code> LLM.<\/p>\n<p>In this profile, we enable Select AI features including conversation support, comments, constraints, and annotations.<\/p>\n<p>The object_list in the profile can be specified to determine which database tables the AI features can access. It highlights the owner field (e.g., SH) to specify\nthe schema. You can optionally list individual tables, as shown here with <code>CUSTOMERS<\/code>, <code>SALES<\/code>, <code>PRODUCTS<\/code>, and <code>COUNTRIES<\/code>.<\/p>\n<p>With this profile, you'll be able to:<\/p>\n<ul>\n<li>Run natural language queries like 'Show me the top 3 baby boomer big spending customers' and have it generate the corresponding SQL query (NL2SQL)<\/li>\n<li>Engage in interactive chat conversations with the LLM (Chat)<\/li>\n<li>Generate realistic synthetic data based on your existing table structures and data patterns (SDG)<\/li>\n<\/ul>\n","type":"HTML"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":0,"hideResult":false,"dynamicFormParams":"{}","row":0,"hasTitle":true,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Select AI user: OCI_GENAI profile definition","message":["%script","","BEGIN","  -- Remove existing profile if it exists","  BEGIN DBMS_CLOUD_AI.drop_profile(profile_name => 'OCI_GENAI');","  EXCEPTION WHEN OTHERS THEN NULL; END;","","-- OCI GenAI uses the default model if \"model\" is not set in attributes ","","  DBMS_CLOUD_AI.create_profile(","    profile_name => 'OCI_GENAI',","    attributes   => '{","      \"provider\": \"oci\",","      \"credential_name\": \"OCI_CRED\",","      \"conversation\": true,","      \"comments\": true,","      \"constraints\": true,","      \"annotations\": true,","      \"object_list\": [","        {\"owner\": \"SH\", \"name\": \"CUSTOMERS\"},","        {\"owner\": \"SH\", \"name\": \"SALES\"},","        {\"owner\": \"SH\", \"name\": \"PRODUCTS\"},","        {\"owner\": \"SH\", \"name\": \"COUNTRIES\"}","      ]","    }',","    description => 'Profile for chat, NL2SQL, and SDG'","  );","","  DBMS_CLOUD_AI.set_profile(profile_name => 'OCI_GENAI');","  DBMS_OUTPUT.put_line('OCI_GENAI profile has been successfully created and set.');","END;"],"enabled":true,"result":{"startTime":1759422225485,"interpreter":"script.low","endTime":1759422226328,"results":[{"message":"BEGIN\n*\nERROR at line 1:\nORA-20004: Credential \"SELECT_AI_USER\".\"OCI_CRED\" does not exist\nORA-06512: at \"C##CLOUD$SERVICE.DBMS_CLOUD$PDBCS_250907_0\", line 2243\nORA-06512: at \"C##CLOUD$SERVICE.DBMS_CLOUD_AI\", line 17404\nORA-06512: at line 8\n\nhttps://docs.oracle.com/error-help/db/ora-20004/\n\n\nMore Details :\nhttps://docs.oracle.com/error-help/db/ora-20004/\nhttps://docs.oracle.com/error-help/db/ora-06512/\n","type":"TEXT"}],"taskStatus":"ERROR","forms":"[]","status":"ERROR"},"sizeX":0,"hideCode":false,"width":0,"hideResult":false,"dynamicFormParams":"{}","row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":null,"message":["%md","","### Create an AI profile for RAG","","We'll create an AI profile named `OCI_GENAI_RAG` that also uses OCI Generative AI Service as the provider and the same LLM. ","But we also can specify the embedding model (or transformer) we want to use for generating vectors. ","","Additionally, we'll specify a vector index named `MY_VECTOR_INDEX` to enable RAG. This profile will be used in the corresponding RAG notebook. ","",">Note, we haven't created `MY_VECTOR_INDEX` yet. That will be done in the notebook that covers RAG. ","","In this profile, we enable conversations and set it as the current profile after creation. ","","The RAG profile will allow you to ask questions about customer documentation in your vector store, such as 'What are our service level agreements for high-value customers ","in the Asia Pacific region?' and receive answers enhanced with information derived from your object storage documents."],"enabled":true,"result":null,"sizeX":0,"hideCode":true,"width":0,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Select AI user: OCI_GENAI_RAG profile definition","message":["%script","","BEGIN","  BEGIN DBMS_CLOUD_AI.drop_profile(profile_name => 'OCI_GENAI_RAG'); ","  EXCEPTION WHEN OTHERS THEN NULL; END;","","  DBMS_CLOUD_AI.create_profile(","      profile_name => 'OCI_GENAI_RAG',","      attributes   => '{","          \"provider\": \"oci\",","          \"model\": \"meta.llama-4-maverick-17b-128e-instruct-fp8\",","          \"embedding_model\": \"cohere.embed-english-v3.0\",","          \"credential_name\": \"OCI_CRED\",","          \"vector_index_name\": \"MY_VECTOR_INDEX\",","          \"enable_source_offsets\": true,","          \"conversation\": true","      }',","      description  => 'Supports RAG with semantic search to augment prompts using a vector store'","  ); ","","  DBMS_CLOUD_AI.set_profile(profile_name => 'OCI_GENAI_RAG');","  DBMS_OUTPUT.put_line('OCI_GENAI_RAG has been successfully created and set.');","END;"],"enabled":true,"result":{"startTime":1760118381668,"interpreter":"script.low","endTime":1760118384642,"results":[{"message":"OCI_GENAI_RAG has been successfully created and set.\n\n\nPL/SQL procedure successfully completed.\n\n","type":"TEXT"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":12,"hideResult":false,"dynamicFormParams":"{}","row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Select AI user: OCI_GENAI_RAG2 profile definition","message":["%script","","BEGIN","  BEGIN DBMS_CLOUD_AI.drop_profile(profile_name => 'OCI_GENAI_RAG2'); ","  EXCEPTION WHEN OTHERS THEN NULL; END;","","  DBMS_CLOUD_AI.create_profile(","      profile_name => 'OCI_GENAI_RAG2',","      attributes   => '{","          \"provider\": \"oci\",","          \"model\": \"meta.llama-4-maverick-17b-128e-instruct-fp8\",","          \"embedding_model\": \"database:MULTILINGUAL_E5_SMALL\",","          \"credential_name\": \"OCI_CRED\",","          \"vector_index_name\": \"MY_VECTOR_INDEX2\",","          \"enable_source_offsets\": true,","          \"conversation\": true","      }',","      description  => 'Supports RAG with semantic search to augment prompts using a vector store using in-database transformer'","  ); ","","  DBMS_CLOUD_AI.set_profile(profile_name => 'OCI_GENAI_RAG2');","  DBMS_OUTPUT.put_line('OCI_GENAI_RAG2 has been successfully created and set.');","END;"],"enabled":true,"result":{"startTime":1760118486072,"interpreter":"script.low","endTime":1760118487247,"results":[{"message":"OCI_GENAI_RAG2 has been successfully created and set.\n\n\nPL/SQL procedure successfully completed.\n\n","type":"TEXT"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":12,"hideResult":false,"dynamicFormParams":"{}","row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":null,"message":["%md","","#### Transformer model selection ","The `embedding_model` parameter allows you to choose between different text embedding models and deployment approaches. You can use third-party hosted transformer ","models from providers like OpenAI or Cohere, or use in-database transformers as described below.","","For example, `ALL_MINILM_L12_V2` and `MULTILINGUAL_E5_SMALL` are Hugging Face models that have been augmented through a specialized pipeline and converted to ONNX ","format. This enables them to generate embeddings directly within the database.","","Each model offers different capabilities and embedding dimensions. Third-party hosted models may provide specialized capabilities or larger embedding dimensions, while ","in-database models like `ALL_MINILM_L12_V2` are optimized for English content (384 dimensions) and `MULTILINGUAL_E5_SMALL` supports 100+ languages (384 dimensions).","You can switch the active embedding model at any time by updating the profile with `DBMS_CLOUD_AI.SET_ATTRIBUTE`, as demonstrated below in this section.","","In the following steps, we'll load these embedding models into the database. For additional information on these models, refer to these blogs:","","<a href=\"https://blogs.oracle.com/machinelearning/post/use-our-prebuilt-onnx-model-now-available-for-embedding-generation-in-oracle-database-23ai\" target=\"_blank\">Prebuilt ONNX Models for Embedding Generation<\/a>","<br>","<a href=\"https://blogs.oracle.com/machinelearning/post/enhance-your-semantic-similarity-search-with-multilingual-support\" target=\"_blank\">Multilingual Support for Semantic Similarity Search<\/a>"],"enabled":true,"result":{"startTime":1759423651112,"interpreter":"md.low","endTime":1759423651456,"results":[{"message":"<h4 id=\"transformer-model-selection\">Transformer model selection<\/h4>\n<p>The <code>embedding_model<\/code> parameter allows you to choose between different text embedding models and deployment approaches. You can use third-party hosted transformer\nmodels from providers like OpenAI or Cohere, or use in-database transformers as described below.<\/p>\n<p>For example, <code>ALL_MINILM_L12_V2<\/code> and <code>MULTILINGUAL_E5_SMALL<\/code> are Hugging Face models that have been augmented through a specialized pipeline and converted to ONNX\nformat. This enables them to generate embeddings directly within the database.<\/p>\n<p>Each model offers different capabilities and embedding dimensions. Third-party hosted models may provide specialized capabilities or larger embedding dimensions, while\nin-database models like <code>ALL_MINILM_L12_V2<\/code> are optimized for English content (384 dimensions) and <code>MULTILINGUAL_E5_SMALL<\/code> supports 100+ languages (384 dimensions).\nYou can switch the active embedding model at any time by updating the profile with <code>DBMS_CLOUD_AI.SET_ATTRIBUTE<\/code>, as demonstrated below in this section.<\/p>\n<p>In the following steps, we'll load these embedding models into the database. For additional information on these models, refer to these blogs:<\/p>\n<p><a href=\"https://blogs.oracle.com/machinelearning/post/use-our-prebuilt-onnx-model-now-available-for-embedding-generation-in-oracle-database-23ai\" target=\"_blank\">Prebuilt ONNX Models for Embedding Generation<\/a>\n<br>\n<a href=\"https://blogs.oracle.com/machinelearning/post/enhance-your-semantic-similarity-search-with-multilingual-support\" target=\"_blank\">Multilingual Support for Semantic Similarity Search<\/a><\/p>\n","type":"HTML"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":true,"width":0,"hideResult":false,"dynamicFormParams":"{}","row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Load Hugging Face all-MiniLM-L12-v2 model","message":["%script","","DECLARE ","    ONNX_MOD_FILE VARCHAR2(100) := 'all_MiniLM_L12_v2.onnx';","    MODNAME VARCHAR2(500);","    LOCATION_URI VARCHAR2(200) := 'https://adwc4pm.objectstorage.us-ashburn-1.oci.customer-oci.com/p/eLddQappgBJ7jNi6Guz9m9LOtYe2u8LWY19GfgU8flFK4N9YgP4kTlrE9Px3pE12/n/adwc4pm/b/OML-Resources/o/';","","BEGIN","--------------------------------------------","-- Define a model name for the loaded model","--------------------------------------------","    SELECT UPPER(REGEXP_SUBSTR(ONNX_MOD_FILE, '[^.]+')) INTO MODNAME from dual;","","-----------------------------------------------------","-- Read the ONNX model file from Object Storage into ","-- the Autonomous Database data pump directory","-----------------------------------------------------","","BEGIN DBMS_DATA_MINING.DROP_MODEL(model_name => MODNAME);","EXCEPTION WHEN OTHERS THEN NULL; END;","","    DBMS_CLOUD.GET_OBJECT(                            ","        credential_name => 'OCI_CRED',","        directory_name  => 'DATA_PUMP_DIR',","        object_uri      => LOCATION_URI || ONNX_MOD_FILE);","","-----------------------------------------","-- Load the ONNX model to the database","-----------------------------------------                   ","","    DBMS_VECTOR.LOAD_ONNX_MODEL(","        directory  => 'DATA_PUMP_DIR',","        file_name  => ONNX_MOD_FILE,","        model_name => MODNAME);","","    DBMS_OUTPUT.PUT_LINE(MODNAME || ' model successfully loaded');","END;"],"enabled":true,"result":null,"sizeX":0,"hideCode":false,"width":0,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"raw","title":"Load Hugging Face multilingual-e5-small model","message":["%script","","DECLARE","  ONNX_MOD_FILE VARCHAR2(100) := 'multilingual_e5_small.onnx';","  MODNAME VARCHAR2(500);","  LOCATION_URI VARCHAR2(200) := 'https://adwc4pm.objectstorage.us-ashburn-1.oci.customer-oci.com/p/mbFT6Y4-cDFZr86_BlvZJA8CUiIzFmOCxN7m627gr3DWbksfgTzxf9HBREVgTvn1/n/adwc4pm/b/OML-Resources/o/';","","BEGIN","--------------------------------------------","-- Define a model name for the loaded model","--------------------------------------------","","  SELECT UPPER(REGEXP_SUBSTR(ONNX_MOD_FILE, '[^.]+')) INTO MODNAME from dual;","----------------------------------------------------","-- Read the ONNX model file from Object Storage into","-- the Autonomous Database data pump directory","-----------------------------------------------------","","BEGIN DBMS_DATA_MINING.DROP_MODEL(model_name => MODNAME);"," EXCEPTION WHEN OTHERS THEN NULL; END;","","    DBMS_CLOUD.GET_OBJECT(","      credential_name => 'OCI_CRED',","      directory_name  => 'DATA_PUMP_DIR',","      object_uri      => LOCATION_URI || ONNX_MOD_FILE);","","-----------------------------------------","-- Load the ONNX model to the database","-----------------------------------------                   ","","    DBMS_VECTOR.LOAD_ONNX_MODEL(","      directory  => 'DATA_PUMP_DIR',","      file_name  => ONNX_MOD_FILE,","      model_name => MODNAME);","","    DBMS_OUTPUT.PUT_LINE(MODNAME || ' model successfully loaded');","END;"],"enabled":true,"result":{"startTime":1760118452536,"interpreter":"script.low","endTime":1760118464517,"results":[{"message":"MULTILINGUAL_E5_SMALL model successfully loaded\n\n\nPL/SQL procedure successfully completed.\n\n","type":"TEXT"}],"taskStatus":"SUCCESS","forms":"[]","status":"SUCCESS"},"sizeX":0,"hideCode":false,"width":0,"hideResult":false,"dynamicFormParams":"{}","row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"List imported transformer models available for use with in-database ONNX Runtime","message":["%sql","","select MODEL_NAME, ALGORITHM","from   user_mining_models","where  mining_function = 'EMBEDDING';"],"enabled":true,"result":null,"sizeX":0,"hideCode":false,"width":0,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":null,"title":"Optionally specify in-database transformer","message":["%script","","BEGIN"," DBMS_CLOUD_AI.SET_ATTRIBUTE(profile_name    => 'OCI_GENAI_RAG',","                             attribute_name  => 'embedding_model',","                             attribute_value => 'database:ALL_MINILM_L12_V2'","                       --    attribute_value => 'database:MULTILINGUAL_E5_SMALL'","                             );","END;"],"enabled":false,"result":null,"sizeX":0,"hideCode":false,"width":0,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":null},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"View the profile listings","message":["%sql","","SELECT PROFILE_NAME, STATUS, DESCRIPTION ","FROM USER_CLOUD_AI_PROFILES ","WHERE PROFILE_NAME IN ('OCI_GENAI', 'OCI_GENAI_RAG', 'OCI_GENAI_RAG2');"],"enabled":true,"result":null,"sizeX":0,"hideCode":false,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"table","title":"View profile attributes","message":["%sql","","SELECT a.profile_name, a.status, b.attribute_name, b.attribute_value ","FROM USER_CLOUD_AI_PROFILES a","JOIN USER_CLOUD_AI_PROFILE_ATTRIBUTES b ON a.profile_id = b.profile_id","WHERE a.profile_name IN ('OCI_GENAI','OCI_GENAI_RAG', 'OCI_GENAI_RAG2') ","ORDER BY a.profile_name, b.attribute_name;"],"enabled":true,"result":null,"sizeX":0,"hideCode":false,"width":0,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":true,"hideVizConfig":false,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":null,"message":["%md","","### Next Steps","","After completing this setup, you're ready to use Select AI's features through the following companion notebooks:","","- **Select AI -1- Chat**","- **Select AI -2- Natural Language to SQL (NL2SQL)** ","- **Select AI -3- Retrieval Augmented Generation (RAG)** ","- **Select AI -4- Synthetic Data Generation (SDG)** ","","These notebooks provide step-by-step instructions for using each feature with the profiles you've configured."],"enabled":true,"result":null,"sizeX":0,"hideCode":true,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"},{"col":0,"visualizationConfig":null,"hideInIFrame":false,"selectedVisualization":"html","title":null,"message":["%md","","# End of Script"],"enabled":true,"result":null,"sizeX":0,"hideCode":true,"width":12,"hideResult":false,"dynamicFormParams":null,"row":0,"hasTitle":false,"hideVizConfig":true,"hideGutter":true,"relations":[],"forms":"[]"}],"version":"7","snapshot":false,"tags":null}]