Feed aggregator

Hello, This is math/algebra question, but I was interested to see if there was a sql based solution as I am pleasantly surprised by the depth and breath of solutions on this site. At our company, we print labels, brochures etc. For each print job, the item's length and width are specified as number with upto 4 decimals. For reporting purposes, it's necessary to show (for example) the width 2.875 as 2 7/8. So, I want to essentially convert a number to a fraction representation. Any insight on how to solve will be greatly appreciated. Thank You, Manish

-- Create table <code>create table test1 as ( select 2001 as keyvalue, 1 as counter, '+++ Input: My Name is ABC' as text from dual union all select 2001 as keyvalue, 2 as counter, '+++ Input: Cricket is my favourite sport' as text from dual union all select 2001 as keyvalue, 3 as counter, '+++ Input 23/05/23: Problem Summary This is test scenario' as text from dual union all select 2001 as keyvalue, 4 as counter, '+++ Input: I love travelling' as text from dual ); select * from test1;</code> --------------------------------------------- I have 3 columns: <code>KEYVALUE | COUNTER | TEXT -------------------------------------------------------------------- 2001 | 1 | +++ Input: My Name is ABC -------------------------------------------------------------------- 2001 | 2 | +++ Input: Cricket is my favourite sport -------------------------------------------------------------------- 2001 | 3 | +++ Input 23/05/23: Problem Summary This is test scenario --------------------------------------------------------------------- 2001 | 4 |+++ Input: I love travelling ---------------------------------------------------------------------</code> 1) required output: <code>KEYVALUE | TEXT --------------------------------------------------------------------- 2001 | +++ Input: My Name is ABC +++ Input: Cricket is my favourite sport +++ Input 23/05/23: Problem Summary This is test scenario +++ Input: I love travelling</code> Please note LISTAGG is not working due to the size.( I am trying to have a merged column of clob datatype) I have made use of the below query: <code>SELECT keyvalue, DBMS_XMLGEN.CONVERT ( EXTRACT( xmltype('<?xml version="1.0"?><document>' || XMLAGG( XMLTYPE('<V>' || DBMS_XMLGEN.CONVERT(text)||' '|| '</V>') order by counter).getclobval() || '</document>'), '/document/V/text()').getclobval(),1) AS data_value FROM cust_input_contents where keyvalue in ('2001') GROUP BY keyvalue;</code> But I am getting the below error: <code>ORA-31011: XML parsing failed ORA-19202: Error occurred in XML processing LPX-00217: invalid character 8 (U+0008) Error at line 3 ORA-06512: at "SYS.XMLTYPE", line 310 ORA-06512: at line 1 31011. 00000 - "XML parsing failed" *Cause: XML parser returned an error while trying to parse the document. *Action: Check if the document to be parsed is valid.</code> 2)Let's say after the merge I now have <code>KEYVALUE | TEXT --------------------------------------------------------------------- 2001 | +++ Input: My Name is ABC +++ Input: Cricket is my favourite sport +++ Input 23/05/23: Problem Summary This is test scenario +++ Input: I love travelling</code> I now want to remove the entire line starting from Input when I encounter a problem summary in the text. Required Output: <...

 In today's uncertain times, investing in upskilling and professional development is a key to stay ahead and be competitive in a job market. I want to highlight FREE training courses that come with free assessments and digital badges.

=> Architecting (new!): https://bit.ly/architect23

You can browse through the most popular courses offered in AWS Skill Builder relevant to architecting and more here. 

=> AWS for Games (new!): https://go.aws/3l6fYf7

provides a deeper examination of game-specific solution design and operational considerations for developing games on AWS. Game developers, DevOps administrators, and cloud architects can now showcase their knowledge in creating and maintaining games in the cloud through a verifiable AWS for Games Digital Learning Badge

=> Serverless: https://go.aws/3UZpFs0

If you’re a beginner looking for a clear starting point to help you build a career or advance your serverless knowledge and skills, I recommend you start with an AWS Learning Plan. This set of on-demand courses will help you develop skills for running code, managing data, and integrating applications, all without managing servers. you can earn a digital badge to demonstrate and share your knowledge of AWS Serverless technologies by scoring at least 80% on the assessment.

=> Object Storage: https://go.aws/3JVneTS

Learn about Amazon Simple Storage Service (Amazon S3), which is built to store and retrieve any amount of data from anywhere.

=> Block Storage: https://go.aws/3JVneTS

Learn about Amazon Elastic Block Storage (Amazon EBS) for both throughput and transaction intensive workloads.

