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

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
PMI ACP® Project Management
LVL 13
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

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
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
Starting with Angular 5
LVL 13
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

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
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
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
HTML5 and CSS3 Fundamentals
LVL 13
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

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
I have a oracle form that the user  loads data. The date format the user uses is different than what I have in my list of values. Need help in adding this format

I have added the format the user uses. Thanks in advance
date-format-list-of-values.jpg
user-date-format.jpg
0
hi,

right now we try to migrate oracle view to MariaDB but most of the oracle view we have need to user this oracle system view:ALL_TAB_COLS , which MariaDB do not have and therefore gives error.

when we migrating VIEW containing ALL_TAB_COLS, what should we do in MariaDB?
0
Hi Experts,

Please provide clarification on below plsql statements.

CURSOR PAY_CUR IS
SELECT PAYID FROM PAYHISTORY WHERE FLAG_C = 'P';  //O/P-->1001,1002,1002,1004,1005

			 
			 
 TYPE CGGRPID_TT IS TABLE OF GRP_LINK.CGGRPID%TYPE INDEX BY PLS_INTEGER;//  What is the use of %TYPE INDEX BY PLS_INTEGER
 L_CGGRPID_TT CGGRPID_TT;
 
 
 l_ql_sql:= 'SELECT CGGRPID FROM grp_link';//o/p:1001,1002,1002,1004

          
EXECUTE IMMEDIATE l_ql_sql BULK COLLECT INTO L_CGGRPID_TT USING  to_char(PAY_REC.PAYID);
//from the above 2 queries outputs 1005 data will be insert in L_CGGRPID_TT?
//advantage of USING clause is to compare the ids or any other purpose

Open in new window

0
Hello Experts,

The example here is a dummy data. I am trying to merge a Student records into one. Please provide your guidance how to achieve this.
 
Current result:

StudentID Name 'Submit Date'                     'Approved by' 'Approved by ID'

1234,        SAM, '10/01/2018 4:04:18 PM',     Hary,              hry243

1234,        SAM, '10/02/2018 12:30:51 PM',   John,              jhn334

 
Expected result:

StudentID      Name     'Submit Date 1'                'Approved by 1' 'Approved by ID 1' 'Submit Date 2'                     'Approved by 2' 'Approved by ID2'

1234              SAM       '10/01/2018 4:04:18 PM', Hary,                 hry243,                  '10/02/2018 12:30:51 PM',          John,                  jhn334
0
I'm using a Oracle 10g Database. One of the tablespace was almost full. So I've increase the database size with the command alter database resize.
And the i've used a script to estimate the tablespace's sizes. The size of my tablespace have been increased, but the physical size is now greater than the allocated one.
Weird?
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.