How to Check for party names contains standalone four length keywords of consecutive letters(both upper and lower case)
For.e.g. "VMWARE ABCD"
"Jack XWyz jon"
output shoud be: abcd
ABCD,
XWYZ,
like this i want to remove the consecutive characters.
what is the purpose of over and partition by keywords in analytical functions
View 3 Replies View Relatedi m using oracle 10g forms. in one form i m using post keyword when i use this function then records saved in temporary database but i want to remove one record from this form but it is allrady saved through post.
View 1 Replies View RelatedI'm having a report deriving data from the database, words are written properly in the database, but in my report some words have space between the letters
View 1 Replies View RelatedI'm in a bit of a pickle with some SQL syntax, and while my Oracle-fu is weak, an associate with SQL skills is also stuck. I am developing a database for my department that is backed by Oracle Enterprise 11g and the front end is ChemAxon (basically, chemistry database software). The short of it is that I have one large table with every compound from different groups. Each row has a flag (column PI) on it that indicates which group it belongs to (let us say 'Smith' and 'Jones' for example). The software can apply row-level filtering which will basically only show the rows that a particular username are allowed to see.
I have a FLAG_TABLE table, which contains two columns: USERNAME and FLAG. An example set up is this:
USERNAME FLAG
------------- ----------
smith_minion Smith
jones_minion Jones
jones_minion Smith
The software automatically applies a SQL filter that begins with:
SELECT DISTINCT CHEMAXON.STRUCTURES."ID" FROM CHEMAXON.STRUCTURES WHERE
I can set up filtering to work dandy, such that when smith_minion logs in, he can only see rows with the Smith flag (or jones_minion can see both Jones and Smith) by using the filter:
STRUCTURES.PI IS NULL or STRUCTURES.PI IN(SELECT ALL FLAG from FLAG_TABLE where USERNAME = '__IJC_USERNAME__')
('__IJC_USERNAME__' is how the third party software passes the logged in username into SQL)
But we have a new problem: there also exists a master user (chemaxon) who needs to see every row no matter what the flag. The row filtering is applied no matter who logs on, so we need to set up the SQL filter to basically say "If chemaxon, then select all rows, otherwise, select rows based on the username". This is proving a problem as the select statement must be prefaced with SELECT DISTINCT CHEMAXON.STRUCTURES."ID" FROM CHEMAXON.STRUCTURES WHERE.
I've tried using DECODE in a few capacities, but I am always thwarted. My last attempt was:
STRUCTURES.PI IS NULL or STRUCTURES.PI IN (DECODE('__IJC_USERNAME__','chemaxon',(!='something'),(SELECT ALL FLAG from FLAG_TABLE where USERNAME = __IJC_USERNAME__));
But this throws a ORA-00936 missing expression error, with a * under the != portion (I test it by replacing '__IJC_USERNAME__' with 'CHEMAXON').
I have 20 tables. In all 20 tables, some of column names are same and some are different. I need to find all column names in all 20 tables that have same names.
create table t1 (
col1 varchar2(10),
col2 varchar2(10));
create table t2 (
col1 varchar2(10),
col3 varchar2(10));
create table t3 (
col1 varchar2(10));
Imagine I have the following function:
FUNCTION normalize(str IN VARCHAR2) RETURN VARCHAR2
IS
BEGIN
RETURN TRANSLATE(LOWER(str),
'äàáâãăāåąæčçðďéèëêěĕėęğģġîĭïīìíłļľŀñńňņöóòôõσøőřśŝšşţüúùûǔųūůŵýÿżźžżαβßγδεζŋηικλμµνξπρσςτυφω',
'aaaaaaaaaaccddeeeeeeeegggiiiiiillllnnnnoooooooorsssstuuuuuuuuwyyzzzzassydeznniklmmnxprsstufo'
);
END;
I'm tired to add missing characters in this list...
What I would like is that all characters with an accent or diacritics should be replace by their "base" letter (ë -> e) and Greek letters to be replace by their corresponding values (ω (omega) -> o)
I have 11.2.0.3 Db in HP UX 11.31, I have to spool a table output in csv format which contains arabic letters in few columns. my spool output is not showing the arabic letters properly.
[oratest@]$ echo $NLS_LANGAmerican_America.
AL32UTF8 V$NLS_PARAMETERS NLS_LANGUAGE
AMERICANNLS_TERRITORY
AMERICANLS_CURRENCY
$NLS_ISO_CURRENCY
AMERICANLS_NUMERIC_CHARACTERS
.,NLS_CALENDAR
[Code]....
I have this 3rd party tool, which are running SQL queries. I need to see what queries the tool is running and capture them. I enabled tracing but that's not working, as the tool doesn't establish connection, it connects when it has to run the query and then gets disconnected.
View 2 Replies View RelatedI had third party reporting system,in which i could not avoid time selection in the screen. So if a user selets date and time or date alone .i should get only in date foarmat i.e(DD-MM-YYYY)
i had tried from third party tool liket0_char (P_date,'DD-MM-YYYY').But i get error ORA-01841: (full) year must be between -4713 and +9999, and not be 0
I've edited the XML on the Chart Attributes page but I don't find atrtributes to rotate the y-axis title (<text>TITLE</text>) nor the letters themselves in the title. What I'd like is something very close to
T
I
T
L
E
But what I get is a horizontal title rotated -90 degrees. I've looked at lots of Anychart documentation but don't find anything that seems to fit with the XML already given on the Chart Attributes page. I've seen the "rotate" attribute but I don't know where it would go and even then, I'm sure it only rotates the title as a unit and not the individual letters.
Upgrading one of the 9i database to 11g that supports a 3rd party software - ***Vendor provided an over-simplified documentations*** and recommends moving from 9i to 10g before going to 11g. A few changes from 9i to 10g.
1) db_block_size
2) character sets
etc.
Anyway, created the database DBUPGTEST on 10.2.0.1 (ultimately moving to 11gR2, so no point patching to 10.2.0.5, is there?) with all the parameter changes. At this point, these are the 2 db in play:
Current production db: Oracle 9i - PROD dbname => 2048K db block size
Current migrating db to: Oracle10g - DBUPGTEST dbname => 8192k db block size
Steps
According to vendor notes / documentation,
1) create db
2) exp full from 9i
3) imp full to 10g
Problems
1) import ended with completed unsuccessful.
2) user accounts are imported (because their default tablespace is USERS - which had already been created during DB creation); but, user accounts (schema accounts) with a different default tablespace are not imported.
Looking at the imp.log - seems like it's complaining about the db_block_size during tablespace creation - which explains why the schema accounts are not imported; because the tablespace was not created.
My questions
1) How do I import to 10g? Can I create all the tablespace in 10g first? Then import? Will it crap out because it already exists? Or will it import the objects in the schema?
2) How do I refresh data from PROD? Remember this is 9i and most of the expdp functionalities are not available. And I cannot re-exp and re-imp because there are steps (sql to run) after moving to 10g to fix some software upgrade table mappings. If I re-exp from 9i and re-imp to 10g, won't I have to re-run all those steps before the apps will run?
I have a two node RAC (11gR2) on Unix and I want to restore it into a single node server using RMAN. Can I tar the oracle home and grid home and untar it on the new server where I can recover the database using RMAN? or do I have to have a fresh standalone oracle installation and do the RMAN recovery?
View 2 Replies View RelatedI need to know if a customer appears at least 4 consecutive days. Here's an example of data
CLI_ID , DATE
-------------------------
123 , 2010/10/01
123 , 2010/10/04
123 , 2010/10/05
123 , 2010/10/06
123 , 2010/10/07
123 , 2010/10/08
123 , 2010/10/10
456 , 2010/10/01
456 , 2010/10/02
456 , 2010/10/03
456 , 2010/10/06
456 , 2010/10/07
456 , 2010/10/08
456 , 2010/10/11
In the example the client 123 appears from 2010/10/04 to 2010/10//08 (5 consecutive days), so this client must appear in the output. In the example customer 456 does not appear at least 4 consecutive days, so should not appear in the output.
There is a requirement in my database that I want to restrict the user from directly running queries on database from third party tools such as pl/sql developer and toad.
There is a utility in SQL product_user_profile through which this can be done but it is only restricted if you run the query through sql plus. If I want to restrict and (give suppose select,insert) to a user for directly running queries through PL/SQL.
Is it possible to create a completely self-contained installer that deploys all the Oracle files along with the application (i.e. the user doesn't need to install instant client, mess around with configuring path environment variables, etc.)?
[URL]
What if as part of the installer, I need to test a connection string entered by the user? That seems to be the sticking point for me. Even after installing the instant client (from the x64 xcopy zip file using install.bat), the installer is not able to connect to the database.
And, the application is 64 bit only (we need the 64 bit Crystal Reports runtime), meaning I've got the 64 bit Oracle instant client installed. I suspect that might be the problem since the msiexec installer process is 32 bit.
I use the "Media Resource" RESTful service type to generate custom JSON objects via CLOB-returning functions. This worked great in APEX Listener 1.1.3. However, on APEX Listener 2.0 I get status 500 on a RESTful service which works fine on 1.1.3 (which generates a clob > 4000 bytes), and the following in the server log:
SEVERE: ORA-22922: nonexistent LOB value
oracle.dbtools.common.UnrecoverableException: ORA-22922: nonexistent LOB valueThis seems to be the same issue as reported here:
APEX Listener EA2 Standalone CLOB error
As stated by thoechst in that thread:
>
It is as though the function is getting cast as a varchar2 somewhere. I verified that the function will correctly return a large result to other sources (dbms_output, for instance).
>
Versions:
APEX Listener 2.0
WebLogic 10.3.6 on Windows 2008 SE R2 x64
Oracle DB 11.2.0.3
I have a table with three columns: terminal, place and batch. How can I check for missing batches?
select * from transactions;
terminal place batch
84812
84813
84814
84816
84821
84823
84824
84825
I have a table T with columns
Col1 Col2
1 A
2 B
3 C
4 D
[code]..
I want to do group ranking in desired col3 in such a way that it checks for different values across consecutive rows under col2 and assigns a number to each group. Just when two consecutive rows in col2 have same value then the group ends and the next group starts.
So my desired output is:
Col1 Col2 Col3
1 A 1
2 B 1
3 C 1
4 D 1
5 D 2
6 A 2
[code]...
Here you can see that the first four rows under col2 are unique i.e A,B,C,D so col3 assigns this as group number 1. It ends at row 4 becuase row 5 also has value D under column 2. So in other words, each group must have all unique values and there should not be any repetition. For example, see group 3 (under col3) in above desired output; it starts from row 9 and ends at row 11 because row 12 also has value 'C' and the value 'C' has already occurred in group 3 in row 9.
I want to achieve this SQL. I tried using Dense rank but couldn't go through. I want the shortest possible query to acheive this.
I am pulling information from a view to get the current results and placing them into a cursor which then inserts the results into a temp table.
Current Results:
JOB_NAMESCHEDULED_TIME STATUS
JOB_1 02/23/2010 13:25:00Failed
JOB_1 02/22/2010 13:25:00Failed
JOB_1 02/21/2010 13:25:00Failed
JOB_1 02/19/2010 13:25:00Failed
JOB_2 02/21/2010 13:25:00Failed
JOB_2 02/18/2010 13:25:00Failed
JOB_2 02/17/2010 13:25:00Error
JOB_2 02/15/2010 13:25:00Error
Needed Results:
JOB_NAMESCHEDULED_TIME STATUS
JOB_1 02/23/2010 13:25:00Failed
JOB_1 02/22/2010 13:25:00Failed
JOB_1 02/21/2010 13:25:00Failed
JOB_2 02/18/2010 13:25:00Failed
JOB_2 02/17/2010 13:25:00Error
How to migrate Oracle Standalone Database to Oracle Real Application Cluster?
View 1 Replies View RelatedI am doing a upgrade from 10g2 standalone (with out ASM) to 11g2 2 node cluster (on ASM) and the database is some what 1TB of the size. My problem is if I do a export and import the live system need to be down for over an a day. is there a better way of doing this and how ?
View 6 Replies View RelatedI planing use latest APEX listener and Apache with mod_jk on new server.I wonder what are pros and cons use APEX listener standalone comparing when deployed to J2EE container e.g. Glassfish? URL.....
View 4 Replies View RelatedWe have to upgrade our existing 11g R-1standalone DB to Cluster & ASM. Upgrade DB from 11.1.0.7 to 11.2.0.3 & Enable RAC.
View 7 Replies View RelatedOur goal is to create an light weight packaged application and provide to any customers who wants to install and use the tool.
Can an application with database be built on Apex and created as packaged installable, instead of hosting in any Application , middle and database server. A kind of quick, cost effective and light weight installation? If yes,
I am trying to use the Apex listener 2.0 (instead of HTTP server). I installed and went thru the configuration of the listener, but I must be missing something. When I do the java -jar apex.war to start it up in standalone mode, I get the configuration properties etc., but when I open up my browser and try to bring up APEX with http://localhost:8080/apex/ I get a 500 - Internal Server error with the info below.
***********ERROR*********** init: # headers=43 declare nm owa.vc_arr := ?; vl owa.vc_arr := ?; begin owa.init_cgi_env( ?, nm, vl ); htp.init; htp.HTBUF_LEN := 63; ? := sys_context('USERENV','SID'); end; SID:880 CALL: begin apex; commit; end; BINDS PAGE CALL: declare nlns number := 999999; l_clob CLOB; lines htp.htbuf_arr; l_buff varchar2(32767); l_clob_init boolean:= false; l_file varchar2(5); l_doc_info varchar2(1000); begin OWA.GET_PAGE(lines, nlns); if (nlns > 1) then for i in 1..nlns loop if ( length(lines(i)) > 0 ) then if ( ( lengthb(l_buff) + lengthb(lines(i))) > 32767) then if (NOT l_clob_init) then dbms_lob.createtemporary(l_clob, TRUE); dbms_lob.open(l_clob, dbms_lob.lob_readwrite); l_clob_init:=true; end if; dbms_lob.writeappend(l_clob,length(l_buff),l_buff); l_buff := lines(i); else l_buff := l_buff || lines(i); end if; end if; end loop; end if; if (l_clob_init) then
[code]....
I'm trying to setup a dev machine to run APEX with RESTful services ahead of using Oracle's Cloud Database. Using Personal Edition, APEX 4.2.1 and the 2.0 Listener is up and running in standalone more. The problem is when hitting 'Test' on a selected REST Resource Handler, it comes up with an APEX 404 not found error, such as on the example HR REST handlers and a new test one setup.
I've followed all of the APEX and Listener instructions, run apex_rest_config.sql, ensured the various users are unlocked and tried both http and https (even though I've added the no SSL flag in the Listener config per standalone instructions.
I want to do cloning of a database on my standalone pc having oracle 10g on windows 7. PC is P4 with 2GB RAM.
The database is installed on my pc and i want to clone it on the same pc.
i want to restrict the user if he/she enters any 3 consecutive sequence of numbers,characters,alphanumerics and special characters for example aaa, aAa, @@@, ---- , 111, 123 are not valid.
valid sequences are a1w,?1A,aa1,WW2,78a,-#a
i want to show the invalid sequence in a single query using regular expression function. suppose for example if user enters aaa,$$$,123 then the query output is aaa,$$$,123.
i have written two different queries for that but i want a single query
SELECT REGEXP_SUBSTR('EEE','([a-z])\1\1',1,1,'i') FROM DUAL;
SELECT REGEXP_SUBSTR('111','([0-9])\1\1',1,1,'i') FROM DUAL;
SELECT REGEXP_SUBSTR('@@@','([^-$])\1\1',1,1,'i') FROM DUAL; -it is not checking for -(hypen) characters
any 3 consecutive characters from key board.
SELECT * FROM table WHERE id = $x
$x being a range of non-consecutive values like so:
1,3,5-9,13,18,21 and so on...
I realize I can query using an array of operands and such, but these ranges will be in upwards of 100 or more items. I want to minimize the number of queries I have to do and the length of them. Is there any resource you can point me to that can optimize something like this?