Feed aggregator

When I insert or update, I can find out using SQL%ROWCOUNT how many rows were affected. But how do I find out how many rows a MERGE statement does? Any help would be greatly appreciated. Thanks

Hi Tom , We have to load some data in oracle from a flat file . The date in flat file is displayed in Julian date format so 31st Mar 2003 comes as 2003090(number of days since Jan 1st,2003) and 20th Mar 2003 comes as 2003069(again number of days since Jan 1st,2003) . I have to convert Julian date(2003090) into 20030331(yyyymmdd) format. Now oracle consider julian date as the number of days since Jan 1st,4712 bc and not in the way i got my data. So is their any function which i can use to convert a date given in 2003090 format into 20030331 format or do i have to write a user function for this ? Your help will be greatly appreciated as always. Thanks

I am getting the dreaded ORA-24247 when I try to execute UTL_MAIL.SEND as a non-DBA user. The non-DBA users have been granted execute on UTL_MAIL. The DBA users work fine. If I grant DBA to a non-DBA account, and try to send an email it works. The script I ran to create the ACLs is the following: <code>BEGIN DBMS_NETWORK_ACL_ADMIN.CREATE_ACL ( acl => 'UTL_MAIL.xml', description => 'Granting privs to required users for UTL_MAIL.xml', principal => 'SYS', is_grant => TRUE, privilege => 'connect'); DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE ( acl => 'UTL_MAIL.xml', principal => 'SYS', is_grant => TRUE, privilege => 'resolve'); END; / BEGIN DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL ( acl => 'UTL_MAIL.xml', host => '172.xx.xx.xxx', lower_port => '25', upper_port => null); COMMIT; END; / - A DBA BEGIN DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE ( acl => 'UTL_MAIL.xml', principal => 'NEWEIS', is_grant => TRUE, privilege => 'connect'); END; / BEGIN DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE ( acl => 'UTL_MAIL.xml', principal => 'NEWEIS', is_grant => TRUE, privilege => 'resolve'); DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL ( acl => 'UTL_MAIL.xml', host => '172.xx.xx.xxx'); END; / commit; / - A non-DBA BEGIN DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE ( acl => '/sys/acls/utl_mail.xml', principal => 'SOPRAS', is_grant => TRUE, privilege => 'connect'); END; / BEGIN DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE ( acl => '/sys/acls/utl_mail.xml', principal => 'SOPRAS', is_grant => TRUE, privilege => 'resolve'); DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL ( acl => '/sys/acls/utl_mail.xml', host => '172.xx.xx.xxx'); END; / commit; /</code>

 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

 

Best practices are an essential part of our curriculum. When creating Fluid pages, we recommend the following page name pattern:

<site-prefix><purpose><page-type>

For example, when creating a Fluid subpage to manage widgets, we would name it JSM_WIDGETS_SBF. Here is a list of page-type suffixes derived from Oracle-delivered pages:

FL

Fluid Page

SBF

Subpage Fluid

SCF

Secondary Page Fluid

SFL

Side Page (1 or 2)

FFL

Footer Page

LFL

Layout Page

But four more page types aren't used enough to have a suffix pattern: Header Page, Search Page, Prompt Page, and Master&Detail Target Page. For those page types, we've come up with our own suffixes:

HFL

Header Page

SRF

Search Page

PFL

Prompt Page

MDF

Master&Detail Target Page

For the most part, the convention is easy to understand. First initial of page type, and then FL. But what about SRF? Where did that come from? There are four page types that start with the letter S. Adding the R after the S looked more like Search than any of the other options we considered.

With Classic already having the suffixes SEC, SUB, and POP, we have suffixes for every page type except Classic standard pages. Should we, therefore, adopt _CL for Classic pages? PeopleSoft uses exception-based design. For example, the page bar is on until you turn it off, and the standard component toolbar is on until you turn it off. Changing these properties would be exceptions. Naming conventions are no different. Our naming conventions document the exceptions. With over 12,000 Classic pages in HCM, Classic is clearly the norm, and everything else is an exception.

What do you think? Do you have different naming conventions you use for page development? If so, share your ideas in the comments!

At JSMpros, we teach PeopleSoft Fluid training and best practices regularly. We look forward to hosting you in a future class!

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

I ran across a funny thing today in an application I built 10 yesrs ago but that I have not been involved in maintaining since then. The application is gettings som much needed UI upgrades, like getting out of the dark ages and using universal theme.

During that work there were a set of things that didn’t work, components could nbot be found and some unexpected things happened. Unexpected compared to the behavior that had been experienced while doing a mock app in the private environment in the VM.

