SQL & PL/SQL :: Performing TYPE 2 Operation Using Merge In 10g
Mar 11, 2010
I have a type to table where only few columns are SCD 2. Like address, city, zip.I wrote a code using cursor. But now the requirement is to replace the cursor .how to perform type 2 using Merge in oracle 10g or any better way to perform it without using cursor. Look the code which i have attached with cursor.
CREATE OR REPLACE PROCEDURE Proc_cp_jci_contract_dim_hist
IS
l_fun_ret_value NUMBER;
v_src_id NUMBER;
rec_jci_contract_dim jci_contract_dim%ROWTYPE;
rec_jci_contract_dim_hist jci_contract_dim_hist%ROWTYPE;
err_row VARCHAR2(4000);
ind NUMBER(1) := 1;
[code]....
View 2 Replies
ADVERTISEMENT
Sep 8, 2011
run down of the implications of MERGE by ROWID in such a fashion:
CODE MERGE
INTO XXWT_AP_ACCRUALS_RECEIPT_F EXT
USING (
SELECT PO_DISTRIBUTION_ID,
[code]...
Can this lead to an "Unstable Set of Rows?". Is it possible for the ROWID's to change during the execution of this statement - meaning certain ROWIDs identified in the SELECT will not actually be updated when it comes to the MERGE operation?
Basically, is it sound practice to use ROWID to merge on - in cases where you dont have a WHEN NOT MATCHED condition?
View 8 Replies
View Related
May 31, 2007
Can i use table type in the using clause of merge statement ?
Attached is the code of the procedure
create or replace procedure fnd_sample as
cursor fnd_c is select * from fnd_columns;
type test_t is table of fnd_columns%rowtype;
[Code]....
(APPLICATION_ID,TABLE_ID,COLUMN_ID,COLUMN_NAME,USER_COLUMN_NAME,COLUMN_SEQUENCE,
LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,
COLUMN_TYPE,WIDTH,NULL_ALLOWED_FLAG,TRANSLATE_FLAG,FLEXFIELD_USAGE_CODE) values
(f.APPLICATION_ID,f.TABLE_ID,f.COLUMN_ID,f.COLUMN_NAME,f.USER_COLUMN_NAME,
f.COLUMN_SEQUENCE,f.LAST_UPDATE_DATE,f.LAST_UPDATED_BY,f.CREATION_DATE,f.CREATED_BY,
f.LAST_UPDATE_LOGIN,f.COLUMN_TYPE,f.WIDTH,f.NULL_ALLOWED_FLAG,f.TRANSLATE_FLAG,f.FLEXFIELD_USAGE_CODE);
end loop;
exception
when others then
dbms_output.put_line(SQLERRM);
end;
/
I am getting an error saying that
/19 PL/SQL: SQL Statement ignored
8/60 PL/SQL: ORA-00942: table or view does not exist
View 18 Replies
View Related
Apr 1, 2011
are there any possibility to attack oracle databases while performing database backup!
View 3 Replies
View Related
Jul 23, 2013
while try to expdp on network drive. getting below error.how can we perform the expdp on network drive
Network location: \\tsclient\p\expdp
1.)SQL> show user
USER is "SYS"
SQL> create or replace directory exp as '\\tsclient\p\expdp';Directory created.
SQL>Grant read,write on directory exp to system;
2.)expdp system/xxxxx@orcl directory=exp dumpfile=EXP_orcl_072013.dmp logfile=EXP_72013_1.log schemas=('IIMS','CMMN')
Export: Release 11.2.0.2.0 - Production on Tue Jul 23 13:48:07 2013
Copyright © 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Release 11.2.0.2.0 - 64bit Production
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
View 2 Replies
View Related
Oct 16, 2012
Iam usin 11.2.0.3 database on linux 64 bit machine
I have got the report from the users that the dataload operation which they perform in the database is taking normal than the expected time
I rebooted the server and started the database i can see that the database startup is even taking more time
The memory allocated is sufficient and when i tried to perform the logswitch the database simply hangs
alertlog contents after restart
---------------------------------------
minact-scn: useg scan erroring out with error e:12751
Tue Oct 16 11:02:03 2012
minact-scn: useg scan erroring out with error e:12751
Suspending MMON action 'Block Cleanout Optim, Undo Segment Scan' for 82800 seconds
Tue Oct 16 11:12:47 2012
Active Session History (ASH) performed an emergency flush. This may mean that ASH is undersized. If emergency flushes are a recurring issue, you may consider increasing ASH size by setting the value of ASHSIZE to a sufficiently large value. Currently, ASH size is 50331648 bytes. Both ASH size and the total number of emergency flushes since instance startup can be monitored by running the following query:
select total_size,awr_flush_emergency_count from v$ash_info;
Tue Oct 16 11:17:53 2012
Suspending MMON action 'undo usage' for 82800 seconds
Tue Oct 16 11:28:05 2012
Suspending MMON action 'metrics monitoring' for 82800 seconds
View 3 Replies
View Related
Jun 29, 2010
I have prepared shell scripts to do the parallel inserts on my DB table (LEGACY_SYSTEM).
There is a trigger (AFTER INSERT ON EACH ROW) associated with the above table. I am calling a package.function inside the trigger to do the required operation and finally it will insert records into my target table (PRICE_CHANGE).
Expectation:
------------
If I insert 10 rows into LEGACY_SYSTEM table, it should do few updates and finally insert 10 rows into PRICE_CHANGE table.
Result:
-------
10 rows got inserted into LEGACY_SYSTEM. All the updates are successful but I could see only 4 rows in PRICE_CHANGE table. If I run it for the second or third time, all the results will be perfect.
Instead of these shell script, if I insert one by one rows manually into LEGACY_SYSTEM table, I am getting all the expected results and the results are consistent. If you look at my scripts below, you will understand the problem better..
DRIVING SCRIPT(test_global.sh):
---------------
#!/bin/sh
./test_script.sh &
./test_script2.sh &
exit;
test_script.sh:
---------------
#!/bin/sh
./test.sh 100052051 10974 &
./test.sh 100052051 8649 &
./test.sh 100052060 10974 &
./test.sh 100052060 8649 &
./test.sh 100052078 10974 &
./test.sh 100052078 8649 &
./test.sh 100052086 10974 &
./test.sh 100052086 8649 &
exit;
test_script2.sh:
---------------
#!/bin/sh
./test.sh 100052051 11698 &
./test.sh 100052051 11777 &
./test.sh 100052060 11698 &
./test.sh 100052060 11777 &
./test.sh 100052078 11698 &
./test.sh 100052078 11777 &
./test.sh 100052086 11698 &
exit;
test.sh:
--------
#!/bin/sh
sqlplus HRUAMGR/HRUAMGR@MALDEV_DW1<<EOF
ALTER SESSION ENABLE PARALLEL DML;
insert into legacy_system (product, item, supp, type, g_ind, d_ind, o_num) values (7896544,$1,$2,'TEST',NULL,NULL,NULL);
commit;
exit;
EOF
I am calling test_global.sh through the UNIX session and all the records got inserted into LEGACY_SYSTEM table and few rows are missing from PRICE_CHANGE table.
If I remove the '&' symbol and execute, the results are perfect. But the requirement is not to remove the '&' symbol. I have been facing this problem for the past 1 month.
View 8 Replies
View Related
Feb 25, 2011
I was just wondering how Oracle manages multiple sessions in a database performing DML. I believe this is related to 'Read Consistency' and I tried to search for the same but could not get any satisfactory online documents.
CASE 1:
user A logs in to a database1
issues select on table A and then inserts 4 rows
user B logs in to databse1
issues select on table A and then inserts 5 rows
issues rollback
user C logs in to a database1
issues select on table A and then inserts 6 rows
issues commit
How many rows can user C see in the table A when he issues select?
CASE 2:
user A logs in to a database1
issues select on table A and then inserts 4 rows
user B logs in to databse1
issues select on table A and then inserts 5 rows
user C logs in to a database1
user B issues rollback
user C issues select on table A and then inserts 6 rows
issues commit
How many rows can user C see in the table A when he issues select?
NOTE: All the users are currently logged in to the same database and none has logged out.
View 6 Replies
View Related
Mar 10, 2013
In my form i have a multi record block which is based on a table in which i am performing insertion, deletion, updation.
My requirement is as below,
While saving a records i want to check distinct value of specific item can not be greater than some value say 2. Then only my records should be saved. I am planning to populate those values in a collection table type and take the distinct values from it. How to populate record values in a collection table type.
View 2 Replies
View Related
Sep 4, 2008
We are facing serious problem while refreshing materialized views using fast refresh option in ORACLE..For the very first time we are performing Complete refresh of data from DB1 to DB2 for few tables.Ongoing we are performing Fast Refresh.Sometimes the fast refresh works fine without any error and sometimes it fails with the below error.
ERROR at line 1:
ORA-32320: REFRESH FAST of "CIR"."C_BO_COMM" unsupported after
cointainer table
PMOPs
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 803
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 860
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 841
ORA-06512: at line 1
let me know when will this error occur during Fast Refresh.
View 4 Replies
View Related
Oct 29, 2011
Tape streaming is not happening while performing RMAN tape backup. On investigation, you find that it is not because of the incremental backup or the empty file backup and that RMAN is sending data blocks to the tape drive fast enough.What could be a solution to make tape streaming happen during the backup?
A.Configure backup optimization
B.Configure the channel to increase MAXOPENFILES
C.Configure the channel to increase the capacity with the RATE parameter
D.Configure the channel to adjust the tape buffer size with the BLKSIZE option
View 7 Replies
View Related
Jan 2, 2013
I have 3 user defined collection types. I am trying to access the type3's attribute in type1 body (like backward accessing).
Ex. My Collection Types Structure (something like master detail structure)
create type type1 as object
(
attr1 varchar2(10),
attr2 varchar2(10),
member procedure function1
[code]...
so, in the type1 body i have to get or assign the value either to type2's attribute or type3's attribute. I have search all the in internet but i haven't find anything such. how to find the reverse method of accessing the super type's attribute(s) in sub type's body.
View 3 Replies
View Related
Oct 17, 2011
Is there some functions to convert the long type field data to varchar2 type?
View 2 Replies
View Related
Mar 8, 2013
I have created the below types and oracle objects.
create or replace type T_EMA_NP_SETDEL_RESP_REC as object
(
respCode number,
respDesc varchar2(255)
)
create or replace type T_EMA_NP_RANGE_LNPTICKET_TAB AS TABLE OF T_EMA_NP_RANGE_LNPTICKET_REC
create or replace type T_RANGE_TICKET_TAB AS TABLE OF T_RANGE_TICKET_REC
The following types are created in the Package specification
type t_resp_rec IS RECORD
(
resp_code number,
resp_desc varchar2(255)
);
--
subtype t_ema_lnpticket is T186_IN_REQ_PARAMETER.T186_EMA_LNPTICKET%TYPE; -- Number
type t_ema_lnpticket_tab is table of t_ema_lnpticket index by binary_integer;
I have the following two procedures
PROCEDURE getEMAReturnResponse(
p_in_call_request_id IN number,
p_ema_resp_rec IN t_ema_np_setdel_resp_rec,
p_ema_range_lnpticket_tab IN t_ema_np_range_lnpticket_tab,
p_endof_event IN varchar)
PROCEDURE Return_Response(p_in_call_request_id IN number,
p_ema_resp_rec IN t_ema_resp_rec,
p_ema_lnpticket_tab IN t_ema_lnpticket_tab,
p_endof_event IN varchar2)
getEMAReturnResponse Procedure:
Accessed by Java application to pass the values. Should call the Return_Response procedure and pass the values received from Java.
Return_Response Procedure
The p_ema_lnpticket_tab is a sort of array that can have multiple values. Please see the example of values. Has all the business rules and validation that should be adhered.
Example of Vaules
p_in_call_request_id = 1
p_ema_resp_rec = 12345, 'Operation Failed'
p_ema_lnpticket_tab = (1,2,4,5)
p_endof_event = Y
View 2 Replies
View Related
Feb 1, 2012
i am trying to run a script in which a command tries to create or replace a type.
i get this error:
ORA-02303: cannot drop or replace a type with type or table dependents
SQL>
SQL> --create a test user:
SQL>
SQL> create user tuser identified by tuser
[Code]....
Table created.
SQL>
SQL> --then change the type:
SQL>
SQL> create or replace type t1 as object (obj_type number(15))
2 /
create or replace type t1 as object (obj_type number(15))
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or table dependents
SQL>
SQL> --if i'll do FORCE action on the type - it'll corrupt my depandant table:
SQL>
SQL> drop type t1 FORCE
2 /
Type dropped.
SQL>
SQL>
SQL>
SQL>
SQL> desc dpntnt_table
Name Null? Type
----------------------------------------- -------- ----------------------------
ID1 NUMBER(7)
SQL>
SQL>
SQL>
SQL> --if i re-create it - my table is still corrupted:
SQL>
SQL>
SQL> create or replace type t1 as object (obj_type number(15))
2 /
Type created.
SQL>
SQL>
SQL>
SQL> desc dpntnt_table
Name Null? Type
----------------------------------------- -------- ----------------------------
ID1 NUMBER(7)
SQL>
--if i re-create it - my table is still corrupted:
create or replace type t1 as object (obj_type number(15))
/
desc dpntnt_table
[/code]
1. If i'll do drop type FORCE what will happen to the dependent object(might be a table for example) ?
2. I understand that this type is already assigned to some object, but i can't seem to find out which one.
how do i find out which object is depending on the type i want to create or replace?
View 4 Replies
View Related
Mar 8, 2013
I have created the below types and oracle objects.
create or replace type T_SETDEL_RESP_REC as object
(
respCode number,
respDesc varchar2(255)
)
--
create or replace type T_EMA_NP_RANGE_LNPTICKET_REC as object
(
ticket number
)
create or replace type T_RANGE_TICKET_TAB AS TABLE OF T_RANGE_TICKET_REC
The following type is created in the Package specification
type t_resp_rec IS RECORD
(
resp_code number,
resp_desc varchar2(255)
);
I have the following two procedures
Procedure getResponse(p_call_request_id IN number, p_resp_rec IN t_setdel_resp_rec,
p_range_ticket_tab IN t_range_icket_tab, p_endof_event IN varchar)
PROCEDURE ProcessResponse(p_call_request_id IN number, p_resp_rec IN t_resp_rec,
p_ticket_tab IN t_ticket_tab, p_endof_event IN varchar2)
The get Response procedure is a wrapper procedure exposed to Java to pass values. The Process Response procedure is a main procedure where all logics and business rules are handled.
The Problem is:
How can I pass the values from get Response procedure to Process Response procedure. So that rules and validations are applied. Please note the p_ticket_tab may have many ticket numbers corresponding to p_call_request_id.
Values E.g. :
p_call_request_id = 1
p_resp_rec (1234, 'Error found')
p_range_ticket_tab (1,2,3,4,5)
p_endof_event = 'Y'
View 7 Replies
View Related
Mar 26, 2013
I've the following function returning OBJECT type. how to call this function
CREATE OR REPLACE TYPE GET_EMP_OBJ is object
( emp_name varchar2(50) ,
mgr_id number,
dept_id number
);
[Code]...
The above function got created successfully. And i'm confused how to call this functions. I tried like below but didn't work
DECLARE
t_emp_info_1 GET_EMP_OBJ ;
BEGIN
t_emp_info_1 := get_emp(7566) ;
for i in 1..t_emp_info_1.COUNT
LOOP
DBMS_OUTPUT.put_line ('Values are'||i.emp_name ) ;
END LOOP;
END;
View 7 Replies
View Related
May 12, 2011
When i tried to drop a type using below command , i received errors
DROP TYPE JAM_ACAS_MSG_TYPE
ERROR at line 1: ORA-02303: cannot drop or replace a type with type or table dependents
View 32 Replies
View Related
Mar 7, 2013
I have been creating lot many threads around the same problem, however i thought i knew but realized I do not know or else do not know how to. I have created object type with an attribute READINGVALUE NUMBER(21,6)...How can i use type attribute on this object while declaring variable.....can we use type attribute on NESTED TABLES, similar to the db tables?
example
CREATE TYPE READING AS OBJECT(READINGVALUE NUMBER(21,6));
CREATE TABLE INTERVALREADINGS OF TYPE READING;
View 5 Replies
View Related
Jul 12, 2010
how to convert a record type to a table type?
View 4 Replies
View Related
Aug 17, 2013
I want to return the output of this query using one OUT parameter from the procedure using RECORD type or OBJECT type.
SELECT empno,ename,sal FROM emp WHERE deptno=30;
Let us assume the query is returning 50 records.
I want to send those 50 records to OUT parameter using record type or object type.
View 20 Replies
View Related
Mar 5, 2013
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production
I'm new to this XML DB thing and also this is my first post.
I'm trying to populate collection type from XMLType. I was able to populate a table from XMLType but any way to populate the collection type. Here is the description of my problem:
Object Type:
CREATE OR REPLACE TYPE DOC_ROWTYPE AS OBJECT
(
REFERENCENUMBER VARCHAR2(255),
REQID NUMBER(12),
[Code]....
Collection Type:
CREATE OR REPLACE TYPE DOC_TABLETYPE IS TABLE OF DOC_ROWTYPE;
I have a physical table which is created when I registered a schema.
A table (Temp_Result) got created with column SYS_NC_ROWINFO$ which is of XMLType.
As you can see this is only a temporary table which will store the response XML which I want to finally get it to collection type.
XML to parse:
<code>
<TFSResponse>
<TFS>
<refNumber>12345</refNumber>
<reqId>123</reqId>
[Code]...
So each object in the collection is one TFS tag. how to implement this?
View 9 Replies
View Related
Dec 25, 2007
i want to get table name, constraint name, constraint type with join processes in string type. this is what i want: alter table tablename add constraint constraintname constrainttype(columnname)
View 1 Replies
View Related
Mar 29, 2013
I have an Type-object typeObj1 that consists another Type-object typeObj2. this def has another Type-object typeObj3. how to access variable declared inside typeObj3. I have syntax below for each Type.
CREATE OR REPLACE TYPE typeObj1
AS OBJECT
(
SYSTEM_IDENTIFER VARCHAR2(50),
PROCESS_TYPE VARCHAR2(50),
abc typeObj2
)
/
[Code]...
/I have tried to access the type-object in where clause in following way
FROM TABLE(CAST(I_typeObj1 AS typeObj1)) ITTPRC,
......
Where
.......
AND (ADDKEY.ADDTN_INFO_KEY_TYP_NM IN (SELECT ADDTN_INFO_KEY_TYP_NM FROM TABLE(ITTPRC.abc)))
AND (ADTINF.ADDTN_RQST_TYP_VAL_DT IN (SELECT ADDTN_RQST_VAL_DT FROM TABLE( ITTPRC.def)) OR ITTPRC.def IS NULL )
AND (ADTINF.ADDTN_RQST_TYP_VAL_NUM IN (SELECT ADDTN_RQST_VAL_NUM FROM TABLE( ITTPRC.def)) OR ITTPRC.def IS NULL )
AND (ADTINF.ADDTN_RQST_TYP_VALUE IN (SELECT ADDTN_RQST_VALUE FROM TABLE( ITTPRC.def)) OR ITTPRC.def IS NULL )
In this way i am able to access the variable inside typeObj3. But problem is i am getting error "ORA-01427 single-row subquery returns more than one row" when i pass more that one typeObj2.
I passed the values like this in proc execution.
T_T_A_I_V := typeObj3('asdasd',NULL,NULL),
typeObj3('String654',NULL,NULL),
typeObj3('abcdef',NULL,NULL));
T_T_A_I_I := typeObj2('CampusCode',T_T_A_I_V),
typeObj2('PlanNumber',T_T_A_I_V);
What i have done is removed typeObj3 from typeObj2, variables defined in typeObj3 are added in typeObj2 then i got ride of above error. is it correct
View 4 Replies
View Related
Oct 23, 2012
i what to update a column data type am geting this error
ORA-02267: column type incompatible with referenced column type
my query is
when i run
SELECT b.table_name||
' joins to the '||
a.table_name||
' table on '||
c.column_name||
' = '||
d.column_name Table_relationships
[code]....
View 9 Replies
View Related
Sep 17, 2011
i HAVE TWO TABLES
EMP_NAME_DETAILS
EMP_ID EMPNAME
EMP_ADD_DETAILS
EMP_ID EMPCITY
NOW I WANT TO MERGE/COMBINE THIS TWO TABLES INTO ONE FLAT TABLE SO MY REPORTS WILL RUN ONLY FROM ONE TABLE INSTEAD OF TWO.
MY DESIRED OUTPUT TABLE WOULD BE
EMP_ID EMPNAME EMPCITY
View 2 Replies
View Related
Nov 23, 2012
the query ..
Below are the codes
create table testing1
(
id number,
data varchar2(20)
)
insert all into testing1 values (1,'100')
into testing1 values (2,'Ram')
[Code]....
I want the output as
100 Ram Pune
200 Shyam Mumbai
300 Kamal bangalore
View 8 Replies
View Related
Jun 12, 2013
I am using the dblink to merge the data. I am using the following merge statement.
merge into APP_USER.USR_NEW_RIGHTS@NEW_RIGHTS t
Using (select 'test' GRANTEE,'TESTxxx'ROLE from dual ) s
on (t.GRANTEE = s.GRANTEE and t.ROLE = s.ROLE)
when not matched then
insert (ID,GRANTEE,ROLE,XRIGHT,COMPANY,OWNER,TABLENAME)
values ('','test','TESTxxx',null, null, null, null);
I know that I have to set a commit and it's working when I insert information's with a normal insert statement via database link, but it seems that merging doesn't work.
View 1 Replies
View Related
Apr 29, 2010
What are all the DML operation can be done in DUAL table.?
View 5 Replies
View Related
Jun 12, 2013
I have a source view where I have some invalid records and those should be found based on codes present in another table.
For eg. from source the records come like
****************SIINNSFDFD****FDFDF2******8
**********TABLE****************FDFSFSSFASFAS********
and if my reference table has values
SIINNSFDFD
TABLE
then these values are present as substring in the particular column in the source view. So I need to flag those records. For every record, I need to check whether all the values present in the reference table matches or not. If it matches then it should be flagged.
I can use in operator as we are not checking for the exact match and we are checking whether that value is present anywhere in that column record.
Looping results in performance issue. We can use PL/SQL for this. As the source view is put into a ETL internal file.
View 2 Replies
View Related
