Oracle Database

79K

Solutions

26K

Contributors

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

Here is my code


SELECT accountnumber, acct_no_id,  length(acct_no_id) as geoscout_len,                                                                                                                                                                                                                                                                                            
DATA.HELPERS.Convert_accountnumber,                                                                                                                                                                                                                                                                                           
                      (                                                                                                                                                                                                                                                                                          
w.uwi                                                                                                                                                                                                                                                                                          
                      , DATA.HELPERS.geoSCOUT_Format                                                                                                                                                                                                                                                                                          
                      , Tk_reentry_conflict_ind =>  'N') as Acct_id                                                                                                                                                                                                                                                                                          
FROM Main                                                                                                                                                                                                                                                                                          
WHERE  acct_no_id is null                                                                                                                                                                                                                                                                                          
and substr(AccountNumber,1,2) != '1C';

Output
accountnumber            acct_no_id,        geoscout_len      DISPLAY_ID                         ROW_CHANGED_BY            ROW_CHANGED_DATE            Acct_id
103113000418W203      null                  null                         103/11-30-004-18W2/03      Key Change Script              08-JUN-17                      00421830110300000
101071502619W300      null                  null                         101/07-15-026-19W3/00      LOAD_SK_MONTHLY.SIR      26-MAY-16                      02631915070100000
102042600131W102      null                  null                    102/04-26-001-31W1/02      LOAD_SK_MONTHLY.SIR      22-MAR-17                      00113126040200000
104032600131W102      null                  null                         104/03-26-001-31W1/02      LOAD_SK_MONTHLY.SIR      22-MAR-17                      00113126030400000
102101803728W302      null                  null                         102/10-18-037-28W3/02      LOAD_SK_MONTHLY.SIR      23-AUG-17                      03732818100200000
102053000130W102      null                  null                         …
0
Ensure Business Longevity with As-A-Service
Ensure Business Longevity with As-A-Service

Using the as-a-service approach for your business model allows you to grow your revenue stream with new practice areas, without forcing you to part ways with existing clients just because they don’t fit the mold of your new service offerings.

Hi,
I am using Visual Studio on my local windows 10 workstation to test an SSIS package which was not created on this machine.
There is a Connection Manager  which connects to an Oracle database as part of the package.
When I click Test Connection I get the error displayed .
ora
Is there some oracle  component I need to install locally?
Any guidance appreciated.
0
In Oracle to select from a specific partition only in a table, is there any difference in speed between...

select * from table_name Partition(xyz)
vs
select * from table_name
where partition_column = 'xyz'

?
0
This SQL command is causing an Invalid Character Error - -2147467259-[Oracle][ODBC][Ora]ORA-00911: invalid character

select * FROM LIMS.SAMPLES WHERE LIMS.SAMPLES.HSN = 804010001 and Format$(LIMS.SAMPLES.COLLECT_DATE, "MM/DD/YYYY") = #03/01/2018# order by LIMS.SAMPLES.COLLECT_DATE


Do you see anything in the command that would be the issue?
0
Hi,
I have data like below

just want to pick min emplyee_id when ind=1 can we use partition by to do it.



ind   employee_id  min_empl_id
1           99                       98
0          101                      98
1          98                        98

Thanks
0
hi,

how to find any Oracle function has has UDT ?
0
I have an Oracle 10g r2 database with table name TB_Docs
 The column docname has values like
   Doc1_SubDoc_1.Doc
    Doc2_SomeDoc_2.Doc
   Docxx_MyFile_150.pdf

I need a query to give results as follows
     
Doc1_SubDoc      1
 Doc2_SomeDoc  2
Docxx_MyFile       150
0
sample values will be like
   Doc1_R1.PDF
   Doc2_1.XLSX
  Doc2_2.XLSX
    Doc3_Sub__R12.XLSX
   Doc3_Sub__R13.XLSX
  Doc4_Sub_1.Doc
 


  I need to get the result  as follows (  The string is split and Sorted by DocNo,Rev)
  DocNo          Rev
    -----         ----
   
 Doc1              1
  Doc2             1
  Doc2             2
  Doc3_Sub         12
  Doc3_Sub         13  
  Doc4_Sub         1