=> File Storage: https://go.aws/3JVneTS

Dive into Amazon EFS, a simple, serverless, set-and-forget, elastic file system and the Amazon FSx family of fully managed storage service solutions.

=> Storage Data Migration: https://go.aws/3JVneTS

Learn about data migration services and strategies to move your data to the cloud.

=> Data Protection & Disaster Recovery: https://go.aws/3JVneTS

 Explore methods on how to centrally manage and automate backups across AWS services, ensure your data is protected, and examine best practices in disaster recovery using AWS storage services.

Whether you’re an on-premises or cloud storage engineer, storage administrator, developer, solutions architect, or just interested in building your cloud knowledge, these learning plans and digital courses have been designed by AWS teams based on best practices to help you discover more possibilities with modern AWS Cloud  services and solutions. Happy learning. 

With regards to Database Links, I can look at dba_db_links to see what that particular instance's DB Links to other instances. Is there a way to tell which other instances have DB links to the instance that I'm currently in? Maybe entries in the data dictionaries? For example, Instance A has a DB_Link -> Instance C & Instance D. I can find these in DBA_DB_LINKS when logged in to Instance A. However, Instance B has a DB Link -> Instance A. Is there a way to tell, logged in to Instance A, that Instance B has a db link to Instance A? Thanks, Paul

Hi Tom, While checking ASH, I found some SELECT queries blocked other transactions with LATCH FREE with latch# = 39, which is "object stats modification". Can you explain what that particular latch is for and in which area it stays ? Thanks, Vu

