### Run Benchmark Driver with npm Source: https://github.com/oracle-samples/oracle-db-examples/blob/main/sagas/SagaBenchmark/README.md Starts the benchmark driver application using npm. This will generate a 'report.html' file with the benchmark results. May require running 'npm install' first. ```bash npm start ``` -------------------------------- ### Install python-oracledb Source: https://github.com/oracle-samples/oracle-db-examples/blob/main/python/python-oracledb/notebooks/1-Connection.ipynb Installs the python-oracledb library using pip. Supports standard installation, user-specific installation, and installation via a proxy server. ```bash python3 -m pip install oracledb --upgrade python3 -m pip install oracledb --upgrade --user python3 -m pip install oracledb --upgrade --user --proxy=http://proxy.example.com:80 ``` -------------------------------- ### Oracle PL/SQL: Setup User and Tables for Staging Index Source: https://github.com/oracle-samples/oracle-db-examples/blob/main/text/oracle-text-scripts/itab_homemade/staging_index.sql.txt Sets up the necessary user, grants privileges, and creates the main and staging tables. The staging table includes an 'update_time' column to track changes. ```sql connect system/manager drop user testuser cascade; create user testuser identified by testuser default tablespace users temporary tablespace temp quota unlimited on users; grant connect, resource, ctxapp to testuser; grant execute on ctx_ddl to testuser; connect testuser/testuser -- this is our main table create table main_table (id number primary key, author varchar2(255), text varchar2(4000)); insert into main_table values (1, 'John Smith', 'The quick brown fox jumps over the lazy dog'); -- this is our staging table - identical to the main table but with an update_time column added create table stage_table (id number primary key, author varchar2(255), text varchar2(4000), update_time timestamp); ``` -------------------------------- ### Create and Populate Table in Oracle SQL Source: https://github.com/oracle-samples/oracle-db-examples/blob/main/text/oracle-text-scripts/svm_testcase/match_scores.txt This snippet demonstrates creating a table with a primary key and CLOB data type, then populating it with data from another table using a sequence for the primary key. It assumes the existence of 'test.abstract_seq' and 'test.brain'. ```sql create table test.brain_abstracts_with_pk ( docid number primary key, abstract CLOB ); insert into test.brain_abstracts_with_pk (select test.abstract_seq.nextval, abstract from test.brain); commit; ``` -------------------------------- ### Install Project Dependencies with npm Source: https://github.com/oracle-samples/oracle-db-examples/blob/main/sagas/SagaBenchmark/README.md Installs all the necessary driver dependencies for the project using npm. This command should be run from the root directory of the project. ```bash npm install ``` -------------------------------- ### Load SQL Tuning Set Example (SQL) Source: https://github.com/oracle-samples/oracle-db-examples/blob/main/optimizer/column_groups/README.md Provides an example of how to load a SQL tuning set, which is relevant for cases where queries exceed the maximum VARCHAR2 length and cannot be processed directly by 'sqlid' scripts. ```sql load_sqlset.sql ``` -------------------------------- ### Start Airline Application with Maven Source: https://github.com/oracle-samples/oracle-db-examples/blob/main/sagas/SagaBenchmark/README.md Starts the standalone Airline Java application by executing a Maven command from the airline directory. Assumes the project has been built. ```bash mvn exec:java ``` -------------------------------- ### Create APEX Session for Export Examples (SQL) Source: https://github.com/oracle-samples/oracle-db-examples/blob/main/apex/sample-code/data-export/README.md This snippet demonstrates how to create an APEX session, which is required to run the APEX_DATA_EXPORT examples as REST Resource Handlers. It requires valid application and page IDs. ```sql apex_session.create_session( p_app_id => 101, p_page_id => 1, p_username => 'DUMMY' ); ``` -------------------------------- ### Create Kafka Topic (Windows) Source: https://github.com/oracle-samples/oracle-db-examples/blob/main/txeventq/kafka-connector-example/README.md Command to create a Kafka topic with a specified number of partitions on a Windows environment. Requires Kafka to be installed and running. ```bash .\bin\windows\kafka-topics.bat --create --topic --bootstrap-server localhost:9092 --partitions 10 ``` -------------------------------- ### Start ActiveMQ Broker Source: https://github.com/oracle-samples/oracle-db-examples/blob/main/java/rsi-example/README.md Command to start the Apache ActiveMQ message broker. This involves navigating to the ActiveMQ directory and executing the start script. It logs information about the Java environment and creates a PID file. ```shell #!/bin/bash cd apache-activemq-5.17.2 ./bin/activemq start INFO: Loading '/Users/tinglwang/Downloads/apache-activemq-5.17.2//bin/env' INFO: Using java '/Library/Java/JavaVirtualMachines/jdk-11.0.5.jdk/Contents/Home/bin/java' INFO: Starting - inspect logfiles specified in logging.properties and log4j.properties to get details INFO: pidfile created : '/Users/tinglwang/Downloads/apache-activemq-5.17.2//data/activemq.pid' (pid '61766') ``` -------------------------------- ### Create Kafka Topic (Linux) Source: https://github.com/oracle-samples/oracle-db-examples/blob/main/txeventq/kafka-connector-example/README.md Command to create a Kafka topic with a specified number of partitions on a Linux environment. Requires Kafka to be installed and running. ```bash bin/kafka-topics.sh --create --topic --bootstrap-server localhost:9092 --partitions 10 ``` -------------------------------- ### Start Apache JMeter Source: https://github.com/oracle-samples/oracle-db-examples/blob/main/java/rsi-example/README.md Commands to untar JMeter and start the application. Assumes JMeter is downloaded and extracted. ```shell $ tar -xf apache-jmeter-5.5 $ ./apache-jmeter-5.5/bin/jmeter ``` -------------------------------- ### Connect to Oracle Cloud Database using SQLcl Source: https://github.com/oracle-samples/oracle-db-examples/blob/main/optimizer/autonomous/README.md Demonstrates how to connect to an Oracle Cloud database using SQLcl. It requires a wallet file for cloud configuration and user credentials for authentication. This is a prerequisite for running other examples in this directory. ```sqlcl $ sql /nolog SQL> set cloudconfig wallet_file.zip SQL> connect adwcu1/password@dbname_high ``` -------------------------------- ### Trigger Report via cURL Source: https://github.com/oracle-samples/oracle-db-examples/blob/main/apex/sample-code/data-export/README.md Example command to trigger a report generation process via a POST request using multipart form data. ```bash curl -v -F format=xlsx -F name="John Doe" -F to=example@oracle.com ``` -------------------------------- ### Build and Package Application with Maven Source: https://github.com/oracle-samples/oracle-db-examples/blob/main/sagas/SagaBenchmark/README.md Cleans the project and installs all artifacts using Maven. This command also copies the application properties file and builds/packages each application component. ```bash mvn clean install ``` -------------------------------- ### Email Template Configuration Source: https://github.com/oracle-samples/oracle-db-examples/blob/main/apex/sample-code/data-export/README.md Details of the EMPLOYEES email template, including its subject, HTML content, and plain text version. ```APIDOC ## Email Template: EMPLOYEES ### Description This email template is designed to provide an employee overview. It supports dynamic content using placeholders like `#NAME#`. ### Template Details - **Template Name**: EMPLOYEES - **Static Identifier**: EMPLOYEES - **Email Subject**: Employee overview ### HTML Header ```html Hello #NAME#, ``` ### HTML Body ```html

Hi #NAME#,

Employee overview

Please see the latest employee details in the attached document.

Report Printing

Report Printing in Oracle APEX 20.2 offers lots of options and possibilities.

Happy APEXing!

Copyright © 2020. All rights reserved.

``` ### HTML Footer ```html ``` ### Plain Text Format ```txt Hi #NAME#, Please see the latest employee overview as attached document. ``` ``` -------------------------------- ### Manage Topics using OKafka Administration API Source: https://github.com/oracle-samples/oracle-db-examples/blob/main/txeventq/okafka/Quickstart/README.md Demonstrates how to create or delete topics using the Gradle build system and the OKafka Admin utility. The command requires an action (CREATE/DELETE) followed by a list of topic names. ```cmd gradle Simple:Admin:run Usage: java OKafkaAdminTopic [CREATE|DELETE] topic1 ... topicN ``` ```shell gradle Simple:Admin:run --args="CREATE TOPIC_ADMIN_2 TOPIC_ADMIN_3" ``` -------------------------------- ### Verify Topic Creation via SQL Source: https://github.com/oracle-samples/oracle-db-examples/blob/main/txeventq/okafka/Quickstart/README.md A SQL query to verify the existence and configuration of created topics within the Oracle database. It filters by the specific owner and excludes exception queues. ```sql select name, queue_table, dequeue_enabled,enqueue_enabled, sharded, queue_category, recipients from all_queues where OWNER='OKAFKA_USER' and QUEUE_TYPE<>'EXCEPTION_QUEUE'; ``` -------------------------------- ### Start DRCP Pool via SQL Source: https://github.com/oracle-samples/oracle-db-examples/blob/main/python/python-oracledb/notebooks/1-Connection.ipynb Executes the administrative command to start the Database Resident Connection Pool on the Oracle server instance. ```sql execute dbms_connection_pool.start_pool() ``` -------------------------------- ### Deploy Oracle Database 23ai Container Source: https://github.com/oracle-samples/oracle-db-examples/blob/main/txeventq/okafka/Quickstart/README.md Uses Podman to create a secret for the database password and launch an Oracle Database 23ai Free instance. The container exposes port 1521 for database connections. ```shell # Podman Secrets Support: echo "" | podman secret create oracle_pwd - podman run --name=db23aifree \ --secret=oracle_pwd \ --publish 1521:1521 \ --detach \ container-registry.oracle.com/database/free:latest ``` -------------------------------- ### Configure Database User for OKafka Source: https://github.com/oracle-samples/oracle-db-examples/blob/main/txeventq/okafka/Quickstart/README.md Connects to the database and creates a dedicated user with the required AQ (Advanced Queuing) privileges. These permissions are necessary for the user to manage and interact with Transactional Event Queues. ```sql sql /nolog CONNECT sys/""@localhost:1521/FREEPDB1 as sysdba CREATE user okafka_user identified by ; GRANT resource, connect, unlimited tablespace to okafka_user; GRANT aq_user_role to okafka_user; GRANT EXECUTE on DBMS_AQ to okafka_user; GRANT EXECUTE ON DBMS_AQIN to okafka_user; GRANT EXECUTE on DBMS_AQADM to okafka_user; GRANT SELECT on GV_$SESSION to okafka_user; GRANT SELECT on V_$SESSION to okafka_user; GRANT SELECT on GV_$INSTANCE to okafka_user; GRANT SELECT on GV_$LISTENER_NETWORK to okafka_user; GRANT SELECT on GV_$PDBS to okafka_user; GRANT SELECT on USER_QUEUE_PARTITION_ASSIGNMENT_TABLE to okafka_user; GRANT select on SYS.DBA_RSRC_PLAN_DIRECTIVES to okafka_user; EXEC DBMS_AQADM.GRANT_PRIV_FOR_RM_PLAN('okafka_user'); COMMIT; ``` -------------------------------- ### Configure OKafka Application Properties Source: https://github.com/oracle-samples/oracle-db-examples/blob/main/txeventq/okafka/Quickstart/README.md Defines the configuration required for OKafka applications to connect to the Oracle Database using the PLAINTEXT protocol. This includes setting bootstrap servers, service names, and credentials. ```text security.protocol = "PLAINTEXT" bootstrap.servers = "host:port" oracle.service.name = "name of the service running on the instance" oracle.net.tns_admin = "location of ojdbc.properties file" # ojdbc.properties file content: user(in lowercase)=DatabaseUserName password(in lowercase)=Password ``` -------------------------------- ### Python Connection Pool with Flask (python-oracledb) Source: https://context7.com/oracle-samples/oracle-db-examples/llms.txt Shows how to create and use a connection pool with the python-oracledb driver in a Flask application for improved performance. Includes session callback for connection initialization and demonstrates getting connections from the pool for CRUD operations. ```python import oracledb from flask import Flask POOL_MIN = 4 POOL_MAX = 4 def init_session(connection, requestedTag_ignored): """Session callback to initialize connection state""" with connection.cursor() as cursor: cursor.execute(""" alter session set time_zone = 'UTC' nls_date_format = 'YYYY-MM-DD HH24:MI' """) # Create connection pool with alias for caching oracledb.create_pool( user="myuser", password="mypassword", dsn="localhost:1521/FREEPDB1", min=POOL_MIN, max=POOL_MAX, increment=0, session_callback=init_session, pool_alias="mypool" ) app = Flask(__name__) @app.route("/user/") def get_user(id): # Get connection from pool using alias with oracledb.connect(pool_alias="mypool") as connection: with connection.cursor() as cursor: cursor.execute("SELECT username FROM users WHERE id = :id", [id]) row = cursor.fetchone() return row[0] if row else "User not found" @app.route("/post/") def add_user(username): with oracledb.connect(pool_alias="mypool") as connection: with connection.cursor() as cursor: connection.autocommit = True id_var = cursor.var(int) cursor.execute(""" INSERT INTO users (username) VALUES (:name) RETURNING id INTO :id """, [username, id_var]) return f"Created user {username} with id {id_var.getvalue()[0]}" ``` -------------------------------- ### Build Application with Maven Source: https://github.com/oracle-samples/oracle-db-examples/blob/main/java/jdbc-resource-providers/README.md Builds the Java application using Maven, including cleaning the project and packaging it. This is a standard Maven command for compiling and creating an executable JAR. ```bash mvn clean package ``` -------------------------------- ### Handle Non-Existent Table Drop in Oracle SQL Source: https://github.com/oracle-samples/oracle-db-examples/blob/main/text/oracle-text-scripts/svm_testcase/match_scores.txt This demonstrates attempting to drop tables that may not exist in the 'test' schema. The ORA-00942 error indicates that the specified table or view does not exist, which is a common scenario during script execution where cleanup steps are performed. ```sql drop table test.brain_abstracts_with_pk; drop table test.brain_match_scores; drop table test.match_scores; ``` -------------------------------- ### Connect to Oracle Database using C OCI Source: https://context7.com/oracle-samples/oracle-db-examples/llms.txt Demonstrates how to establish a connection to an Oracle Database using the OCI library, execute a SELECT query, and fetch results. It covers environment initialization, credential setting, statement preparation, and resource cleanup. ```c #include #include #include #include static OCIEnv *envhp = NULL; static OCIError *errhp = NULL; void check_error(OCIError *errhp, sword status) { OraText errbuf[512]; sb4 errcode = 0; if (status == OCI_ERROR) { OCIErrorGet(errhp, 1, NULL, &errcode, errbuf, sizeof(errbuf), OCI_HTYPE_ERROR); printf("Error: %s\n", errbuf); } } int main() { char *username = "myuser"; char *password = "mypassword"; char *connstr = "localhost:1521/FREEPDB1"; OCIAuthInfo *authInfop = NULL; OCISvcCtx *svchp = NULL; OCIStmt *stmthp = NULL; OCIDefine *defnp = NULL; sword empno; char ename[50]; OCIEnvCreate(&envhp, OCI_DEFAULT, NULL, NULL, NULL, NULL, 0, NULL); OCIHandleAlloc(envhp, (void**)&errhp, OCI_HTYPE_ERROR, 0, NULL); OCIHandleAlloc(envhp, (void**)&authInfop, OCI_HTYPE_AUTHINFO, 0, NULL); OCIAttrSet(authInfop, OCI_HTYPE_AUTHINFO, username, strlen(username), OCI_ATTR_USERNAME, errhp); OCIAttrSet(authInfop, OCI_HTYPE_AUTHINFO, password, strlen(password), OCI_ATTR_PASSWORD, errhp); sword status = OCISessionGet(envhp, errhp, &svchp, authInfop, (OraText*)connstr, strlen(connstr), NULL, 0, NULL, 0, FALSE, OCI_DEFAULT); if (status != OCI_SUCCESS) { check_error(errhp, status); return 1; } printf("Connected successfully\n"); OraText *sql = (OraText*)"SELECT employee_id, first_name FROM employees WHERE ROWNUM <= 5"; OCIStmtPrepare2(svchp, &stmthp, errhp, sql, strlen((char*)sql), NULL, 0, OCI_NTV_SYNTAX, OCI_DEFAULT); OCIDefineByPos(stmthp, &defnp, errhp, 1, &empno, sizeof(empno), SQLT_INT, NULL, NULL, NULL, OCI_DEFAULT); OCIDefineByPos(stmthp, &defnp, errhp, 2, ename, sizeof(ename), SQLT_STR, NULL, NULL, NULL, OCI_DEFAULT); OCIStmtExecute(svchp, stmthp, errhp, 0, 0, NULL, NULL, OCI_DEFAULT); printf("Employees:\n"); while (OCIStmtFetch2(stmthp, errhp, 1, OCI_FETCH_NEXT, 0, OCI_DEFAULT) == OCI_SUCCESS) { printf(" ID: %d, Name: %s\n", empno, ename); } OCIStmtRelease(stmthp, errhp, NULL, 0, OCI_DEFAULT); OCISessionRelease(svchp, errhp, NULL, 0, OCI_DEFAULT); OCIHandleFree(errhp, OCI_HTYPE_ERROR); OCIHandleFree(envhp, OCI_HTYPE_ENV); return 0; } ``` -------------------------------- ### JavaScript: User Logout Functionality Source: https://github.com/oracle-samples/oracle-db-examples/blob/main/java/HRWebApp/src/main/webapp/index.html Implements the user logout process by sending an asynchronous HTTP GET request to the server. It uses XMLHttpRequest to communicate with the 'WebController' and redirects the user to the index page upon completion. The 'Authorization' header is set to simulate a logout request. ```javascript function logout() { var xmllogout = new XMLHttpRequest(); xmllogout.open("GET", "WebController?logout=true", true, "_", "_"); xmllogout.withCredentials = true; // Invlalid credentials to fake logout xmllogout.setRequestHeader("Authorization", "Basic 00001"); xmllogout.send(); xmllogout.onreadystatechange = function() { window.location.replace("index.html"); } return true; } ``` -------------------------------- ### Create OKafka Topic Source: https://github.com/oracle-samples/oracle-db-examples/blob/main/txeventq/okafka/Quickstart/README.md Creates a new Transactional Event Queue topic named 'TOPIC_1' with 5 partitions and a 7-day retention period using the DBMS_AQADM package. ```sql CONNECT okafka_user/""@localhost:1521/FREEPDB1 BEGIN dbms_aqadm.create_database_kafka_topic( topicname=> 'TOPIC_1', partition_num=>5, retentiontime => 7*24*3600); END; / ``` -------------------------------- ### Query Max Match Score per Document in Oracle SQL Source: https://github.com/oracle-samples/oracle-db-examples/blob/main/text/oracle-text-scripts/svm_testcase/match_scores.txt This SQL query retrieves document IDs, category names, and their corresponding match scores from the 'brain_match_scores' table. It filters the results to show only the highest match score for each document ID by using a subquery. ```sql select docid, cat_name, match_score from test.brain_match_scores m1 where match_score = (select max(match_score) from test.brain_match_scores m2 where m1.docid = m2.docid) order by 1,2; ``` -------------------------------- ### JavaScript: Role-Based UI Adaptation Source: https://github.com/oracle-samples/oracle-db-examples/blob/main/java/HRWebApp/src/main/webapp/index.html Fetches the user's role from the server via an asynchronous GET request to '/getrole'. Based on the role, it dynamically modifies the UI by hiding elements with the class 'manager' if the role is 'staff'. It also displays the user's role on the page. ```javascript var xmlhttp = new XMLHttpRequest(); var url = "getrole"; xmlhttp.onreadystatechange = function() { if (xmlhttp.readyState == 4 && xmlhttp.status == 200) { role = xmlhttp.responseText; console.log("role: " +role); if (role == "staff") { console.log ("disabling manager"); var x = document.getElementsByClassName('manager'); for(i = 0; i < x.length; ++i) { x[i].style.display = 'none'; } } document.getElementById('myrole').innerHTML = ' '+role+' '; } } xmlhttp.open("GET", url, true); xmlhttp.send(); ``` -------------------------------- ### Create Table with Match Scores in Oracle SQL Source: https://github.com/oracle-samples/oracle-db-examples/blob/main/text/oracle-text-scripts/svm_testcase/match_scores.txt This SQL snippet creates a new table 'brain_match_scores' by selecting distinct document IDs, category names, and calculated match scores. It utilizes the 'match_score' function and joins multiple tables, filtering based on a condition involving 'matches' function. ```sql create table test.brain_match_scores as ( select distinct d.docid, cat_name, match_score(1) match_score from test.restab r, test.testcategory t, test.brain_abstracts_with_pk d where matches(rule, d.abstract ,1)>0 and r.cat_id = t.cat_id ); ``` -------------------------------- ### List Employees using JavaScript XMLHttpRequest Source: https://github.com/oracle-samples/oracle-db-examples/blob/main/java/HRWebApp/src/main/webapp/listAll.html This JavaScript code snippet fetches employee data from a 'WebController' endpoint. It uses XMLHttpRequest to make a GET request and then processes the JSON response to dynamically create an HTML table displaying employee information. The table headers are derived from the keys of the first employee object, and the data is populated row by row. ```javascript var xmlhttp = new XMLHttpRequest(); var url = "WebController"; xmlhttp.onreadystatechange=function() { if (xmlhttp.readyState == 4 && xmlhttp.status == 200) { processResponse(xmlhttp.responseText); } } xmlhttp.open("GET", url, true); xmlhttp.send(); function processResponse(response) { var arr = JSON.parse(response); var i; var out = ""; keys = Object.keys(arr[0]); // Print headers out += "" for(i = 0; i < keys.length; ++i) { out += "" } out += ""; // Print values for(j = 0; j < arr.length; j++) { out += "" for(i = 0; i < keys.length; ++i) { out += "" } out += "" } out += "
"+keys[i]+"
"+arr[j][keys[i]]+"
"; document.getElementById("id-emp").innerHTML = out; } ``` -------------------------------- ### Run Application JAR Source: https://github.com/oracle-samples/oracle-db-examples/blob/main/java/jdbc-resource-providers/README.md Executes the compiled Java application from its JAR file. This command assumes the JAR file is located in the 'target' directory with the name 'java-basic-1.0-SNAPSHOT.jar'. ```bash java -jar target/java-basic-1.0-SNAPSHOT.jar ``` -------------------------------- ### Set Up Target Directory for ONNX Model Source: https://github.com/oracle-samples/oracle-db-examples/blob/main/machine-learning/notebooks-oml/notebooks-classic/python/OfficeHours_Python_Deploy_an_XGBoost_model_in_OML_Services.ipynb Configures the file system by creating a target directory for the ONNX model and changing the current working directory to it. Handles potential directory existence errors. ```python import os home = os.path.expanduser('~') target_folder = os.path.join(home, 'onnx_test' ) try: os.makedirs(target_folder) except: pass os.chdir(target_folder) ``` -------------------------------- ### Configure Oracle JDBC for OCI and Azure Authentication Source: https://github.com/oracle-samples/oracle-db-examples/blob/main/java/jdbc-resource-providers/README.md Demonstrates how to initialize the OracleDataSource without hardcoded credentials and configure the JDBC driver to use external property files for authentication providers. ```java String PASSWORD = System.getenv("ORACLE_PASSWORD"); String USERNAME = System.getenv("ORACLE_USERNAME"); OracleDataSource ods = new OracleDataSource(); ods.setURL("jdbc:oracle:thin:@"); ``` ```bash System.setProperty("oracle.jdbc.config.file", "properties/demo-2.properties"); # Or for Azure configuration System.setProperty("oracle.jdbc.config.file", "properties/demo-3.properties"); ``` -------------------------------- ### Manage Optimizer Statistics and Query Hints Source: https://context7.com/oracle-samples/oracle-db-examples/llms.txt Provides methods for gathering table statistics, creating extended statistics for column correlation, and using SQL hints to influence execution plans. Also includes a procedure for loading SQL plan baselines to ensure query performance stability. ```SQL -- Gather table statistics BEGIN DBMS_STATS.GATHER_TABLE_STATS( ownname => USER, tabname => 'SALES', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE ); END; / -- Create extended statistics for correlated columns SELECT DBMS_STATS.CREATE_EXTENDED_STATS( ownname => USER, tabname => 'SALES', extension => '(PRODUCT_ID, REGION_ID)' ) FROM dual; -- View execution plan with cost estimates EXPLAIN PLAN FOR SELECT /*+ INDEX(s sales_date_ix) */ s.sale_date, s.amount, c.customer_name FROM sales s JOIN customers c ON s.customer_id = c.customer_id WHERE s.sale_date BETWEEN DATE '2024-01-01' AND DATE '2024-12-31' AND s.region_id = 10; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); -- Common optimizer hints SELECT /*+ FULL(employees) */ employee_id, salary FROM employees; SELECT /*+ INDEX(e emp_department_ix) */ employee_id FROM employees e WHERE department_id = 50; SELECT /*+ PARALLEL(sales, 4) */ SUM(amount) FROM sales; SELECT /*+ LEADING(d e) USE_NL(e) */ e.employee_id, d.department_name FROM departments d, employees e WHERE d.department_id = e.department_id; -- SQL Plan Baseline to lock a plan DECLARE l_plans PLS_INTEGER; BEGIN l_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( sql_id => '&sql_id', plan_hash_value => &plan_hash, enabled => 'YES', fixed => 'YES' ); DBMS_OUTPUT.PUT_LINE('Plans loaded: ' || l_plans); END; / ``` -------------------------------- ### Query Message Enqueue Times in Oracle Database Source: https://github.com/oracle-samples/oracle-db-examples/blob/main/txeventq/okafka/Quickstart/README.md This SQL query retrieves the message ID and enqueue timestamp from a specified database topic table. It is used to verify that messages have been successfully processed and stored in the database. ```sql select MSGID, ENQUEUE_TIME from TOPIC_1; ``` -------------------------------- ### Install Artillery with npm Source: https://github.com/oracle-samples/oracle-db-examples/blob/main/sagas/SagaBenchmark/README.md Installs the latest version of Artillery globally using npm. Artillery is a load testing tool used to drive the benchmark application. ```bash npm install -g artillery@latest ``` -------------------------------- ### Run RSI Demo Listener Application with Virtual Threads (Shell) Source: https://github.com/oracle-samples/oracle-db-examples/blob/main/java/rsi-example/README.md This command demonstrates how to run the RSI demo application's listener. It requires compiling the project using `mvn package` and then executing the Java application with the `--enable-preview` flag, which is necessary for using JDK 19's Virtual Threads. The command specifies the classpath and the main class to run, such as `rsi.demo.amqp.Listener`. ```shell $ mvn package $ java --enable-preview -cp ./target/rsi-demo-0.1.0.jar rsi.demo.amqp.Listener ``` -------------------------------- ### Oracle EXPLAIN PLAN Output Source: https://github.com/oracle-samples/oracle-db-examples/blob/main/text/oracle-text-scripts/lang_options/output.txt Shows the execution plan for a SQL query. This example illustrates a simple plan involving a WORD operation on 'WASCHOEN'. ```sql EXPLAIN_ID ID PARENT_ID OPERATION OPTIONS OBJECT_NAME POSITION ---------- --- --------- ---------- ---------- -------------------- -------- Test 1 0 WORD WASCHOEN 1 ``` -------------------------------- ### Create PL/SQL Function Source: https://github.com/oracle-samples/oracle-db-examples/blob/main/text/oracle-text-scripts/materializedview/indexstats.txt A basic example of creating a stored procedure in Oracle that returns a string value. This demonstrates the syntax for function declaration and return statements. ```sql create or replace procedure func_test return varchar2 as begin return 'roger/roger'; end; ``` -------------------------------- ### Create Index in Oracle SQL Source: https://github.com/oracle-samples/oracle-db-examples/blob/main/text/oracle-text-scripts/lang_options/output.txt SQL command to create an index on a table. Indexes improve query performance. This example creates an index, likely for text searching. ```sql Index created. ```