In this article, we will learn how to generate SQLs or chat using #Cohere AI or #OpenAI with Oracle # SelectAI to respond to users' input prompts. DBMS_CLOUD_AI package facilitates and configures the translation of natural language prompts to SQL statements. Oracle #APEX helps create a user interface for accepting user inputs and displaying AI results. Use Oracle Machine Learning #OML notebooks to run some of the Select AI SQLs.
You have access to the following:
- Oracle Autonomous Database with ADMIN user access
- API Keys from Cohere - please check their terms and conditions for usage. Cohere - Developer Guide
- Oracle APEX workspace (Optional) or any non-ADMIN Database USER
- APEX username (<APEX_USERNAME>) is the same as schema name (Optional)
Access Autonomous Database and Setup Grants by running the following commands
-- Login as ADMIN User
grant execute on DBMS_CLOUD to <APEX_USERNAME>;
grant execute on DBMS_CLOUD_AI to <APEX_USERNAME>;
-- replace <APEX_USERNAME> with your APEX Workspace Schema USERNAME for example
-- grant execute on DBMS_CLOUD to DEMOUSER;
_ Important __ : Ensure that you select High as a connection Consumer group. You might have issues if you select other Consumer groups. _
Click on the green run button to execute these commands.
We would need this to be done so that the Database can communicate with api.cohere.ai
BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => 'api.cohere.ai',
ace => xs$ace_type(privilege_list => xs$name_list('http'),
principal_name => '<APEX_USERNAME>',
principal_type => xs_acl.ptype_db)
);
END;
/
-- replace <APEX_USERNAME> with your APEX Workspace Schema USERNAME
BEGIN
DBMS_CLOUD.create_credential('COHERE_CRED', 'COHERE', '<Key>');
END;
-- replace <Key> with your Cohere API key
BEGIN
DBMS_CLOUD_AI.create_profile(
'COHERE',
'{"provider": "cohere",
"credential_name": "COHERE_CRED",
"object_list": [{"owner": "<APEX_USERNAME>", "name": "<USER_TABLE1>"},
{"owner": "<APEX_USERNAME>", "name": "<USER_TABLE2>"} ]
}');
end;
/
-- for example in the below create profile, we have two tables
-- RBANK_CUSTOMERS and US_HOSPITALS
-- DEMOUSER is APEX_USERNAME
BEGIN
DBMS_CLOUD_AI.create_profile(
'COHERE',
'{"provider": "cohere",
"credential_name": "COHERE_CRED",
"object_list": [{"owner": "DEMOUSER", "name": "RBANK_CUSTOMERS"},
{"owner": "DEMOUSER", "name": "US_HOSPITALS"} ]
}');
end;
/
As an ADMIN user, set a profile, log out as ADMIN and back in as DEMOUSER ( the APEX USER in SQL Developer), and set your profile there.
Ensure that Consume group HIGH is selected
EXEC DBMS_CLOUD_AI.set_profile('COHERE');
SELECT DBMS_CLOUD_AI.GENERATE(prompt => 'what is the total number of customers',
profile_name => 'COHERE',
action => 'showsql') as query
FROM dual;
SELECT DBMS_CLOUD_AI.GENERATE(prompt => 'show the customers who are buying Shoes',
profile_name => 'COHERE',
action => 'showsql') as query
FROM dual;
At this stage, it is important to note that action showsql will just return SQL after natural language processing of the prompt.
Our application logic should process iteration through the SQL to display the result set. Low Code Oracle APEX makes it easy to build such applications.
What about other actions supported by DBMS_CLOUD_AI.GENERATE?
We can use chat or narrate.
If we pass the action as chat, then there is no need for a corresponding database table in our profile.
SELECT DBMS_CLOUD_AI.GENERATE(prompt => 'what is breast cancer?',
profile_name => 'COHERE',
action => 'chat') as chat
FROM dual;
-- chat output
Breast cancer is a type of cancer that occurs in the cells of the breasts. It can affect both men and women, although it is much more common in women.
Breast cancer happens when cells in the breast begin to grow out of control. These cells usually form a tumor that can often be seen on an x-ray or felt as a lump. Breast cancer tumors can spread to other parts of the body through the blood and lymph systems. This is why it is important to get medical attention as soon as possible.
There are many factors that can increase the risk of developing breast cancer, including genetics, gender, and age. Additionally, certain lifestyle factors such as smoking, heavy alcohol consumption, and being overweight or obese can also increase the risk of developing breast cancer.
Breast cancer is the most common cancer among women in the United States, after skin cancer. It is important to note that while breast cancer can be fatal, it is often treatable with surgery, radiation therapy, chemotherapy, or hormone therapy. There are also many ways to lower your risk of developing breast cancer, including avoiding harmful lifestyle habits, staying at a healthy weight, getting regular exercise, and eating a balanced diet.
If you have any concerns or notice any changes in your breasts, such as lumps or discoloration, it is important to consult with a healthcare professional as soon as possible.
Would you like me to go into more detail about breast cancer?
Narrate example: The Narrate option provides the SQL and AI narration around the generated SQL, for example.
SELECT DBMS_CLOUD_AI.GENERATE
(prompt => 'name the customers who are married',
profile_name => 'COHERE',
action => 'narrate') as narrate
FROM dual;
This would generate following output.
To name the customers who are married in the "DEMOUSER"."RBANK_CUSTOMERS" table,
you can use the following SQL query:
```sql
SELECT NAME
FROM DEMOUSER.RBANK_CUSTOMERS
WHERE MARITAL_STATUS = 'Married';
```
This query will select the names of customers from the `"RBANK_CUSTOMERS"` table whose `"MARITAL_STATUS"` column value is equal to 'Married'. The results will include the names of married customers stored in the `"NAME"` column.
Would you like to know more about the query or anything specific?
How do we create a simple user interface for this chat interface in Oracle APEX?
Step 1: Create a table to save user prompts and corresponding SQLs or Chat that is generated from Cohere AI
-- DDL output
CREATE TABLE "SQLS"
( "ID" NUMBER GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 99999 INCREMENT BY 1 START WITH 5 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE NOT NULL ENABLE,
"QUERY" VARCHAR2(4000),
"PROMPT" VARCHAR2(400),
"ACTION" VARCHAR2(20),
"IS_VALID" VARCHAR2(1),
PRIMARY KEY ("ID")
USING INDEX ENABLE
) ;
Now, create an APEX page item and PL/SQL region on an APEX page to save these into the above table.
DECLARE
l_input varchar2(4000) := :P59_INPUT;
l_action varchar2(4000) := :P59_ACTION;
l_qry varchar2(4000);
CURSOR C1 IS
SELECT DBMS_CLOUD_AI.GENERATE(prompt => l_input,
profile_name => 'COHERE',
action => l_action ) as qry
FROM dual;
BEGIN
if l_input is not null then
For row_1 In C1 Loop
l_qry := row_1.qry;
End Loop;
insert into SQLS (QUERY, PROMPT, ACTION)
values (l_qry, l_input, l_action);
end if;
END;
Create and Interactive Grid from SQLs table and once you click on a menu item run the SQL or show narration.
Create a popup page that takes a prompt id and, based on the select query generated it, displays the table data.
My PL/SQL Dynamic Content code in a popup region to run the Dynamic SQL and display results
-- PL/SQL Dynamic Content
-- prompt id :P60_PROMPTID is passed from main page to popup window
-- feel free to update this code block.
-- Author Madhusudhan Rao
DECLARE
l_qry varchar2(4000);
l_prompt varchar2(4000);
l_action varchar2(400);
cur1 SYS_REFCURSOR;
cursor c1 is select query, action, prompt from sqls where id = :P60_PROMPTID;
TYPE myrec IS RECORD
(
v_name VARCHAR(100)
);
myrecord myrec;
BEGIN
For row_1 In C1 Loop
l_qry := row_1.query;
l_action := row_1.action;
l_prompt := row_1.prompt;
End Loop;
Htp.p('<b>action: </b>' || l_action);
Htp.p('<br/>');
Htp.p('<b>Prompt: </b>' || l_prompt);
Htp.p('<br/>');
Htp.p('<b>SQL: </b>' || l_qry);
if l_action = 'showsql' AND l_qry like 'SELECT%' then
Htp.p('<br/><br/>');
OPEN cur1 FOR l_qry;
Htp.p('<table>');
LOOP
FETCH cur1 INTO myrecord;
Htp.p('<tr><td> '||myrecord.v_name||' <td><tr>');
EXIT WHEN cur1%NOTFOUND;
END LOOP;
Htp.p('</table>');
CLOSE cur1;
end if;
END;
Another input prompt
...
CURSOR C1 IS
SELECT DBMS_CLOUD_AI.GENERATE
(prompt => 'show address of hospitals in OREM',
profile_name => 'COHERE',
action => showsql ) as qry
FROM dual;
..
...
CURSOR C1 IS
SELECT DBMS_CLOUD_AI.GENERATE
(prompt => 'list the benefits of fruit and vegetables',
profile_name => 'COHERE',
action => 'chat' ) as qry
FROM dual;
..
prompt: list the benefits of fruit and vegetables
action: chat
Alternatively, you can use Oracle APEX Interactive Grid layout from a PL/SQL function returning an SQL Query
Note: _ You cannot run PL/SQL statements, DDL statements, or __ DML statements using the AI keyword __ . _
Important: 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.
You can use the AI keyword in a query with Oracle clients such as
SQL Developer, OML Notebooks, and third-party tools, to interact with database in natural language.
You cannot run PL/SQL statements, DDL statements, or DML statements using the AI keyword.
Running Select AI in Oracle Machine Learning Notebooks
Step 1: Login to Oracle Machine Learning (from Oracle Autonomous Database Tools Configuration Tab), Click on Notebooks
%script
EXEC DBMS_CLOUD_AI.set_profile('COHERE');
%sql
Select AI show the customers who are buying Shoes
%sql
Select AI list all the customers who are married
%sql
Select AI list all the customers, what they are buying and price
Click on various chart icons to change visualisation
-
ORA-20000: ORA-24247: Network access denied by access control list (ACL)
ORA-20000: ORA-24247: Network access denied by access control list (ACL) ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 1890 ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD_AI", line 3387 ORA-06512: at line 2 Error at Line: 7 Column: 0Solution:_ _ ensure that DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE has been run as an ADMIN user
BEGIN DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE( host => 'api.cohere.ai', ace => xs$ace_type(privilege_list => xs$name_list('http'), principal_name => '<APEX_USERNAME>', principal_type => xs_acl.ptype_db) ); END; / -
Prompts are not returning SQL query
SELECT DBMS_CLOUD_AI.GENERATE (prompt => 'list the benefits of fruit and vegetables', profile_name => 'COHERE', action => 'showsql' ) as qry FROM dual; -- returns just "list the benefits of fruit and vegetables" and no SQLSolution: Check if Consumer Group is set to HIGH and the profile is set active
EXEC DBMS_CLOUD_AI.set_profile('COHERE'); -
DBMS_CLOUD_AI.GENERATE are giving errors
Solution: Check if USER has grants provided by ADMIN and API Key has not expired
grant execute on DBMS_CLOUD to DEMOUSER; grant execute on DBMS_CLOUD_AI to DEMOUSER; EXEC DBMS_CLOUD.create_credential ('COHERE_CRED', 'COHERE', 'YourKey'); -
ORA-00923 : FROM keyword not found where expected 00923. 00000 -
"FROM keyword not found where expected" Cause: In a SELECT or REVOKE statement, the keyword FROM was either missing, misplaced, or misspelled. The keyword FROM must follow the last selected item in a SELECT statement or the privileges in a REVOKE statement. Action: Correct the syntax. Insert the keyword FROM where appropriate. The SELECT list itself also may be in error. If quotation marks were used in an alias, check that double quotation marks enclose the alias. Also, check to see if a reserved word was used as an alias. Error at Line: 1 Column: 14
Error at line 1/16: ORA-00923: FROM keyword not found where expected
Solution: To run Select AI SQL commands, use Oracle Machine Learning Notebook or SQL Developer Desktop client. Select AI will not work in SQL Database Actions or in Oracle APEX SQL Commands.
