XML DB :: Populate Collection Type From XML Type

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


ADVERTISEMENT

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

SQL & PL/SQL :: Creating PLSQL Collection Table Type With Dynamic Query?

Jul 12, 2013

How can I create PLSQL table type dynamically?

I will have to take the Employee Names and create a table structure. Number of employee names can vary from day to day. So, whenever I execute my procedure with Table type, I will have to build the table columns with the employee names.

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

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

PL/SQL :: How To Call A Function Having OBJECT Type As Return Type

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

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 :: Using Record Type And Object Type?

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

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 :: How To Access Type Object Variable Declared Inside Another Type Object

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

PL/SQL :: ORA-02267 / Column Type Incompatible With Referenced Column Type

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

SQL & PL/SQL :: Type And Rowtype?

Oct 24, 2013

Here is an example

(
pnLeadID p_lead.lead_id%TYPE ,
)
IS
v_lead_rec p_lead%ROWTYPE;

v_lead_rec := dml_p_lead.get_rec(pnLeadID);

-- %TYPE is used to declare a field with the same type as
-- that of a specified table's column:
-- %ROWTYPE is used to declare a record with the same types as
-- found in the specified database table, view or cursor:
so pnleadid it gets the collumn in p_lead table
so for v_lead_rec is saying what ever is in the collumn pnleadid =v_lead_rec

View 3 Replies View Related

SQL & PL/SQL :: Query With %type?

Sep 18, 2011

I have a query in this code?

declare
old_name varchar2(20) not null:='stephen';
new_name old_name%type;
begin
dbms_output.put_line(new_name);
end;

will new_name variable will inherit the datatype only or the default value as well?The output of this code will be stephen or not?

View 7 Replies View Related

SQL & PL/SQL :: Using N In Query Type

Aug 23, 2011

what is the use of using 'n' in a query type as mentioned below:

select * from scott.emp a where n =

View 11 Replies View Related

What Type Of Storage Is Supported By ASM

May 25, 2012

what type of storage is supported by ASM? I know of RAW storage but does it run on something else?

We are running on Power7, AIX 6.1.

View 1 Replies View Related

SQL & PL/SQL :: How To Check Type Of File

Jul 23, 2012

I have an SP which takes filename as input parameter and loads the data into table (using UTL_FILE package), it is working fine with flat file, but if it is anything other than flat file (xls, doc, docx, pdf) then in When Others exception I'm showing as UNKNOWN Error.

Requirement: Is there a way to check the type of file (txt, xls, doc or pdf) passed as input to the stored procedure using UTL_FILE package (Only file name is send as input parameter not with file extension)

View 3 Replies View Related

SQL & PL/SQL :: Fetch Into Object Type?

Oct 2, 2011

what ios wrong in the following code

create or replace type testobj as object(col1 number);
create or replace type tabtest as table of testobj;
create or replace procedure proc(a out tabtest) is
cursor c is

[code]..

View 5 Replies View Related

SQL & PL/SQL :: Getting Data From Record Type

Jul 19, 2010

I have one requirement. We have a Package which consists of 2 Stored Procedures which has a RecordType output Parameter.

FUNCTION GET_NFE_INFO(O_status_code IN OUT NUMBER,
O_error_message IN OUT VARCHAR2,
O_message IN OUT "OBJ_FM_NFE_DOCHDR_REC",
I_fiscal_doc_id IN FM_FISCAL_DOC_HEADER.FISCAL_DOC_ID%TYPE)
return BOOLEAN is

I am working on a one Stored Procedure which would display the output from the above function as,

set serveroutput on size 9000;

DECLARE

L_stg_status FM_FISCAL_DOC_HEADER.STATUS%TYPE := 'NFE_P';
L_fiscal_doc_id FM_FISCAL_DOC_HEADER.FISCAL_DOC_ID%TYPE := 12325;
L_dummy VARCHAR2(1) := NULL;
L_status_code NUMBER(1) := 0;
L_error_message VARCHAR2(255) := NULL;
L_message "OBJ_FM_NFE_DOCHDR_REC" := NULL;

[Code]..

