DBA Blogs
ORA-12154: TNS:could not resolve the connect identifier specified for ODBC
Greetings,
I received the following error while trying to connect to Oracle instantclient 10.1 in MS Access 2016:
ORA-12154: TNS:could not resolve the connect identifier specified [#12154) [Microsoft][ODBC Driver Manager] SQLSetConnectAttr fail...
Categories: DBA Blogs
ORA-12560 ERROR - do I have all the services I need ?
Hello,
I am using a Macbook and I downloaded the Parallels virtual machine to get Windows 10 in order to download SQL Plus,
I have done all of the minimum requirements and installed SQL Plus, but when it comes to entering my username and password...
Categories: DBA Blogs
Database Link across versions
Can a database link created on an Oracle 12 database access tables hosted on an Oracle 11 database?
We have an existing application Oracle Argus that we are upgrading to Argus 8.1.2.1 which will run on Oracle 12 Exadata server. We have an existing...
Categories: DBA Blogs
Insert datetime values from .NET apps
Hello, Team.
A .NET app is being developed by our team. We are using Oracle database 18.3.0.
In order to insert date and time in a date column developers use the following code:
<b>TO_DATE(TO_CHAR(:DATE_COLUMN,'DD/MM/RRRR' HH:MI:SS AM), 'DD/...
Categories: DBA Blogs
Unix Strings command and data not encrypted
Hello TOM,
I have a problem with the Unix command named STRINGS. If I do not use encryption on my datas, I can read my string datas via Unix, even if these datas were truncated under Oracle.
And dtranger, I have their history too...
Fir...
Categories: DBA Blogs
Table storage parameters
I've always created tables using the defaults for storage parameters.
<code>
create table blah (
a number;
b varchar2(100)
);
</code>
however I recently worked with an organization where the following storage parameters were always spe...
Categories: DBA Blogs
Default Value From A Sequence: Sequence Updates On Provided Value Entries With INSERT Through LOOP But Not on Individual INSERTs
I have two samples of code below that use the same table and sequence definitions.
The first one inserts using individual insert statements, with the first three inserts giving an override value to the sequenced column and the final insert relyi...
Categories: DBA Blogs
Compare all table content from one schema to another
Hi,
I need to compare full schema table contents with another schema. Both will have same tables, with exact the same DDL, just some records will be different in few columns.
Is there any way to execute a script or any way to do it in SQL Deve...
Categories: DBA Blogs
parsing recusirvely "#" from strings without PL/SQL procedure
Dear Tom,
First of all thanks for the site, the tips... always useful... I faced recently an sql issue and i designed a beginning of answears but i think it should be optimized.
I have a table containing posts from a social network platforms. Thos...
Categories: DBA Blogs
Sending mail using utl_mail
Dear Team,
I tried to install UTL_MAIL using the below command
sqlplus sys/<pwd>
SQL> @$ORACLE_HOME/rdbms/admin/utlmail.sql
SQL> @$ORACLE_HOME/rdbms/admin/prvtmail.plb
it runs successfully and created the package and synonym but when i tri...
Categories: DBA Blogs
LOB compression
Hello,
I have a table with BLOBs and they are actually character logs generated by fax transmission software. They are about 4k-16k big and pretty redundant, they compress with zlib to just about 1k-2k. And I want to store them compressed in DB. N...
Categories: DBA Blogs
return multiple address to mail_pkg
Hi Tom. I'm trying to get your package mail_pkg (found it on this site) to accept multiple values for the p_to (recipient list).
begin
mail_pkg.send
( p_sender_email => 'me@acme.com',
p_from => 'Oracle Database Account <me@acme.co...
Categories: DBA Blogs
Merge vs Update
MERGE INTO pkt_prty_fncl_st ppst
USING tmp_pkt_prty_fstate_clnb_stgg tmp
on (tmp.fncl_ast_id = ppst.fncl_ast_id
AND tmp.prty_id = ppst.prty_id
AND tmp.pkt_pcsg_st_cd = ppst.pkt_pcsg_st_cd
AN...
Categories: DBA Blogs
Select first value if exists, otherwise select another value
Hello
I have a table like this
<code>ID NTYPE
1 0
2 0
3 1
4 2</code>
I need a select to get all IDs according of a list of NTYPE (1 to N), but if any of the NTYPE list does not exist then get where NTYPE = 0..
...
Categories: DBA Blogs
Quickly Download Oracle Software From My Oracle Support
In order to quickly download Oracle software from My Oracle Support (MOS) to your server or local host, you must have curl or wget installed.
Check these by using:
[oracle@test working]$ which curl
/usr/bin/curl
[oracle@test working]$ which wget
/usr/bin/wget
Now use following command from the operating system prompt to download orachk, opatch, any Oracle patch or any other software by using your MOS credentials.
Right click on the software link and copy the link e.g. For orachk , this is the link:
https://updates.oracle.com/Orion/Services/download/orachk_beta.zip?aru=21110110&patch_file=orachk_beta.zip
Check these by using:
[oracle@test working]$ which curl
/usr/bin/curl
[oracle@test working]$ which wget
/usr/bin/wget
Now use following command from the operating system prompt to download orachk, opatch, any Oracle patch or any other software by using your MOS credentials.
Right click on the software link and copy the link e.g. For orachk , this is the link:
https://updates.oracle.com/Orion/Services/download/orachk_beta.zip?aru=21110110&patch_file=orachk_beta.zip
Now use the above link in following command as follows:
wget --http-user=<MOS User Name> --ask-password "<File URL>" -O <File Name>
Example:
wget --http-user=test@mos.com --ask-password "https://updates.oracle.com/Orion/Services/download/orachk_beta.zip?aru=21110110" -O orachk_beta.zip
Hope that helps.
Categories: DBA Blogs
AUSOUG “Oracle Master” Award
I was very flattered and honoured to be named one of the first 4 “Oracle Masters” by the Australian Oracle User Group at the recent AUSOUG Connect 2018 conference in Melbourne. Thank you to all the AUSOUG members involved for this award. As always, it’s a pleasure to help fellow Oracle Database professionals in […]
Categories: DBA Blogs
Partner Webcast – Automate Data Integration with Oracle Data Integration Platform Cloud
Oracle Data Integration Platform Cloud is a part of the Platform service offerings in Oracle Cloud Integration family, offering a Comprehensive Cloud Based Platform Solution for all of your Data...
We share our skills to maximize your revenue!
We share our skills to maximize your revenue!
Categories: DBA Blogs
removing control characters from text
Is there a routine in Oracle that can test for and remove
unwanted characters in a text string, ie control characters?
Categories: DBA Blogs
Presenting at AUSOUG Connect 2018 Conference in Melbourne, 21 November 2018.
After initially not being in a position to make it this year, I will now be presenting at the AUSOUG Connect 2018 Conference in Melbourne this coming Wednesday, 21 November 2018. My presentation will be: “12c Release 2 and 18c – New Indexing Related Features” Oracle Database 12.2 and 18.3 releases have introduced a number […]
Categories: DBA Blogs
Migration from 11g to 12c change execution plan(Adaptative plan)
Hi, we are working on a PeopleSoft Migration and Database too.
We're migrating Oracle 11.2.0.3 to 12.2.0.1, so we have an issiue with a PeopleSoft Query.
The query on actual database enviroment(11.2.0.3), have a excecution plan with minimal cost ...
Categories: DBA Blogs
Pages