Appreciate if this can be done as multiple case   looking for substring  ( Regular expression puts revision as 0 if documents of no format present ( Instead of null)
0
hi,

any oracle query to find any table AUTO_INCREMENT column ?
0
How do I call a procedure with parameters like this please help in pl sql

Execute PPDM37_TEST_CURRENT.ADDALIASEDASSOCIATE PARAMETERS CODE = 36388
BA_NAME = 'Millennium Land (555) Ltd.'
BA_SOURCE = 'SIR'
CREATOR = 'STAVENGWA'
NM_ABBR = null
BA_TYPE = 'COMPANY'
0
CompTIA Security+
LVL 13
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

Is there a tool similar to SQL Management Studio, that allows you to query, create jobs, and dump data into tables from a Oracle database?
0
Hi,  I want to bring back everyone in a table whose max school year is 2017. They may have an entry in 2018

Year            Student ID
2018           123
2017           123
2016           123
2017           124
2016           124

In the above example, I only want to see Student 124

Thanks in advance
0
Please provide clarification on below statements,I have to improve the performance of the queries.

1.clustered index and non clustered index
clustered index will give better performance than non clustered index is it true?
if yes
how to change existing table non clustered index to clustered index please provide a sample if possible.

2.sql select statement i have used predefined function

function used in sql select statements, will it reduce the performance of the query?
if yes
 below sample sql statement i have used trunc function could you please advise alternative
select * from DLVRY_DTL  where TRUNC(SYSDATE) BETWEEN TRUNC(DLV.UAD_START_DATE) AND TRUNC(DLV.UAD_END_DATE)

Open in new window


3.Is there any way to check unused indexes of a table ?
or we need check select statement where conditons and this need to compare with already created indexes manually.

4.I have CLOB type column called deliverxml which contains xml.
this column does not have index,Is it wise idea to create index for this column
this column i am using in where condition,Please advise need to create index or not required for this column.

5.column name-->filtercondition    datatype-->  VARCHAR2(4000)  
this column i am using in where condition,Please advise need to create index or not required for this column.

6.select statement  query is execution time is taking  in below way
1st time execution-->1.28927433 seconds
2nd------------------>0.38675585 seconds
3rd------------------>0.2717154  seconds

how i will know whethere it is giving better performance or not?
please suggest.
0
Oracle database, using PL SQL
I have duplicates in a table and to find them I have to look at 4 fields.  This is the query I used to find the duplicates.

SELECT a.srv_date, a.proc_code, a.rend_prv_npi, a.cin_number, count(*) as qty
FROM Current_Data_Final a
GROUP BY a.srv_date, a.proc_code, a.rend_prv_npi, a.cin_number
HAVING count(*)> 1;

I need a query to delete the duplicates.

Thanks,
Scott
0
Hi dear EE fellows,

I'm kinda stuck with setting up our new Oracle 18c databases and/or I'm just missing the forest through the trees ;-)

We want to have server A running 18c with OMF, which reside on a SAN storage. Now, we want to have a server B as some sort of cold backup. If server A might crash, we want to start up server B, which should also be able to run with the SAN files. Sure, we'd have to use the last backup, do a recover and apply the archive logs ect... That is NOT the point. How do I have to setup the instance on server B so that it can also run against the SAN storage (e.g. shut down server A, startup server B).
In the end, we want to have 2 instances running on 2 separate servers (where 1 would always be down) which are able to "connect" to the very same SAN storage (OMF).
The databases should/will be Oracle 18c SE2 and no, we don't want to/can't use RAC.

Thanks for your help in advance ;-)

Cheers, Alex
0
Hi Team,

I have doubt with regard to the Private SQL Area in PGA . In this there are two memory structures namely
Persistent Area and Run time Area .
As per all the online documents they say that
persistent Area :- contains bind variable info
run time area :- contains the state info .  I know that whenever we execute a SQL query , data is first loaded into SGA buffer cache . My question is here only ,
how does the data flow from buffer cache to the client , is it stores some where in PGA and then transferred . What is the significance of Persistent area and runtime area in this scenario.

