Oracle DatabaseSponsored by jamf





Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.

Share tech news, updates, or what's on your mind.

Sign up to Post


what kind of encryption Oracle is offering? what tier are they doing to protect?
Free Tool: IP Lookup
LVL 12
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

I have an Oracle database with Table TB_Documents   I need to get all the Docuemnts ending as _0
While I try  DocNo like   '%_0'  , I am getting more rows than the ones I need . The _ seems to be ignored
How can I run a query to get the right result ?
Hello expert,

In previous question titled 'DECLARE CURSOR IS SELECT BEGIN'
The Cursor/Loop form was used to display a list of names and Contact IDs

Informative but not too useful.

In this question, more complex, there are attached texts to create three
TMP_SOURCE_180615 has fields Contact_ID, FirstName and LastName

TMP_SOURCE_180617 has fields Contact_ID, Contact_Phone, FirstName and LastName

TMP_SOURCE_180618 has fields Contact_ID, LastName, and Contact_Phone

PS File names too long for display. Have truncated the names to the last six digits..

Ususally a script is attempted before asking a question because I don't
believe in just asking a completely open ended question like 'How do I do this?'

But have tried a couple of things and it is obvious that I am not even close.

However within the context of the script marked 'Declare Cursor TMP_SOURCE_180615'
copied herewith below what is the approach and tactic:

Where the Contact_ID is a certain value in TMP_SOURCE_180615
Fetch the CONTACT_PHONE that has that CONTACT_ID value from TMP_SOURCE_180617
and insert the CONTACT_PHONE value that matches the Contact_ID in TMP_SOURCE_180618
in the matching CONTACT_Phone field.

Again would like to make question more specific but am in uncharted waters and cannot
find a tutorial that addresses this issue.

Will double point on answer.

Allen in Dallas

---- Declare Cursor TMP_SOURCE_180615 ------
      CURSOR …
Hello expert,

that need updating but am not familiar with this form.

Have looked online for a simple example so I could experiment with this model and figure it out.
Not finding an example that is simple, if it exists.

So have set out to edify myself.

First, created a table using script  so it is portable. Attached herewith: PL SQL Create Table explicit.txt
Creates a table with three records and twenty rows.

Second, Have been experimenting with code listed herewith below;

The Oracle SQL Developer always returns
Error starting at line : 1 in command
which is not too informative.

Think the issue is in the syntax in the PUT_LINE
but can't quite make it work.

Just working to get a simple example to do something
on which a base can be built.


Allen in Dallas

-----------------begin cursor script------------------
  CURSOR cur_name
  FOR TMP_SOURCE_180615 IN cur_name
Hi Expert,

I have following data as source!

ID          NAME
----         ---------
10              A
NULL      B
20              C
NULL      D
NULL      E
30              F

Looking for Output as ::

ID          NAME
----         ---------
10          AB
20          CDE
30          F

Thanks In Advance ...
Dear Experts,

I have a field in table type VARCHAR2(255), this field contain a number

I cannot convert this field to number because some values are a negative number with minus (-) sign after the number like: 124-

how do I convert this field to number ?

to_number( field) returns error: invalid number


what is the official method to migrate from Oracle to MySQL ?

can you please anyone let me know how to find the business days between two dates in oracle sql ?

Example :

Start Date               End Date            Total Days      Business Days( Weekends Excluded)
01/01/2018            01/31/2018            31                                 23
Hi Gurus,

I have the below table with details.

Table Name : Test

ID    USER        START_DATE           END DATE
---   --------      -------------------------------------------
  1     abc            01/01/2018            01/02/2018  
  1     xyz             01/02/2018           01/29/2018

Desired Output Required is Columns(s) ID, User, Start Date, End Date for Maximum Start Date and End Date i.e

 1     xyz            01/02/2018          01/29/2018
Hi EE,

Just finished setting up connectors to Oracle from SQL Data Engine standpoint now I need to get it working on SSIS.

