SQL & PL/SQL :: Using Table Type With Merge?

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


ADVERTISEMENT

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 View Related

SQL & PL/SQL :: ORA-02303 - Cannot Drop Or Replace A Type With Type Or Table Dependents

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

PL/SQL :: Passing Values From Table Type To Oracle Object Type

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

SQL & PL/SQL :: Cannot Drop Or Replace A Type With Type Or Table Dependents

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

PL/SQL :: Type Attribute With Object Type Or Nested Table?

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

SQL & PL/SQL :: Converting Record Type In Table Type

Jul 12, 2010

how to convert a record type to a table type?

View 4 Replies View Related

SQL & PL/SQL :: Merge Two Table Into One

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

SQL & PL/SQL :: Merge Into Table Name Using Cursor

Jun 28, 2010

I am trying to merge into test table using a cursor. If the contract number in the test table matches with the contract number from the cursor then i need to update the creation_date else i need to insert values from the cursor into the test table.

MERGE INTO test
USING cursortest
ON (i.contract_number = test.contract_number)
WHEN MATCHED THEN
[code]......

View 9 Replies View Related

SQL & PL/SQL :: Merge Two Table With Distinct Value

Sep 4, 2012

select * from a (6 column)
where wallet='01710000273'
select * from b (2 column)
where wallet='01710000273'

when I union these two tables it find two row for similar wallet, but i want to show these in one row. How will possible this?

View 1 Replies View Related

PL/SQL :: Merge Statement On Single Table

Oct 29, 2012