It turns out the application still has the compatibility mode set to 4.1. Yes, no wonder it was behaving different than when one has 22.x.

This is set during upgrade if upgrading from a version to another where the new has a change in behavior. You’ll want to look into what the change behavior is and make sure your application works well with it so it is on the latest version.

You find this setting in:

Application Properties -> Definition -> Properties -> Compatibility Mode

Oracle has a running lit of the changed behavior version by version in the documentation. That is great so one does not have to find the docs for each version that has changed behavior to find it. It is in one convenient place.

https://docs.oracle.com/en/database/oracle/apex/22.2/htmrn/index.html#HTMRN-GUID-712BE54F-08CD-43A3-A645-87B9360ED516

It goes through the changes for each version where the behavior of the APEX engine changed, starting with 4.1 all the way up to the current version. Very convenient to be able to read up on them and see what one should expect and what to check after changing this setting.

As evidenced by this setting in this application having been forgotten it is worth validating that applications you have upgraded has this set to the value you expect. For me I’d expect the latest unless the work to verify that it works as intended has not been completed.

 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

A few days ago I tweeted this link to an article about some workloads being better suited to on-prem infrastructure. Jared Still sent me this link. The executive summary in both cases is, if you have defined workloads that don’t require elastic resource allocation, and you are not making use of cloud-only platforms, you might … Continue reading "Why Automation Matters : The cloud may not be right for you, but you still have to automate!"

The post Why Automation Matters : The cloud may not be right for you, but you still have to automate! first appeared on The ORACLE-BASE Blog.Why Automation Matters : The cloud may not be right for you, but you still have to automate! was first posted on March 22, 2023 at 11:39 am.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

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.

No I’m not talking about the Oracle Database. It is extremely unlikely that you found your way here if it isn’t already to some extent your specialisation. If you made it here and have no idea what this is about, drop a comment and let me know your path here if that were to be the case.

No, this is about Python. Even more it is about the OracleDB module for python. That capitalisation is mine, the official way to write it is Python-oracledb. Either way it is Oracle’s implementation of the Python Database API 2.0 specification.

I know what some of you may be thinking. Why would I need that, I’ve got my old trusted cx_Oracle driver. Yes you do and you have nothing to fear, Python-oracledb is the new name for it. You’ll feel right at home.

Let’s start with recognising what a beautiful API. To me this is an API that is equally natural to use from Python and database perspectives. I think it is not just due to Oracle’s efforts but also for the general API specification that it follows. I often find the interface between language and SQL more natural in Python than even in PL/SQL. Yes, that is probably close to an offensive statement for some. But try it before you diss it.

Speaking of Python, if you too work with developers that mainly write code in Java, you will want to be able to write simple stubbs of code to access the database and achieve the things you want them to implement to help you do your job. Most of the time it is enough with some cookie cutter code and just add what you need.

I use Python the same way but for slightly different reasons. I use it to show myself a concept of the work one has to do when accessing the database from outside the database. It also includes things that makes many things easy to test out that for me is beyond cumbersome to set up in Java. In addition Python is a language you want to have in your toool chest anyway as it is the preferred language for data these days.

Let’s rig a small test. Begin with installing the module.

python -m pip install oracledb --upgrade --user

Test getting a connection with som boiler plats code.

import oracledb

with oracledb.connect(user='<USER>', password='<PASSWORD>', dsn='CONN STRING>') as connection:
    with connection.cursor() as cursor:
        sql = """select sysdate from dual"""
        for r in cursor.execute(sql):
            print(r)

As one would expect it just prints the sysdate. However this blog is not about examples for coding with oracledb, rather I think it is worthwhile to just finish up with highlighting som of the features it has to show the kind of things you can try out with just a few more liens of code.

The plain way of using it uses the thin driver which mimics how most application servers connect. If you want to you can set up for using thick mode also. It is required for som advanced usage scenarios.

Some of the features I think are of particular interest:

• Advanced Queueing including receiving database notifications.
• Support for EBR
• Supporting setting context when creating a connection to allow for better instrumentation in the database.
• Support for E2E monitoring and tracing
• Database Resident Connection Pooling
• ref cursors
• Array-operations
• JSON

And the list goes on.

One final thing I find very interesting. It has an API to set up a connection pool so you can use it to model use cases for how a connection pool would behave. It also allows you to do things like build a REST-server with it’s own connection pool to service parallell requests to your REST-server.

Take it out for a spin, I’m sure you’ll enjoy it.

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