Notes.txt

https://docs.oracle.com/en-us/iaas/autonomous-database-serverless/doc/select-ai-examples.html#GUID-F9C4BEFB-1570-430F-AB36-347F1A4B8A71

https://mylearn.oracle.com/ou/course/oracle-ai-vector-search-fundamentals/140188



#########To Run#########
- uvicorn mcp_server.main:app --reload
- python test_client/chat_with_llm.py

#######This was solved############
- Loading onnx model did not work for nomic-embedding-model 
- Most of the online tutorials are outdated , so it is a pain to make it work
- LOAD_ONNX_MODEL_CLOUD , LOAD_ONNX_MODEL using DATA_PUMP_DIR both did not work
- OML4Py did not work on mac as well dev box. 
########Solution############

Download embedding model from https://adwc4pm.objectstorage.us-ashburn-1.oci.customer-oci.com/p/VBRD9P8ZFWkKvnfhrWxkpPe8K03-JIoM5h_8EJyJcpE80c108fuUjg7R5L5O7mMZ/n/adwc4pm/b/OML-Resources/o/all_MiniLM_L12_v2_augmented.zip

EXEC DBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPAL();

BEGIN
  DBMS_CLOUD.GET_OBJECT(
    credential_name => 'OCI$RESOURCE_PRINCIPAL',
    directory_name  => 'DATA_PUMP_DIR',
    object_uri      => 'https://objectstorage.us-chicago-1.oraclecloud.com/n/axke9xehyrpc/b/ModelBucket/o/all_MiniLM_L12_v2.onnx'
  );
END;
/


BEGIN
  DBMS_VECTOR.LOAD_ONNX_MODEL(
    directory   => 'DATA_PUMP_DIR',
    file_name   => 'all_MiniLM_L12_v2.onnx',
    model_name  => 'all_MiniLM_L12_v2'
    
  );
END;
/

SELECT DBMS_VECTOR.UTL_TO_EMBEDDING(
         'User reported payment failure at checkout',
         JSON('{"provider":"database","model":"all_MiniLM_L12_v2"}')
       ) AS vec
FROM dual;



###################################################


APIUser 
apiuser
fingerprint: 74:3c:f1:24:02:83:54:b5:26:b4:d5:6c:04:ca:c9:8b

[DEFAULT]
user=ocid1.user.oc1..aaaaaaaawbktedi4m5xnqbl3wce32dvszwbcqgvnk53qmjby5mabkxxpimza
fingerprint=74:3c:f1:24:02:83:54:b5:26:b4:d5:6c:04:ca:c9:8b
tenancy=ocid1.tenancy.oc1..aaaaaaaaclhhdny44loq3lqlxmkofnchqq4z3w34q37tjtj24usnmohqbwma
region=us-chicago-1
key_file=/Users/sadanandupase/PycharmProjects/23AI/apiuser-2025-07-04T06_23_01.915Z.pem # TODO


############################################################

Tests
1. what languages are spoken by customers ->  {Result->Succeeds, Generated sql -> SELECT title, content, VECTOR_DISTANCE(embedding, VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING \'languages spoken by customers\' AS data)) AS distance FROM documents ORDER BY distance FETCH FIRST 5 ROWS ONLY,
                                         query response->fetches correct docs : {'results': [{'TITLE': 'Customer languages', 'CONTENT': 'Customers from Mumbai tend to speak in marathi or hindi. while customers from Delhi are mainly speaking in Hindi', 'DISTANCE': 0.4216364701065243}]} }
2. what are occupations of customers -> {Result->Succeeds partially, Generated sql -> SELECT title, content, VECTOR_DISTANCE(embedding, VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING \'occupations of customers\' AS data)) AS distance FROM documents ORDER BY distance FETCH FIRST 5 ROWS ONLY,
                                         query response->fetches documents but they dont satisfy distance criteria {'results': []} }
3. how many different cities do our customers belong to? -> {Result->Succeeds, Generated sql -> SELECT COUNT(DISTINCT city) FROM customers,
                                              query response->gives correct response {'results': [{'COUNT(DISTINCTCITY)': 2}]} }
4. which city customers have complained more ? -> {Result-> Fails, Generated sql -> SELECT c.city, COUNT(*) as complaints_count, VECTOR_DISTANCE(embedding, VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING 'complaints' AS data)) AS distance FROM customers c JOIN documents d ON c.id = d.id GROUP BY c.city ORDER BY distance ASC FETCH FIRST 10 ROWS ONLY,
                                              query response->Error received  : ORA-00979: "EMBEDDING": must appear in the GROUP BY clause or be used in an aggregate function }
5. what all cities do our customers belong to ? -> {Result->Succeeds, Generated sql -> SELECT DISTINCT city FROM customers,
                                              query response->Gives correct result as {'results': [{'CITY': 'Mumbai'}, {'CITY': 'Delhi'}]} }
6. which language speaking customers have complained more ? -> {Result-> Fails, Generated sql -> SELECT title, content, COUNT(*) AS complaints FROM documents WHERE VECTOR_DISTANCE(embedding, VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING 'language spoken by customers' AS data)) < 0.5 GROUP BY title ORDER BY complaints DESC LIMIT 1,
                                               query response->ORA-03049: SQL keyword 'LIMIT' is not syntactically valid following '...ORDER BY complaints DESC }
                                               
                                               
                                               
############################################
Cohere trial key : l9aAr2LQ49u3LhYlE7jWEe7shtfXLRFVF67SRjfR

EXEC DBMS_CLOUD.create_credential('COHERE_CRED', 'COHERE', 'l9aAr2LQ49u3LhYlE7jWEe7shtfXLRFVF67SRjfR');

BEGIN  
    DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
         host => 'api.cohere.ai',
         ace  => xs$ace_type(privilege_list => xs$name_list('http'),
                             principal_name => 'ADMIN',
                             principal_type => xs_acl.ptype_db)
   );
END;
/

BEGIN
  DBMS_CLOUD_AI.create_profile(
      'COHERE',
      '{"provider": "cohere",
        "credential_name": "COHERE_CRED",
        "object_list": [{"owner": "ADMIN", "name":"customers"}, {"owner": "ADMIN", "name":"documents"}],
        "model": "command-light"
       }');
end;
/

BEGIN
     DBMS_CLOUD_AI.ENABLE_PROFILE(
         profile_name    => 'COHERE'
     );
   END;
   /

EXEC DBMS_CLOUD_AI.set_profile('COHERE');

SELECT DBMS_CLOUD_AI.GENERATE(prompt       => 'what languages are spoken by customers',
                              profile_name => 'COHERE',
                              action       => 'runsql')
                              
SELECT DBMS_CLOUD_AI.GENERATE(prompt       => 'how many different cities do our customers belong to',
                              profile_name => 'COHERE',
                              action       => 'runsql')


With this