i would like to know how can i used the OR "|" with REGEXP_SUBSTR
SELECT REGEXP_SUBSTR('500.90 Oracle Parkway, 12-12-2000 78 Redwood Shores, CA','([0-9]{1,2})-([0-9]{1,2})-([0-9]{4})') "REGEXPR_SUBSTR" FROM DUAL;this two condition ([0-9]{1,2}) ([0-9]{1,2}) ([0-9]{4}) or this i did it like this
Current Output ( ADJ#1-2M1YE2 TYPE 20100524 20100624 1MO/0DY ) ===============================================================================================
1) I would like to alter that code above and substr the back 68 characters only with the 12 spaces padded in front, and the trailing nulls to total 80 characters staying as is. How would I put the below substr into that piece of code above?
2) I also would like the take whatever else there is preceding the 69th character counting it from the back and calling the column ADJ_FIRST_LINE.
Desired Output2 (ADJ#1-2M1YE2 TYPE)
No leading or trailing nulls and This is not a fixed amount of characters. ===============================================================================================
Result would be:
ADJ_FIRST_LINE ----------------- ADJ#1-2M1YE2 TYPE ADJ_SECOND_LINE --------------- 20100524 20100624 1MO/0DY ===============================================================================================
Can we have substr(date) as column name. I am trying to create a query where in I need to achieve something like this select s.xyz Today,s.abcd "6 Months ago" || to_char(add_months(sysdate,-6),'Mon rrrr') ||')'from sales_tab s.
DECLARE var VARCHAR2(4000); BEGIN SELECT DBMS_LOB.SUBSTR(v_clob,4000,1) INTO var FROM test_clob; END; ** v_clob is a CLOB column in test_clob table.
I get the below error:
wrong number or types of arguments in call to 'SUBSTR'"SYS"."DBMS_LOB"."SUBSTR": invalid identifier...I have execute privileges to DBMS_LOB.SUBSTR function.
I would like to have a column heading as follows in double inverted comma. but sqlplus environment returns column heading length equal to output value.
SQL> select substr('The independence day', 5,12) "Example of substr function" 2 from dual;
Example of s ------------ independence
I know that default column heading length is 30 character long.
but my column heading is less than 30 character long (which is 26)
How can i have column heading as Example of substr function?
I have a procedure with 20 parameters, acutely it is to update a table and each param represents respective columns in a table. I want to update only few selected columns(random), as of now am passing Null as param values for remaining.
Is there any way to ignore the unnecessary parameters instead of passing NULL value.My Proc call looks like...
In our project we have many instance running with Oracle in one solaris zone. We are in the process of cost reduction so planning to bring the CPU in shared pool and reduce them.
if we can bring all the NUP (Named user Perception) CPU on one shared pool. Will it be cost effective and is there any problems in performing such change.
I will be installing SOA Suite with OSB in Exalogic for testing. It is not for the production environment and very simple application will be used for testing. Database admin asked me what size database would be required but I did not find clear answer in the documentation anywhere.
i have a good query but I thought i know the solution but actually I didn't it's very simple and straight forward but i didn't catch the rope terminal to follow now I have the following code
NOW I NEED A QUERY which will select the only translator who knows the languages written in where clause like if i specify two languages like 'italy' and 'english' the query should retrieve to me GH and SE also if i passed languages 'spainsh' and 'persia' it should return JH only
but if i passed languages like 'Italy' and 'Dutch' it should not return any thing (just : no rows selected)
begin insert into plc_dw_dry_run_fic_rfsh values (424740,'1','','LTML','48000'); insert into plc_dw_dry_run_fic_rfsh values (424736,'1','','LTML','32000'); insert into plc_dw_dry_run_fic_rfsh values (424738,'1','','LTML','128000'); insert into plc_dw_dry_run_fic_rfsh values (424783,'1','','LTML','96000'); insert into plc_dw_dry_run_fic_rfsh values (424789,'2','','LTML','96000'); insert into plc_dw_dry_run_fic_rfsh values (424750,'1',198,'LTML','10000'); insert into plc_dw_dry_run_fic_rfsh values (424760,'1',199,'LDFM','20000'); insert into plc_dw_dry_run_fic_rfsh values (424770,'1','','LTML','192000'); end;
Rules --------- 1) First next_cpn_lvl_id1 should be considered (next_intrvl_code can be anything), if next_cpn_lvl_id1 is 198 or 199 then new_ltr_code should have 1a.
2) If next_cpn_lvl_id1 is not 198 or 199, then next_intrvl_code should be considered
a) if next_intrvl_code is LTML and mod of next_intrvl_value and 96000 is zero then new_ltr_code should be 1a b) if next_intrvl_code is LTML and mod of next_intrvl_value and 48000 is zero then new_ltr_code should be 1b
3) If 1 & 2 are not satisfied, ltr_code should be assigned to new_ltr_code.
to trace. but this trace file is mixing with another trace files in udump. did some Google and found that we can use
ALTER SESSION SET TRACEFILE_IDENTIFIER = "MY_TEST_SESSION".
but this will generate trace file for sys user account not for the desired user session. how we can define trace file name for desired user session trace.
There are tablespaces where the current usage is crossed 90% and we are getting alerts.I want to know how much space required to add for these tablespaces ( datafiles) so that current usage can be reduced to 80 %. or 70 % Is the any SQL query to find this information . oracle 10 g database. .
For procedures, we need to grant EXECUTE privilege.
For example:
grant execute on scott.process_salary to john;Lets say I have a function SCOTT.GET_EMPIDS and I want another user john to be able to invoke this fuction. So, what is the privilege that needs to be granted to John.
This is my first post in this portal. I want display the details of emp table.. for that I am using this SQL statement.
select * from emp where mgr=nvl(:mgr,mgr);
when I give the input as 7698 it is displaying the corresponding records... and also when I won't give any input then it isdisplaying all the records except the mgr with null values.
1)I want to display all the records when I won't give any input including nulls 2)I want to display all the records who's mgr is null
Is there any way to incorporate to include all these in a single query..
I have this query that returns results that contain duplicates(somewhat). I only want either the FIRST or LAST (either one is fine). Here is the query:
select unique PLLA.attribute4, PLA.item_description from po_lines_all PLA, po_line_locations_all PLLA where PLLA.po_line_id = PLA.po_line_id and PLLA.attribute4 is not null
So my output is something like this:
RCE12 This is an item for AUL1 RCE13 This is an item for PWEILL RCE14 This is an item for AUL1
I just want either the RCE12 or RCE14 record and not both since they both have the same description.
I'm experiencing some infinite loop for my delete. I tried many way to deal with this problem but still take too much time. I will try to be clear as possible.
I have 4 implicated table in this problem.
The deletion is done depending of the pool_id given
Table 1 contain the pool_id Table 2 the ticket_id foreign join ticket_pool_id with the pool_id Table 3 ticket_child_id foreign join ticket_id with the ticket_id Table 4 ticket_grand_child_id foreign ticket_child_id join with the ticket_child_id
Concerned count for each table 1---->1 table 2---->1 200 000 table 3---->6 300 000 table 4---->6 300 000
So in fact it`s 6.3M+6.3M+1.2M+1 row to be deleted
Here`s the constraint : -No partintionning -Oracle version 9 -Online all the time so no downtime neither CTAS -We cannot use cascade constraint -The normalization is very important
Here`s what I tried: -Bulk delete -Delete with statement (In and Exists clause) -temp table for each level and 1 level join -procedure and commit each 20k
None of those worked in a decent time frame like less then one hour. The fact that we cannot base a delete on one of the column value is not working. Is there a way I'm getting desperate now