We have a DB where users are able to search for text, using Oracle Text (I guess it's Intermedia in 8.1.7). Anyway, there are 3 columns that are indexed. Each of them is in a separate table. The user has a single field to type in their search criteria. It's great that the entire set of CONTAINS syntax is available to the users... with the '&" and '|' and '~' and all that (I just point them to the Intermedia docs). But, I realize now that this is not really giving them the functionality they think it is. When I pass through this WHERE clause (assume all the join conditions are there): where contains(t1.varchar2_col1,'this & that',10) > 1 or contains(t2.blob_col1,'this & that',20) > 1 or contains(t3.blob_col2,'this & that',30) > 1 I realize I'm requiring "this" and "that" to occur together in at least one of the columns. What the users want is this condition ACROSS THE CONCATENATION OF ALL 3 COLUMNS. I.E. if "this" is in one of the columns and "that" is in one of the others, that result should be returned (as well as the case where both strings are indeed in a single column, of course). Is there a way to get Intermedia to index in this fashion? I don't want to parse the contains syntax, because that's hard, and I'd have to keep up with it as new releases come out.

I recently bought an aranet 4 from Canadian Tire, of all places. Here’s my review.

TL;DR: It’s calibrated, convenient, and easy to use, and your indoor CO2 readings may surprise you.

What is it for?

The Aranet 4 monitors CO2, temperature, and humidity. It uses a non-dispersive infrared sensor (NDIR) to measure the ambient CO2 in parts per million (ppm). From the user manual:

Why does that matter? 1. Thinking:

The outdoor CO2 concentration is around 400-450 ppm (for now…). Inside, with the windows closed, CO2 concentrations can rise rapidly, and high CO2 concentrations actually affect our activity levels and cognition, especially in the domains of information usage and strategy. From a study of office workers exposed to low, medium, and high CO2 concentrations (doi:10.1289/ehp.1510037):

Many countries, including Canada, have issued guidelines in the 600-1000ppm range for indoor CO2 concentrations, and ASHRAE (American Society of Heating, Refrigerating and Air-Conditioning Engineers) recommends keeping indoor CO2 levels to no more than 700 ppm above outdoor levels (so, 1100-1200 ppm).[source]

2. Health:

CO2 concentrations can be used as a rough proxy for clean air. Many illnesses spread through the air, either through droplet or aerosol spread. If indoor CO2 concentrations are low, despite everyone in the room busily metabolizing and breathing out CO2, that means there’s excellent ventilation, and therefore a much lower chance that the air you breathe in has been breathed (or sneezed!) out by somebody else. However, if CO2 concentrations are high, that doesn’t necessarily mean the air isn’t clean. A HEPA filter will remove bacteria, viruses and dust from the air, but will not lower the CO2. So, the higher the indoor CO2 concentration, the more important it is to make sure those HEPA filters are on.

Canadian Tire? Seriously?

Yes! Canadian Tire’s goods mostly range from dollar-store quality to “decent, but far from the best,” so it was a huge surprise to find an Aranet 4 for sale there for less than it would cost to buy it direct & pay for shipping. I bought one online and picked it up the next day.

The review 1. It works

Insert batteries, bam! it boots and works. Very easy setup. It even ships with two batteries.
One caveat: the first reading I got after putting the batteries in was whack, over 1000ppm higher than subsequent readings.

2. It’s properly calibrated straight from the factory

I checked the calibration on the Aranet 4 by taking it outside. I got a CO2 reading of 445 ppm, which is both in the expected range and accords with the 440ppm I got earlier with another CO2 sensor.

3. Setup with the phone app was easy

The bad news: Although a 7-day history of CO2, temp and humidity readings is stored on the sensor, you need the app to actually see that history, and to configure basic functionality like the monitoring interval, temperature scale (C or F), and buzzer (audio alert if CO2 goes over 1500ppm).

The good news: App setup is easy. Install phone app, make sure Bluetooth is on, pair with Aranet 4 in the app, done.

4. Monitoring interval

The default interval between readings is 5 minutes, which is fine for sitting around, but annoying when you’re trying to test it. The only way to reset the monitoring interval is through the app. Options are every 1, 2, 5 or 10 minutes.

5. Leave that bedroom door cracked!

We settled down in a small room with the door closed to watch a movie. I left the Aranet on, and checked the graph at the end. Between 9:30 and 11:30, CO2 rose from 550 ppm to almost 1800 ppm — enough to affect our cognitive function.

Then, I brought the Aranet into the bedroom, but left the door cracked open an inch. Between 11:45 pm and 2:30 am, CO2 rose to 1489 ppm, with a few marked peaks and valleys, then sawtoothed back down to 1284 ppm at 7:30 am. I’m guessing that the peaks and valleys have something to do with the forced-air heat going on and off.

6. CO2 readings are fast, temp readings are slow

Taking the Aranet4 from one room to a different one, or from inside to outside, shows up immediately in the CO2 readings. The temperature and humidity readings are slower to update. For example, when I brought the Aranet4 from my 20-degree (Celsius) house to the 5-degree outside, the CO2 reading immediately showed 445 ppm, but the temp reading was 12 degrees. So, don’t expect instant-read-thermometer speed from the Aranet 4.

7. Mysterious firmware update

The Aranet 4 shipped with firmware version v1.1.2, which was out of date when I got it. There was zero indication that it was out of date, unfortunately. In “Device Settings”, if I clicked on the firmware version (which wasn’t flagged in any way), only then I would find out that a more recent version was available.

Eventually, I stumbled across this and decided to update the firmware to the current version, 1.2.1. This was as simple as putting my phone next to the Aranet, clicking “Upgrade[sic] firmware”, and not touching anything until it was done. No release notes seem to be available anywhere, so I have no idea if any bugs were fixed or why the update was released. All I’ve got is that it still seems to work post update.

8. Personal vs. pro

The Aranet 4 comes in two versions which are visually indistinguishable: Personal and Pro. The Canadian Tire website doesn’t say which one I got, but I’m reasonably sure it’s the personal one. The only difference seems to be that the “pro” version can connect to a (separately available) wireless base station, which can accept connections from multiple Aranet Pros. For home users, the phone app’s ability to connect to up to 6 Aranet Personals is probably more than sufficient.

Verdict: Small, portable, easy and convenient

The Aranet 4 is small and simple enough I could take it out to scout CO2 in restaurants, theatres or museums, or send it with my kid to school and then check the day’s CO2 graph when the kid gets home (historical data is stored on the sensor). No major headaches, it’s accurate, and it works. 10/10 would buy again.

User manual

https://cdn.bfldr.com/FS48XT6B/at/vzp23x5qbsfk7t5t3vp8n/Aranet4_User_Manual_v25_WEB.pdf

Coming soon: side-by-side readings

The next step is to take several side-by-side readings with the aranet4 and another, less portable CO2 sensor, in various areas.

In case you didn’t know, countries occasionally change their time zones, or alter the way they handle daylight saving time (DST). To let the database know about these changes we have to apply a new database time zone file. The updated files have been shipped with upgrades and patches since 11gR2, but applying them to … Continue reading "Update Oracle Database Time Zone Files (Poll Results Discussed)"

The post Update Oracle Database Time Zone Files (Poll Results Discussed) first appeared on The ORACLE-BASE Blog.Update Oracle Database Time Zone Files (Poll Results Discussed) was first posted on March 28, 2023 at 11:13 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.

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.