Here for each and every data value i am Printing it using DBMS_OUTPUT, L_message record has more than 100 columns. Is there a easy way of handling it instead of using DBMS_OUTPUT ?

View 20 Replies View Related

SQL & PL/SQL :: String Return Type

Feb 17, 2012

I have two Oracle instances, both are 10.2.0.3

Executing the following results in two different results:

select N'Test" "result" from dual;

On one the output is:

resu
----
Test

and on the other the output is

result
----------------
Test

This seems to suggest that one is returning "Test" as a char(16) and the other as a varchar2 or nvarchar2. In both cases, NLS_CHARACTERSET is WE8ISO8859P1 and NLS_NCHAR_CHARACTERSET is AL16UTF16.

Not being an Oracle DBA, I am not sure where to look.

View 6 Replies View Related

SQL & PL/SQL :: What Should Be The Data Type For The Column

May 9, 2012

I have to create a table which should store data at Week level. The table have the following columns

Product id, Loc id, Business group id, FISCAL WEEK , Revenue,

Fiscal week column will have data as '2011-W01', '2011-W47' etc.

What should be the data type for fiscal week column. Based on this table i have to create a calculated column which should fetch trailing 12 weeks average for each row.

View 5 Replies View Related

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

PL/SQL :: Displaying Value Of Object Type

Aug 30, 2012

I have created one type as Object and I am trying to display the values available in object type for debugging purpose. To display the contents of object type.

View 4 Replies View Related

XML DB :: How To Retrieve Value Of Enumeration Type

May 21, 2013

I have registered an XSD schema (with gentypes = true) which contains an enumeration type like this:
...
<xsd:simpleType name="OperatoreLogico">
          <xsd:restriction base="xsd:string">
               <xsd:enumeration value="OR"/>
               <xsd:enumeration value="AND"/>
          </xsd:restriction>
     </xsd:simpleType>

[Code]....

was successfully generated the object type:

TYPE "Richiesta_getListaPersoneFisiche"
AS OBJECT ("SYS_XDBPD$" "XDB"."XDB$RAW_LIST_T",
*"operatoreLogico" "XDB"."XDB$ENUM_T"*,
"nome" VARCHAR2(4000 CHAR),
"cognome" VARCHAR2(4000 CHAR),
"codicefiscale" VARCHAR2(4000 CHAR),
"datanascita" DATE) NOT FINAL INSTANTIABLE

Fill correctly the object from an xml valid for the schema using the function "toObject".

I can retrieve the values of all attributes of the object (objectName.attribute), but I do not know how to retrieve the value of the enumeration type (XDB$ENUM_T) "operatoreLogico".

View 8 Replies View Related

SQL & PL/SQL :: Varray Of Object Type

Jun 23, 2010

I want to pass varray of object as out parameter (more than 1 ), Tested the below code getting error.

Note : I want to get value from table and assign it to varray object and pass varray as out parameter.

Here below the code I tested.

create type emp_type as object (
emp_no number,
emp_name
);
create type emp is varray(10) of emp_type;
[code]........

Getting error while above code.

View 11 Replies View Related

How To Find OS Storage Type

Sep 9, 2013

Database Version is 11.2.0.3 & OS version is RHEL5.  How can I find the OS storage type? Which command should I execute to find this?

View 3 Replies View Related

PL/SQL :: Matrix Type Report

Aug 15, 2012

Writing a query based on this data:

Each row is based on a row of a student.

Grade stands for the school grade, K=> Kindergarten

Tabl is a table the student is assigned to.

Bev stands for beverage, 1c=> White Milk , 2c=> Chocolate Milk, 3c => Orange Juice.

GRADE      TABL      BEV
K      1      1c
K      2      3c
K      3      1c
K      4      3c
K      1      1c
K      2      2c
K      3      3c

I need to generate a summary report that looks like this:

Grade/Tabl           White              Chocolate              Orange
K-1                   2                   
K-2                                           1                    1
K-3                   1                                            1
K4                                                                 1

Is there a way in which I can write a query to display this type of matrix report.

View 4 Replies View Related







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