i need to isolate the MVNAME schema from the following job;
dbms_refresh.refresh('"SCHEMA"."MVNAME"');
how to use substr/instr to isolate it?so far I have
select substr(what,instr(what,'"',-1,2),15) from dba_jobs;
But its proving a bit tricky to nail it.
i need to isolate the MVNAME schema from the following job;
dbms_refresh.refresh('"SCHEMA"."MVNAME"');
how to use substr/instr to isolate it?so far I have
select substr(what,instr(what,'"',-1,2),15) from dba_jobs;
I have a table1 with columns serial no and name as below
serial no name
1 john paul
2 john victor
and i have another table1 with columns serialno,firstname and second name as below
serial no firstname secondname
1
2
I want to update my table2 with the name from table1 and divide and insert in first name and second name as below
serialno firstname secondname
1 john paul
2 john victor
i use the query as below
update table2 set
firstname = (select substr(name, 1, instr(name, ' ', 1) - 1)
from table1
)
but the above query not worked.
I need to search a pattern backwards in a CLOB field.Function DBMS_LOB.instr does not work with '-1' offset (where to start the search) as instr does.
Parameters: instr(text_to_be_searched, pattern, offset, nth)
Example: I want to search 'Hello world' for the first instance of the letter 'o' starting from the end, backwards.As you can see, result for DBMS_LOB.instr is null when entered -1 for offset.
select
DBMS_LOB.instr('Hello world','o',-1,1) lob_i,
instr('Hello world','o',-1,1) std_i
from dual;
I am trying this function
instr(','||replace(r_code,' ') ' ' || ', ' , ',' ||r_code || ' , ' )=0
i want to hardcode 'a1', 'a2' from r_code i dont want records from a1, a2
I have a requirement where the user input values will be passed as comma separated string to backend, which is queried against the table using instr. But the index present on the table is not using the index , due to the instr function.How can I create a index in such a way that The instr function uses it.
CREATE TABLE TEST_IDX
(
CCN VARCHAR2(10 CHAR),
SKU_NUM VARCHAR2(10 CHAR),
MOD VARCHAR2(10 CHAR),
SKU_STATUS VARCHAR2(2 CHAR),
RPT_FLAG VARCHAR2(2 CHAR),
CYCLE_AS_OF_DATE DATE,
SMP_IDENTIFIER NUMBER,
MEASURE_NAME VARCHAR2(100 CHAR)
);
CREATE INDEX TEST_IDX1 ON TEST_IDX
(CCN, SMP_IDENTIFIER, MOD, CYCLE_AS_OF_DATE, RPT_FLAG,
MEASURE_NAME);
The below query is going for full table scan due to this.
select * from test_idx where (INSTR (','||'E10000'||',', ',' || ccn || ',') <> 0 OR 'E10000' = 'DEFAULT')
and mod='90396' and rpt_flag='O' and smp_identifier=2
how to recreate the above index so that these queries uses this index.
I have a script that is using the INSTR function to search through a block of data for a specific string (CALL). I am ONLY looking for that string set but unfortunately, there are other words within that block of text that have that string set within it (e.g. CALL_MY_PHONE). Is there any way to make the INSTR search DISTINCT? Below is the code that I am using:
to_number(DECODE(INSTR(ph.block,'CALL'),0,0,1))
Trying to get a number out of an error backtrack
06512: at "SCHEMA.PROCEDURE", line 4
I only need to take the 4 out of this message. Of course the number might be anything from 1 to 10000.
The database languages might differ so I can't do this using
v_line_no:=SUBSTR(v_line_no,INSTR(v_line_no,'line')+5)
As it would not find any 'line' inside the string.
how can i subtr first name and last name select first_name + lastname from employee for example if i have james michael i what to have JMichael
View 4 Replies View RelatedWe have a SSRS Front end screen which sends multi-select column values as comma separated strings to back end ( Oracle 10g) procedure .
The procedure builds the string by inserting single quotes in the following manner.
P_BU_LST is the parameter which have comma separated values
'1234,3456,4577' i.e, BU ids selected by user in front end
the procedure inserts single quotes to this paramer value
i.e., '1234','3456','4577
v_bu_lst := '''' || REPLACE(v_selbu, ',', ''',''')|| '''';
This is used the where clause of the REF CURSOR SELECT query which send the data back to SSRS
ie.,
SELECT BU.*
FROM BU_DETAIL BU
WHERE INSTR(V_BU_LST,BU_ID) <> 0;
INSTR has a chance to fail in this scenario if the value send from the front end is 123456,3456,4577
here 123456 does not exist in table, but it will be true for INSTR and values 1234 from table will be send back to SSRS which is wrong. Earlier I was using a function to convert the comma separated values to multi-rows and treat it like a lookup table.
But the main table has around million records , and each row has to processed against each row of lookup table, which makes it slower. To avoid this I used INSTR which is faster but can give wrong results.
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
[code]...
I want to import a schema from one database schema to another schema b from db STBTST to STATST and from schema CMSSTAGINGB to CMSSTAGINGA
I first want to test this to my own schema (mvanmannekes) CMSSTAGINGA is filled at the moment.
So i've created a dump from STBTST-CMSTAGINGB For importing im using this statement:
impdp mvanmannekes/password schemas=cmsstagingb remap_tablespace=cmsliveb_data:cmslivea_data
remap_tablespace=cmsliveb_index:cmslivea_index
remap_schema=cmsstagingb:mvanmannekes directory=expdp_dir dumpfile=cmstagingb.dmp
I'm getting this:
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "MVANMANNEKES"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "MVANMANNEKES"."SYS_IMPORT_SCHEMA_01": mvanmannekes/********
schemas=cmsstagingb remap_tablespace=cmsliveb_data:cmslivea_data
[code]....
This piece of code returns this below:
RPAD(LPAD(' ',12, ' ') || SUBSTR(adj_second_line,(INSTR(adj_second_line,'~',1,1) + 1),
(INSTR(adj_second_line,'~',1,2) - INSTR(adj_second_line,'~',1,1)
- 1)),80,' ') ADJ_SECOND_LINE
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?
Desired Output ( 20100524 20100624 1MO/0DY )
SUBSTR(adj_second_line,LENGTH(adj_second_line)-69)
===============================================================================================
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
===============================================================================================
i have following table
CREATE TABLE THREAD_SHADES
(
ITM_COD NUMBER NOT NULL,
ITM_DES VARCHAR2(250 BYTE) NOT NULL,
)
[Code]...
Result is
ITM_COD ITM_DES
80064186THREAD TEX-105 SHADE# 7921 (1500 MTRS)
80064187THREAD TEX-40 SHADE#7921 (3000 MTRS)
80114482THREAD TEX 40 SHADE C-8762 1500MTR LOCAL
80130541THREAD TEX-60 SHADE C8676 J&P COAST ASTRA 1000 MTRS
I want a query which should return only Sahde # from above data result must be as
7921
C-8762
C-8762
C8676
I have a following table,
CREATE TABLE checkdata
( col1 VARCHAR2(2000)
);
INSERT
INTO checkdata VALUES
[code]......
I need output as ,
INT8144925446-20110118T123723Z
INT8144925448-20110118T123730Z
How do i use Substr and Regular expression here ?
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.
View 9 Replies View RelatedI am trying to execute the PL/SQL block below:
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.
when I try to run this code
SQL> select
2 regexp_substr('But , soft! What light through yonder window breaks?',
3 'l[[:alpha:]] {4}') AS result
4 from dual;
I get just this ( R
-)
where the result should be like the full sub string which is ( light) not just R.
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 am using the following substr and it works fine on Toad but when i am trying to use within an ETL tool, there getting the error:
substr(PBBDT,length(PBBDT)-1)
Calling <substr> with <2> parameters, but <3> are expected.
A single master schema where many developers are accessing. all share same password.
now i would like to trace all the changes made by each users. so i create a individual users for all and grant permission to access that schema.do i have a possibility of auditing the changes did by each user for that particular schema
We have an application with many separate databases (one per customer). Given they share the same business requirements (service hours, change mgmt etc), we're interested in potentially consolidating the separate DBs (which are relatively small) into separate schemas within a fewer no of databases to reduce the overhead.
Our issue is that the application is hard-coded to use a specific administrator and application connection user name. Changing this is unfortunately not an option.
Given this limitation, is there any possibility to map a generic user into a customer-specific schema based on the database service that they connect to? Each customer connects to different database services but may use the same user name. We considered using private synonyms but this seems to acheive the opposite (i.e. many different users could connect and map to a single users schema). One thing to point out is that where there is a single user name, it is acceptable for a single password to be used across the different customer DBs as they will be a single admin/user.
I would like to create a table in another schema(CBF) as already exist in my schema(TLC) without data but related indexes,synonyms and grants should be include.
How could I do this without using export import. I am using TOAD 9.0.1.
how to take all schema metadata export except one schema (scott)
can i use like EXCLUDE=schema:"IN('SCOTT')
I had done following steps,
schemas(toy,toys)
1) i open the session of toy schema
First i taken backup of table
create table bck20121103_himan as select * from himan;
Backup table is created.
After taking the Backup table
delete himan;(deleting the records)
2) i log in to another session(toys)
exp toys/toys@orcl file=20121103TOYs.DMP TABLES=(HIMAN) /* Particular table is taken*/
3) i log in to toy schema
imp toy/toy@orcl file=<dump file name> TABLES=(HIMAN) INDEXES=N IGNORE=Y
i tried the above statement it taken so much of time..
Later i tried
I log in to toy session
i rename the table with other name.
later i imported
imp toy/toy@orcl file=<dump file name> TABLES=(HIMAN) IGNORE=Y FULL=Y
it's successfully imported.
move the tables with data present in the user scott(full) to another schema named test. In my case scott is in user tablespace and for test schema i have created different tablespace named test_tbs.
View 14 Replies View RelatedHow to use same oracle sequence name in Oracle Database schema as well as timesten schema?
View 1 Replies View Relatedsingle schema setup or multiple schema setups for an application development. Which option is recommended and pros and cons of these methods?
View 4 Replies View RelatedI have create one procedure under my user schema. In that procedure , I am selecting data from another schema's table.
While compiling that I am getting following error->
PL/SQL: ORA-00942: table or view does not exist
PL/SQL: Statement ignored
how I grant access of one schema object to another schema. Currently I am using oracle 10g
A user is using an ad hoc tool similar to SQL Developer called PeopleSoft Application Designer.
He creates a connection to the db, then issues an alter session set current_schema = 'restricted_schema'. The connected user does not have direct privileges on the "restricted_schema" which they call SYSADM.
After changing the schema context in that manner he creates objects in SYSADM. A schema trigger is then fired and grants privileges on the new objects created in SYSADM. Doing the same in either SQL Plus or SQL Developer does not fire the schema trigger.
I think SQL Plus and SQL Dev are working as they should. Altering the session like that does not change your identity - just the schema context. But, when you examine v_$session, the connection with this other tool looks exactly the same as one from SQL Plus or SQL Dev when changing the schema context in the session.
Instead of trying to figure out what this other tool is doing, is there any way for that schema trigger to fire when using this process from one of our tools?