Troubleshooting so far:

  • Installed the 32 bit and 64 bit Oracle client drivers on the windows server
  • Created a linked server on the SQL Server database engine instance works fine when I apply the whole EZCONNECT string. Doesn't work if just reference the label of the string in TNSNAMES.ORG file.
  • Installed the latest Microsoft Connectors V4.0 on the SQL server 2016 box.
  • Set ORACLE_HOME as a system environment variable.
  • TNS_ADMIN entry is now an environment variable.

Attached is the error I am getting

Any assistance is welcome.

Thank you.
Free Tool: SSL Checker
LVL 12
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.


what is the maximum number of oracle RAC node on version 10g, 11g, 12c and 18c ?

When Oracle RAC detect conflict of data, how it handle it ?

any email /alert will send out ?

data gone ?

for Oracle, any feature/way to move all oracle DB files on RAM , not just logical table / objects?
Hi ,
   I have an oracle 11g r2 table created with union of two tables .    
       TB_User Columns

  There are some duplicate UserID ( not caused by union. But I can't change the original source tables  )  .  I need to delete all except one row for a given userID .      I am not able to delete duplicates based on  RowID  as there are duplicate  ROWIDs  after Union
Oracle 12c / PHP7

ORA-01008: not all variables bound err
The table has a collumn name with a reserved word. (I know... Unfortunately I cannot change the collumn name)

$query = 'INSERT INTO leads_bu (leads_bu."SOURCE") VALUES (:SSOURCE)';
$stmt = oci_parse($conn, $query);
oci_bind_by_name($stmt, ":SSOURCE", $source, 20, SQLT_CHR); // VARCHAR2 (20)
$ins_result = oci_execute($stmt);

I've tried everything to get past this error. Thanks for your help.
I need to refresh a schema using a source and target schemas in the same database.  

Using a parameter file. I know there is one process that requires both an export and an import in separate processes, but cannot remember if that is when you are using only one database, or using two databases with a  db link.  In this case, I am using just one database for both the source and target.  Do I need an export process then an import process, or can data pump get it done with the one import as shown?

I bring metadata only due to restrictions I have to place, and I generally just generate inserts after the import completes.

Thank you..
# impdp parfile = K:\dpump\impdp_ch1_to_sch2.par

userid = USER@db1
schemas = sch1
content = metadata_only
directory = db1_dump_dir

exclude = grant

any and all pointers and suggestions are appreciated..
Here's my scenario.  There are 4 user accounts

1. SYS (System Admin/owner account )
2. DML_USER(Account that allows users to perform DML operations)
3. ETL _USER(Account for users to perform extract load and transform)
4. Query_USER (Account  for end users - select access only)

Logging in as SYS -  Need to create a package with procedures for each of the below

1.  need to create public synonyms for those objects that do not have a synonym and, for DML, ETL and Query account.  
2. need to grant DML access thru  a role (select, insert, update, delete) to DML and ETL accounts.

The code snippet below perform grants for one of the user, but not thru a role. Any advice on  how to go about it via a role and on creating synonyms based on type of account/access levels ?

                   || CASE
				WHEN object_type IN ('VIEW')
                          WHEN object_type IN ('TABLE')
                          WHEN object_type IN ('FUNCTION', 'PROCEDURE', 'PACKAGE', 'TYPE')
                          WHEN object_type = 'SEQUENCE'
                   || ' ON "'
                   || owner
                   || '"."'

Open in new window

Dear Experts,

 I have a table1 containing Sales data for all times
Month | Customer | Product | Quantity | Revenue

I have another staging table contain data for specific interval of months:
Month | Customer | Product | Quantity | Revenue

I want to update the Revenue & Quantity to be 0 in table1 for all the rows which are in the same months interval of staging table & not exist in staging table

each row is identified by Month, Customer & product

I tried the following solution bu unfortunatly ot takes more than half an hour how do I can enhance it ? or provide a better solution

                  update CPDATA.table1 V 
                      SET V.KF_QUANITY = 0 , V.KF_REVENUE = 0 Where ROW_ID in  
                              Select  ROW_ID FROM CPDATA.STAGINING T Where MONTH >=  StartInterval AND MONTH <= EndInterval  AND
                                   NOT EXISTS   
                                          ( SELECT * FROM CPDATA.STAGINING S  WHERE T.MONTH =S.MONTH  AND T.CUSTOMER = S.CUSTOMER
                                                 AND nvl(T.PRODUCT,'p')  = nvl(S.PRODUCT,'p')  )                                                 

Open in new window

Hi EE,

I just installed the Oracle Client Drivers for windows and I can't get TNSping to work. The cmd says the command isn't recognised.


I looked the PATH environmental variables this what I found:

D:\app\client\adm_icameron\product\12.1.0\client_1;D:\app\client\adm_icameron\product\12.1.0\client_1\bin;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;%SYSTEMROOT%\System32\WindowsPowerShell\v1.0\;C:\Program Files (x86)\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn\;C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\;C:\Program Files (x86)\Microsoft SQL Server\140\DTS\Binn\;C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\;C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\;C:\Program Files\Microsoft SQL Server\130\Tools\Binn\;C:\Program Files (x86)\Microsoft SQL Server\130\DTS\Binn\;C:\Program Files\Microsoft SQL Server\130\DTS\Binn\;C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn\;C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\;C:\Program Files (x86)\Microsoft SQL Server\120\DTS\Binn\;C:\Program Files\TortoiseSVN\bin;C:\Program Files (x86)\VisualSVN\bin

I checked both the oracle folders and I couldn't find the executable TNSping.exe for some reason it didn't install.

Below are screenshots of my installation of the Oracle client.

Does anyone know how I get the TNSping utility on this server?

Any assistance is appreciated.

Thank you.
Free Tool: Subnet Calculator
LVL 12
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

i want to Write clob data into a text file without using utl_file???
hi team,

i have a column which has values separated by a coma,
I need to extract these values and display them as rows . Any help is really appreciated.

select regexp_substr('cricket,football,hockey','[^,].*',1,level)
from dual
connect by regexp_instr('cricket,football,hockey',',',1, level )>0;

I tried it , it did not work. Is there any other mechanism using which I can extract the data as below

Hi Team,

Below is the query which I have written to get the position of 0 in the input string

select instr('OracleO is OracleO','O',1, level)
from dual
connect by instr('OracleO','O',1, level )>0

Iam getting only 1,7 ,  but i need to get 1,7 ,12,18.

any help is really appreciated.
I'm going to start a new project on oracle forms...In couple of days I may have some questions regarding validations are going to use in Oracle forms...I need an expert in oracle forms and reports. I'm a intermediate developer. If I found an expert...I can upload all the requirements.


Administrative Comment

You might want to use the Ask a Question button at the top of the screen. That is the best way to reach the most Experts.
Hello expert,
Using Oracle SQL Developer to import Data from Excel
When I get to the dialogue box marked Column Definition get
screen shot attached. When Next is clicked get error message
Validation Failed Sever Errors Must be corrected before proceeding.
Column definitionHave checked that the datatype is Number in the Excel spreadsheet
for EMPLOYER_ID and is Number as Data Type in the Oracle table.
Also Phone_Area and the other phone fields are Text in ExceL
(because if it is Number Oracle omits the first number) and is
VARCHAR2 in the Oracle table.

Also tried converting the Excel file into a CSV file but get the same result.

How do I get the Column Definitions to line up?

My shop currently uses both Oracle 11g Single-Instance, and Oracle 12c RAC databases.  A contingent of managers, co-workers and peers are very interested in pursuing a PostGress solution.  Can you point out some of the pros/cons of both database vendors and their solutions?  By the way my application has requirements for 7/24, high availability, load balancing, and the requirement to perform Point-In-Time (PITR) when and if required.  Also database needs to be hosted in an Linux environment.

Oracle DatabaseSponsored by jamf





Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.