{code{

i want to apply merge stmt on single table.

CREATE TABLE TEST11(TNO NUMBER(5), TVAL VARCHAR2(100), TID VARCHAR2(10));
INSERT INTO TEST11 VALUES(1,'VIJAYA','TEST');
INSERT INTO TEST11 VALUES(2,'VIJAYA','TEST');
INSERT INTO TEST11 VALUES(3,'VIJAYA','TEST');
INSERT INTO TEST11 VALUES(4,'VIJAYA','');
INSERT INTO TEST11 VALUES(5,'VIJAYA','');

[Code]....

My requiremen is if record is exists then i wan to update some value, if record not existes the i wan to insert new record

View 3 Replies View Related

SQL & PL/SQL :: Can Have All Rows In A Table Updated At Once In Merge Statement

Aug 10, 2010

Can I have all the rows in a table updated at once in the merge statement?

MERGE INTO providermaster a
using
(
SELECT * FROM PROVIDERMASTER@INGEST) b
ON (b.MASTERPROVIDERID=a.MASTERPROVIDERID)
WHEN MATCHED THEN

UPDATE ....... I want to update all the other rows at once..

View 24 Replies View Related

PL/SQL :: Procedure To Merge / SELECT From Table Of Record

May 28, 2013

I want to write a simple stored procedure and I want to keep it as simple as possible (no loop, the least amount of parameters ...etc.)

Basically, the procedure receives a Table Of Record as input parameters and needs to merge it with existing table, the table of record is of the rowtype of the existing table.

I have difficulties to merge these data. Below is what I tried

CREATE or REPLACE PACKAGE BIZ_xxx_MERGE
IS
TYPE xxx_ACTIVITE_Type IS TABLE OF MyTbl%RowType INDEX BY BINARY_INTEGER;
PROCEDURE MERGE_xxx_ACTIVITE_SP (
MyLP IN xxx_ACTIVITE_Type
);
END BIZ_xxx_MERGE;

CREATE OR REPLACE PACKAGE BODY BIZ_xxx_MERGE AS
PROCEDURE MERGE_xxx_ACTIVITE_SP (
MyLP IN xxx_ACTIVITE_Type
) AS
BEGIN
MERGE INTO MyTbl
[code].........

View 10 Replies View Related

Get Table Name / Constraint Name / Constraint Type With Join Processes In String Type

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

PL/SQL :: Update Vs Merge - Updating One Table Based On Values In Another

Jun 26, 2013

I have two tables lets say TAB_A and TAB_B. I altered table B to include a new column from table A I wrote a merge statement as follows to merge the data 

MERGE INTO TAB_AUSING TAB_BON (TAB_A.SECURITYPERSONKEY=TAB_B.SECURITYPERSONKEY)WHEN MATCHED THEN  UPDATE SET TAB_A.PPYACCOUNT=TAB_B.PPYACCOUNT; 

I know INSERT is for inserting new records UPDATE to my knowledge is to modify currently existing records (loosely) MERGE is one I rarely used, until this particular scenario. The code works perfectly fine, but I was wondering how could I write an update statement? Or in this scenario should I even be using an update statement?

View 4 Replies View Related

SQL & PL/SQL :: Temporary Table And Bulk Collect Into Type Table

Nov 15, 2011

I am using temporary table.

PROCEDURE Return_Summary(WX IN dbms_sql.varchar2_table,
WX OUT SYS_REFCURSOR) IS

Begin
FOR i IN 1 .. Pi_ WX.count LOOP

/* I need to put this results in a temp table or table object Can I use temp table for this or do we have any other recommended method. The loop might execute max of 10 times and for each run it might return 100-200 records. */

select WX_NM,
WX_NUM
from TAB A, TAB B, TAB C
where A.KEY1 = B.KEY1
and B.KEY1 = C.KEY1
and C.WX = WX(i);
End Loop;
End;

View 5 Replies View Related

Performance Tuning :: Merge Statement Tuning For 100M Records In Table?

Oct 31, 2011

I have two tables with 113M records in DWH_BILL_DET & 103M in prd_rerate_chg_que and Im running following merge query, which is running for 13 hrs to update records, which is quiet longer time.

SQL> explain plan for MERGE /*+ parallel (rq, 16) */
INTO DWH_BILL_DET rq
USING (SELECT rated_que_rowid,
detail_rerate_flag_code,
rerate_sel_key,

[code].....

View 39 Replies View Related

SQL & PL/SQL :: Table Type Cast

May 6, 2010

FOR n
IN (SELECT invtry_alloc_id,
invtry_item_id,
oh1_alloc,
oo1_alloc,

[Code].....

is giving me error "invalid datatype" ora-00902 what could be the reason

create or replace type invtry_alloc_obj is object
(invtry_alloc_id number,
invtry_item_id number,
oh1_alloc number,

[Code]....

View 2 Replies View Related

SQL & PL/SQL :: How To Assign NULL To Table Type

Jan 5, 2011

Let me know in PL/SQL how can we set a Table Type record to NULL?

Also, if .delete makes it NULL?

View 1 Replies View Related

SQL & PL/SQL :: Table Type Of RECORD In A Query?

Apr 2, 2012

I am in the need of using a table type object in SQL query.

I have a package which has a spec in which I have declared :

TYPE TESTREC IS RECORD
(
RE_ID NUMBER(9),
RATING_TARIFF_NAME VARCHAR2(40),
);
TYPE TESTTABTYPE IS TABLE OF TESTREC;
TTR TESTTABTYPE;

In one of the package procedures I am collecting data into the above indicated table type object (TTR):

SELECT
RE_ID,
RATING_TARIFF_NAME
BULK COLLECT INTO TTR
FROM TESTPACKTAB;

This works fine. The values get collected into TTR and am able to print them too.

But when I :

SELECT
AA.RATING_TARIFF_NAME
INTO v_name
FROM
TABLE( TTR ) AA ;

in the same procedure immediately after the collection I get the error while running the procedure :

ORA-21700 : Object does not exist or is marked for delete.

View 5 Replies View Related

SQL & PL/SQL :: Index Of Table Type Object

Apr 4, 2012

I have a table type object loaded (tto) with data and am using it as :

SELECT tto.B, tto.C
FROM TABLE(tto)
WHERE tto.A = <some value>;

This is working fine. BUT, can I also select the index of each element too along with other fields of each element ?

View 16 Replies View Related

SQL & PL/SQL :: Strange Objects Of Type Table

Oct 9, 2010

When as scott, I run the following command select * from tab, I get the output that has been attached as pdf. This output contain some strange objects like-

BIN$Pjh/Uul1QJyWtCiF1oTUHQ==$0
BIN$fr3Ks/3LSdSHOFHhjdXCFQ==$0
BIN$ral0nTtJTG2f7dRgYXmCtQ==$0

and as per the attachment these are all of type table. When I try to delete them-

delete table BIN$ral0nTtJTG2f7dRgYXmCtQ==$0

I get the following error-

Error starting at line 1 in command:
delete table BIN$ral0nTtJTG2f7dRgYXmCtQ==$0
Error at Command Line:1 Column:7
Error report:
SQL Error: ORA-00903: invalid table name
00903. 00000 - "invalid table name"
*Cause:
*Action:

whether something is happening to the database?

View 2 Replies View Related

SQL & PL/SQL :: TYPE NUM_ARRAY As Table Of Number?

Dec 5, 2012

exactly what permissions I need to execute the following SQL statement.

SQL> CREATE OR REPLACE TYPE NUM_ARRAY as table of number;
2 /
CREATE OR REPLACE TYPE NUM_ARRAY as table of number;
*
ERROR at line 1:
ORA-01031: insufficient privileges

When I grant DBA to the ID which runs the SQL it works fine.

View 2 Replies View Related

PL/SQL :: Declare Variable As Row Type For A Table?

Jul 19, 2012

Can I declear a variable in PLSQL as the row type for a table, who's name is unknown during compile time, but will be determined when the PLSQL is runnning. The code is like following:

Procedure operTable( tableName IN VARCHAR2)
IS
TYPE ty_Row IS tableName%ROWTYPE
v_Row ty_Row;
CURSOR v_quey_cur

[code]...

View 5 Replies View Related

SQL & PL/SQL :: Backward Accessing Super Type Attributes From Sub-type Body In Oracle Collection?

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

Server Administration :: Functions To Convert The Long Type Field Data To Varchar2 Type

Oct 17, 2011

Is there some functions to convert the long type field data to varchar2 type?

View 2 Replies View Related

SQL & PL/SQL :: Passing Values From Oracle Object Type To PLSQL Type

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

Integrate Same As User Want Output As Table Type

Dec 27, 2012

I've created a type as

CODETYPE TEST_DWH_PROD AS OBJECT
            (WO_RELEASE_DATE DATE,
            SITE_CODE__MFG CHAR(5),
            SITE_CODE__PARENT CHAR(5),
            ORDER_TYPE CHAR(3),
            MFG_TYPE VARCHAR2(10),
[code]....            

the above query is giving the value of current QTD(on the basis of V_REPORT_DATE)..I need to integrate the same as user want the output as table type and he can also give some filter conditions.

View 1 Replies View Related

How To Use User Defined Type While Creating New Table

Oct 13, 2009

That is I have created the User Defined Data Type as following. CREATE OR REPLACE TYPE Bit_Type AS OBJECT(Bit NUMBER(1,0));

After completing this creation of new UDT, I am trying to create the table with this UDT as follows, CREATE OR REPLACE TABLE Sample_Bit ( RegID Bit_Type);

I received an Error Message like:
SQL Error: ORA-22913: must specify table name for nested table column or attribute
22913. 00000 - "must specify table name for nested table column or attribute"
*Cause: The storage clause is not specified for a nested table column or attribute.
*Action: Specify the nested table storage clause for the nested table column or attribute.

View 1 Replies View Related

SQL & PL/SQL :: Table Column Datatype With %type Attribute

Jun 23, 2011

Is it possible to replace datatype of column of one table with another table's column datatype using %type like below

SQL>create table test1 (v1 varchar2(10));

SQL>create table test2 (v1 test1.v1%type);

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved