DBA Blogs

 Prompt Engineering means to teach smart AI software how to respond to people's queries. The objective of Prompt engineering involves writing a prompt that gets you your desired result. You can use prompt engineering with Oracle DBA too.

 If you are a beginner and want to start your learning journey in AI and Data Science then this video is a good starting point.

1-Learn Python the Right Way by Peter Wentworth, Jeffrey Elkner, Allen B. Downey, and Chris Meyers. https://i.ritzastatic.com/learn-python-the-right-way/learn-python-the-right-way.pdf 2- Free SQL Courses https://www.udemy.com/topic/sql/free/ 3-Automate the Boring Stuff with Python by Al Sweigart https://automatetheboringstuff.com/ 4-Introduction to Machine Learning by Alex Smola and S.V.N. Vishwanathan https://alex.smola.org/drafts/thebook.pdf 5-Deep Learning by Ian Goodfellow, Yoshua Bengio, and Aaron Courville https://www.deeplearningbook.org 6-Natural Language Processing with Python by Steven Bird, Ewan Klein, and Edward Loper. https://www.nltk.org/book/ 7-40 days of AI https://www.linkedin.com/pulse/40-days-ai-steve-nouri/

I am trying to subtract 6 months from the current date (SYSDATE). I know about the ADD_MONTH function, but I haven't been able to find documentation about a "SUBTRACT_FUNCTION" which can do this for me.

I did exact thing as per this link https://oracle-base.com/articles/misc/configure-tcpip-with-ssl-and-tls-for-database-connections But I am always getting the below error. ORA-28864: SSL connection closed gracefully I am using free version of 21C database

 

We are using APEX 22.2.0. I have an interactive report on a page. I have added a logo to the page. I want the logo to display on the report when the user selects the "Download" option from the Actions menu. How can I get the logo to download with the rest of the report?

is there any way to add DLL files contains .net functions (c# language) and use them in oracle apex application

 Generative AI is one of the biggest recent advancements in artificial intelligence technology because of its ability to create something new. It opens the door to an entire world of possibilities for human and computer creativity, with practical applications emerging across industries.

Hi Tom I am executing a query that fetches from a cursor first then does an insert based on the number of matching records as follows CREATE OR REPLACE PROCEDURE test AS Dt date; DelID Integer; LocID Integer; CURSOR c1 IS select po_id, po_dt, to_number(name) from Order, Location Where Order.id = Location.id; BEGIN OPEN c1; LOOP FETCH c1 Into DelID, Dt, LocID; EXIT WHEN C1%NOTFOUND; Insert into Results select DelID, NVal, prod_id, NULL, qty, decode(qty, 0, 0, cost/qty), 0, NULL, 1 from subs, Inv Where subs.id = LocID And v_dt = Dt And Subs.id = Inv.inv_id; COMMIT; END LOOP; CLOSE C1; END; / The subs table contains 128 million records Inv table contain 40000 records . Cursor c1 returns 1.3 million records. This insert has taken 3 days to insert just 4 million records and there are way more than 4 million records. Im thinking of using direct insert :Insert /* + Append */. Is this going to help me? I have another question on the placement of commit, I think in hte above query it is getting committed at every one insert , is this right thing to do? Also should I disable the Index on the Results tables and rebuild it later? Is there any other way to optimize the above operation? Thanks Steve

Using loadjava to upload a Java source results in: <code>ORA-29547: Java system class not available: oracle/aurora/rdbms/Compiler ORA-06512: at line 1</code> This usually indicates that you either have not installed the Java Virtual machine or you did not properly apply the latest OJVM PSU patch. However, latest patch was applied and the Java Virtual machine is actually running: opatch lsinventory: <code>Patch 34786990 : applied on Mon Jan 23 10:39:29 CET 2023 Unique Patch ID: 25032666 Patch description: "OJVM RELEASE UPDATE: 19.18.0.0.230117 (34786990)"</code> <code>select comp_id, comp_name, version_full from, status dba_registry; JAVAVM JServer JAVA Virtual Machine 19.18.0.0.0 VALID</code> <code>SQL> select dbms_java.longname('TEST') from dual; DBMS_JAVA.LONGNAME('TEST') -------------------------------------------------------------------------------- TEST</code> <code>SELECT dbms_java.get_jdk_version JDK_Version FROM dual; JDK_VERSION -------------------------------------------------------------------------------- 1.8.0_361</code> And formerly uploaded Java 'Hello World' Code works perfectly: <code>SQL> select helloworld() from dual; HELLOWORLD() -------------------------------------------------------------------------------- Hello world </code> So to summarize, the Java VM ins installed, valid and actually running. It is just the compiler which isn't working. And in fact the respective Java Class ins invalid: <code>select owner, object_name, object_type, status from dba_objects where object_name = 'oracle/aurora/rdbms/Compiler'; SYS oracle/aurora/rdbms/Compiler JAVA CLASS INVALID</code> Trying to resolve the Java Class results in a segfault: <code>SQL> alter java class "oracle/aurora/rdbms/Compiler" resolve; alter java class "oracle/aurora/rdbms/Compiler" resolve * ERROR at line 1: ORA-03113: end-of-file on communication channel Process ID: 1239513 Session ID: 578 Serial number: 75</code> Alert Log: <code>ORA-07445: exception encountered: core dump [kglPinMask()+6] [SIGSEGV] [ADDR:0xA0] [PC:0x4BA9C26] [Address not mapped to object] []</code> So I am wondering what happened and how to solve this issue. Is there some way to get the Compiler working again? Do I have to follow the instructions to remove and reinstall the Java VM? Or does this not solve the Compiler issue? Any ideas are appreciated! Thanks in advance and all the best, Frank

Hi Team, I have one query which quite complex and join multiple tables. Its Insert into table a (Select /*+ parallel(S,8) */ column A,column B from Table a,Table b {Business logic}) Now the issue is, when this query is running, I could see there are 17 entries in gv$SQL and gv$Session. Idelily it should only have 8 parallel queries running in gv$SQL as per my understandig. How come there are double the entries. Please note its RAC environment with 2 Instances. If I go through the real time SQL monitor, I could see PX COORDINATOR has 17 executions. and PX SEND and PX BLOCK operation has 8 execution. Is anything wrong here, because this query is running too slow.

Hi First time asking a question here, I have tried to follow the guidelines, apologies for any errors on my part. Any help you can give will be gratefully received, even if it's just to point me in the right direction rather than an answer First a bit of context. We are in the process of removing technical debt and as such we performing a migration of data from customised tables back to the original base tables for the application where possible. As an interim measure, we are migrating the data and replacing the custom tables with views which point to the new storage locations of the data. The view synonyms have the same names as the custom tables that they replace so that downstream systems don't fall over. Once all interfaces and other dependent code have been migrated to use the new locations, these interim views will be dropped. One of these views has to take data which is now in a clob and produce a view which chunks the clob into 240 character strings to match how the data used to be stored producing <u>level</u> <u>text</u> 1 text string first 240 2 text string next 240 3 text string another 240 . . . I used the following select to produce that output (and I need the surrogate ID from tab_a) <code> select a_surrogate_id, v.lvl, v.chnk, from tab_a, (select subj, crse, term, to_char(regexp_substr(s, '.{240}', 1, lvl)) chnk, lvl from (select subj, crse, term, s, level lvl from (select b_subj_code subj, b_crse_numb crse, b_valid_from term, substr(b_required_materials,1,4000) s from tab_b) connect by level <= length(s) / 240) ) v where a_subj_code = v.subj and a_crse_numb = v.crse and v.term = (select max(sy.b_valid_from) from tab_b sy where sy.b_subj_code = v.subj and sy.b_crse_numb = v.crse and sy.b_valid_from <= a_valid_from) </code> which works, but is really, really slow (add an infinite number of reallys) and when I do a select from the view with a where clause, I have time to run a marathon before it comes back (and I run slowly). The explain plan is the following <code>------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 16470 | 34307 | | 1 | NESTED LOOPS | | 815 | 12M| 14222 | | 2 | VIEW | | 22813 | 357M| 532 | | 3 | CONNECT BY WITHOUT FILTERING | | | | | | 4 | TABLE ACCESS FULL | tab_B | 22...

I have a table with two columns START_DATE and END_DATE and they have a data type of : Timestamp with timezone. and i have also a Globale Item that get the timezone of the user, and i should use it to convert the timezone of the column in the database to the timezone of the user. <b>the data in my table are stored in this format:</b> 09-MAR-23 06.30.00.000000 PM +01:00 <b> the way i do the insert is:</b> insert into table_name(START_DATE) VALUES(TO_TIMESTAMP(:P51010_START_DATE, 'YYYY-MM-DD"T"HH24:MI:SS');); My attempts to solve the proble: <b>1. first attempt:</b> <code>SELECT CAST(START_DATE AS TIMESTAMP WITH TIME ZONE) AT TIME ZONE :GP_TMZ AS CONVERTED_START_DATE, CAST(END_DATE AS TIMESTAMP WITH TIME ZONE) AT TIME ZONE :GP_TMZ AS CONVERTED_END_DATE FROM TABLE_NAME;</code> result: ORA-20999: Failed to parse SQL query! ORA-06550: line 6, column 83: ORA-00923: FROM keyword not found where expected <b>2. second attempt:</b> <code>SELECT CAST( data_inizio AS TIMESTAMP WITH TIME ZONE ) AT TIME ZONE '''' || '' || :GP_TMZ || '''' as CONVERTED_START_DATE, CAST(END_DATE AS TIMESTAMP WITH TIME ZONE) AT TIME ZONE '''' || '' || :GP_TMZ || '''' AS CONVERTED_END_DATE FROM TABLE_NAME;</code> To add a single quote at the Begining and at the end of the page item i try this query <b>result:</b> ORA-20999: Failed to parse SQL query! ORA-06550: line 1, column 1: ORA-01882: timezone region not found <b>Shared Components: Globalization section</b> Application Timestamp Format: DD-MON-YYYY HH:MI:SSXFF PM Application Timestamp Time Zone Format <code></code> DD-MON-YYYY HH.MI.SSXFF PM TZR <b>select * from v$version;</b> BANNER: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production BANNER_FULL: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.18.0.1.0 BANNER_LEGACY: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production CON_ID: 0

As I have done multiple engagements with Oracle GoldenGate and helped clients get the most out of their investment; I […]

The post GoldenGate Parameter Files – Format and Logic appeared first on DBASolved.

 This video shows you step by step demo to enable point in time recovery PITR for AWS DynamoDB table using AWS CLI.

 If you are a cloud engineer and looking to get into Cyber or cloud security then start by learning these tools.

Is there a utility that helps us migrate the data from mySQL database to Oracle 8.1.6 database? Also, is there a strategy one has to follow for migrating from one database to another?

We want to use the Data Loading functionality of APEX to upload a specific tab of an Excel sheet. We only found out it the Data Loading function only imports the first tab of the Excel sheet. We want to upload the second one of the Excel sheet. What are we missing here?

Hi Tom, I have a text column with clob datatype. (Below is how data looks in the column). I want to have only the customer input information in the column and remove all private notes. Text Example: Customer Input 04.12.2022 here is sentence 1 ||Private Notes This is sentence 2||Private Notes This is sentence 3 ||Customer Input this is sentence 4||Private Notes This is sentence 5 and needs to be removed||Customer Input this is sentence 6 Expected Output: Customer Input 04.12.2022 here is sentence 1 ||Customer Input this is sentence 4||Customer Input this is sentence 6

I tried to use UTL_HTTP with generated pdf from query report which is made using rtf file( BI pupplisher), I created page to disply the report, and use the page url to add xml script to the pdf file, but i always receive error message <code>(ORA-29273: HTTP request failed) (ora_sqlcode: -29273 ora_sqlerrm: ORA-29273: HTTP request failed ORA-06512: at "APEX_220200.WWV_FLOW_PROCESS_NATIVE", line 55 ORA-06512: at "APEX_220200.WWV_FLOW_CODE_EXEC_PLSQL", line 126 ORA-06512: at "APEX_220200.WWV_FLOW_DYNAMIC_EXEC", line 2654 ORA-24247: network access denied by access control list (ACL) ORA-06512: at "SYS.UTL_HTTP", line 380 ORA-06512: at "SYS.UTL_HTTP", line 1148 ORA-06512: at line 37 ORA-06512: at "SYS.DBMS_SYS_SQL", line 2120 ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_220200", line 837 ORA-06512: at "APEX_220200.WWV_FLOW_DYNAMIC_EXEC", line 2614 ORA-06512: at "APEX_220200.WWV_FLOW_CODE_EXEC_PLSQL", line 97 ORA-06512: at "APEX_220200.WWV_FLOW_CODE_EXEC_PLSQL", line 430 ORA-06512: at "APEX_220200.WWV_FLOW_CODE_EXEC", line 614 ORA-06512: at "APEX_220200.WWV_FLOW_EXEC_LOCAL", line 3053 ORA-06512: at "APEX_220200.WWV_FLOW_EXEC", line 2642 ORA-06512: at "APEX_220200.WWV_FLOW_EXEC", line 2678 ORA-06512: at "APEX_220200.WWV_FLOW_PROCESS_NATIVE", line 34 ORA-06512: at "APEX_220200.WWV_FLOW_PROCESS_NATIVE", line 1230 ORA-06512: at "APEX_220200.WWV_FLOW_PLUGIN", line 3163 component.type: APEX_APPLICATION_PAGE_PROCESS component.id: 62841056118348574801)</code> could you help me to solve this problem .. thanks in advance Part of Code <code>url :='https://apex.oracle.com/pls/apex/r/UserArea/AppName/testdirectreport?session=&SESSION.'; v_req := UTL_HTTP.begin_request (url, 'POST', ' HTTP/1.1'); UTL_HTTP.set_header (v_req, 'content-type', 'application/xml'); UTL_HTTP.set_header (v_req, 'charset', 'UTF-8'); UTL_HTTP.set_header (v_req, 'Content-Length', LENGTH (var)); reqlength := DBMS_LOB.getlength (var);</code>