kindly give an explanation . Online documents does not explain this flow.
0
Hello,

I'm working on an XML export and some of the element attribute values actually come from the DB. The thing is, the requirement for these attribute values is that they need to be in camelCase. After quite a bit of "playing around" and searching for examples, I've come up with a fairly decent way of getting most of what I need.

select regexp_replace(initcap(regexp_replace('Hello world, how are you?', '[[:digit:]]', ' ')), '([[:punct:] | [:blank:]])', '')
from dual;

Open in new window


The issue I have is that this really isn't true camelCase. This is actually Pascal case. Using initcap, of course, will capitalize the first letter of each word. In camelCase, the first letter of the first word should be lowercase and the first letter of each subsequent word should be uppercase. Is there an easy way of doing this?

E.G.:
Hello world, how are you? should be "helloWorldHowAreYou" not "HelloWorldHowAreYou"

Note that I'm on a 12c DB.

Thanks,
Larry
0
Customer wants to be convinced that if one node goes down on our two node Oracle RAC 11g database, we will continue processing; albeit perhaps with some latency.  And of course; how much latency. Recently we had a minor glitch at our customer's site, where a Solaris Cluster resource faulted.  This particular cluster resource is associated with our ASM disk groups, "asm_dg_rs".  After much investigation, a standard ASM query, that happens as part of this resource check was taking longer than the expected value of 120 seconds.  Long story short, in summary the query was taking longer because the actual hardware was extremely bogged down.  The server was doing a "zfs scrub", which I now understand scrubbing is similar to the file system check, “fsck”, as it validates blocks on the disks. The
zfs scrub” was scheduled once a week, so we canceled the weekly scheduled job.  The hardware team has since decided the our (2) R810 Dell Servers are memory starved, and the memory will be severely upgraded.  Apparently there was an extreme amount of paging occurring.

So, because of this minor resource fault, that I thought was well explained and solved – customer is extraordinarily concerned about a failure during a critical event.  How can I convince my customer, than our high availability Oracle RAC 11g 2-node database will be able to keep on ticking and mostly like not miss a beat?  Part of the RAC technology of high availability is fault tolerance, where you should be able to …
0
Hi Team,

Below is the query which i have written to convert the columns into rows
Select regexp_substr('RECORDTYPE:VALUE|FIELDNAME:VALUE|FIELDNAME:VALUE|FIELDNAME:VALUE|FIELDNAME:VALUE|FIELDNAME:VALUE|FIELDNAME:VALUE','[^|]+',1,level) dt
from dual
connect by regexp_instr('c|FIELDNAME:VALUE|FIELDNAME:VALUE|FIELDNAME:VALUE|FIELDNAME:VALUE|FIELDNAME:VALUE|FIELDNAME:VALUE', '|',1,level) >0

But Iam getting null rows .
RECORDTYPE:VALUE
FIELDNAME:VALUE
FIELDNAME:VALUE
FIELDNAME:VALUE
FIELDNAME:VALUE
FIELDNAME:VALUE
FIELDNAME:VALUE
NULL
NULL

Also , after converting the columns into rows as above , I need to split the RECORDTYPE:VALUE and FIELDNAME:VALUE into columns . Any help in this regard is really appreciated
0
Active Protection takes the fight to cryptojacking
LVL 2
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

How can you determine if you need to rebuild indexes within a partitioned table? (Oracle)
0
I am creating a web form which allows the application user to query data in the database. the search will only query  one specific table in the database.  Through the web form the user can search on a couple of specific columns in the table, and or a date range. The challenge here is that some of the search criteria is optional. For example the user can search by a particular users last name or first name, or both at the same time.

To build the search I need to do this all in a sql stored procedure, and not from dynamic sql, So in the stored procedure I will need to account for values in the search parameter that are null or non existent. For example, someone might search by entering only the first name, and not the last name. I noticed a stack overflow posts where someone suggested that for sql server I can do a query similar to the one below to account for optional parameters using a check for NULL.

This will work for Sql server only. Does anyone know how I would implement a similar query in Oracle Syntax in order to account for optional parameters in the search? In the below example note that the userID,password, and field2 are optional and the NULL is used in the query to account for the case when the value might be null.

SELECT *
FROM tUsers  WHERE (@userID IS NULL OR RTRIM(Name) = @userID )
  AND (@password IS NULL OR RTRIM(Password) = @password)
  AND (@field2 IS NULL OR Field2 = @field2)

Here is the article I am referring to:

0
Hi,
Below query is taking forever sending explain plan and query

Plan
SELECT STATEMENT  ALL_ROWSCost: 2  									
	13 SORT AGGREGATE  Bytes: 38  Cardinality: 1  								
		12 PX COORDINATOR  							
			11 PX SEND QC (RANDOM) PARALLEL_TO_SERIAL SYS.:TQ10000 :Q1000Bytes: 38  Cardinality: 1  						
				10 SORT AGGREGATE PARALLEL_COMBINED_WITH_PARENT :Q1000Bytes: 38  Cardinality: 1  					
					9 NESTED LOOPS PARALLEL_COMBINED_WITH_PARENT :Q1000Cost: 2  Bytes: 38  Cardinality: 1  				
						7 NESTED LOOPS PARALLEL_COMBINED_WITH_PARENT :Q1000Cost: 2  Bytes: 38  Cardinality: 2  			
							5 NESTED LOOPS PARALLEL_COMBINED_WITH_PARENT :Q1000Cost: 0  Bytes: 28  Cardinality: 1  		
								2 PX PARTITION RANGE ALL PARALLEL_COMBINED_WITH_CHILD :Q1000Cost: 0  Bytes: 13  Cardinality: 1  Partition #: 8  Partitions accessed #1 - #11	
									1 INDEX FULL SCAN INDEX PARALLEL_COMBINED_WITH_PARENT ICRS_WH.INTRVW_INTVWDTL_IN :Q1000Cost: 0  Bytes: 13  Cardinality: 1  Partition #: 8  Partitions accessed #1 - #11
								4 TABLE ACCESS BY INDEX ROWID TABLE PARALLEL_COMBINED_WITH_PARENT ICRS_WH.D_INTERVIEW_DETAIL :Q1000Cost: 0  Bytes: 15  Cardinality: 1  	
									3 INDEX UNIQUE SCAN INDEX (UNIQUE) PARALLEL_COMBINED_WITH_PARENT ICRS_WH.INTVWDTL_PK :Q1000Cost: 0  Cardinality: 1  
							6 INDEX RANGE SCAN INDEX PARALLEL_COMBINED_WITH_PARENT ICRS_STG.LNDINTRVW_CLNTRGSTRATN_IN :Q1000Cost: 1  Cardinality: 2  		
						8 TABLE ACCESS BY INDEX ROWID TABLE PARALLEL_COMBINED_WITH_PARENT 

Open in new window

0
hi,

do you know if MariaDB has package ?

seems only when mariaDB in oracle mode can do start it CREATE it ?
0
hi,

we have a function like this:

create or replace FUNCTION        to_string (nt_in IN varchar2_ntt, delimiter_in IN VARCHAR2 DEFAULT ', ') RETURN VARCHAR2 IS
v_idx PLS_INTEGER;
v_str VARCHAR2(32767);
v_dlm VARCHAR2(10);
BEGIN
v_idx := nt_in.FIRST;
WHILE v_idx IS NOT NULL LOOP
v_str := v_str || v_dlm || nt_in(v_idx);
v_dlm := delimiter_in;
v_idx := nt_in.NEXT(v_idx);
END LOOP;
RETURN v_str;
END to_string;

Open in new window


what is this function suppose to do ?

and how to call this function to make some meaningful return ?
0
I am looking out on my local server and I did search for "Oracle.DataAccess.dll". I can see this file in two different directories. Can an Oracle or Microsoft.NET expert tell me the difference between the bin\2.x and bin\4 folders?

1. C:\dbms\oracle32\product\12.2.0\client_1\odp.net\bin\2.x

2. C:\dbms\oracle32\product\12.2.0\client_1\odp.net\bin\4
0

Oracle Database

79K

Solutions

26K

Contributors

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.