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


ADVERTISEMENT

PL/SQL :: Retrieve XML Data In Clob Type?

Dec 5, 2012

I have a table created as below

create table tab123( x clob);

Inserted a row as below

insert into t values ('"<ProcessBatchRequest xmlns:inp1="http://services.abc.com/" ServiceVersionNumber="" xmlns="http://services.abc.com/GlobalBatchServiceV1.0">
<inp1:BatchDetail>
<inp1:ApplicationID>test.123</inp1:ApplicationID>
<inp1:ApplicationBatchID>test.123</inp1:ApplicationBatchID>
<inp1:MessageTimeStamp>2012-11-28T11:05:32.000-05:00</inp1:MessageTimeStamp>

[code]....

Now i want to retrieve the value present under a given xpath.

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

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

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

Retrieve Nth Row From Table

Oct 12, 2010

I have googled so much but unable to find the explanation how the following query work.

SELECT * FROM EMP a WHERE 2 = (SELECT COUNT(rowid) FROM EMP b WHERE b.rowid <= a.rowid);

View 2 Replies View Related

SQL & PL/SQL :: Retrieve Column Value

Jan 20, 2012

example

My table mst_product(product_code,product_name) and its value is (190,toy),(198,tim),(280,bush),(jupiter,top),(AUTO2,auto),(281,tig),(282,com)...and so on

I want to make a query to select those items which is equal to and greater than 280. So how we make a query.How select those items which does not have any alphabetic letters.

View 4 Replies View Related

PL/SQL :: How To Retrieve Xml From Blob Col

Dec 10, 2012

I have kept xml in blob and i am retriving it as following.

select utl_raw.cast_to_varchar2(dbms_lob.substr(colblobforxml,2000,1)) from t1

the problem is, it only gets 2000 chars and i have more than 2000 chars.

View 2 Replies View Related

Retrieve Records - Using Code?

May 28, 2008

I need to retrieve the records where the involvement is either a person or an organization. Using the code below i receive an error stating that the outer join operator is not allowed in the operand OR:

(( INVOL1.PERSON_IDENTIFIER(+)=ALL_PERSONS.IDENTIFIER )
OR ( INVOL1.OUNIT_IDENTIFIER_INV=INVOL1_ORG.IDENTIFIER(+) ))

If I change it to an and query it will only retrieve the records where there is a person and an organisation as the involvement but I need this option as well as one or the other.

View 7 Replies View Related

How To Retrieve Table Schema

May 22, 2007

I'd like to know which SQL command (if there is any) I can use to display the current schema of a table. I'm particular interested in knowing which columns are indexes.My privileges to the DB are quite basic (I think that I'nm only allowed to run SELECT statements

This is the Oracle version that I'm using:

Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
PL/SQL Release 9.2.0.6.0 - Production
CORE 9.2.0.6.0 Production
TNS for HPUX: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production

View 2 Replies View Related

SQL & PL/SQL :: To Retrieve Rows Which Have Same Buyer ID

Dec 1, 2010

My table "xxbuyer_det" contains the below data.

buyer_id Invoice_numPriceItem
------------------------------------
148137110A
149314211B
149314315C
149314415F
148120520D
148120625E

My requirement is to select the rows which have same buyer_id more than or equals to two times (>=2). I am trying with the below query. But this query is failing(not retrieving any data) as i have used group by class and count value is 1 for every record.

SELECT BUYER_ID, INVOICE_NUM, PRICE
FROM
xxbuyer_det
GROUP BY
BUYER_ID,
INVOICE_NUM,
PRICE
HAVING COUNT(*) >=2

View 7 Replies View Related

SQL & PL/SQL :: Using Rownum To Retrieve Few Records?

Mar 29, 2010

select rownum, CATR_ID, CAT_ID, CATR_REG_COPY, CATR_REG_LABEL, CATR_ACQUIRED_DATE, CATR_REG_DATE, CATR_MEDIA_COMMENTS, CATR_WITH_DIGITAL,
CATR_ORIGINAL, CATR_LINK, CATR_CREATED_BY, CATR_CREATED_DATE, CATR_MODIFIED_BY, CATR_MODIFIED_DATE, CATR_CHECKOUT, Available,
CATR_RETURN_DATE, LOCN_ID, LOCN_SITE, LOCN_LOCATION, MTYPE_GROUP, MTYPE_NAME, ACCESS_LEVEL, DESCRIPTION, CAT_TITLE, CAT_DESCRIPTION,
CATEGORY_ID, CAT_AUTHOR, CAT_PUBLISHED_DATE, CAT_PUBLISHER, CAT_EVAL_RELEVANT_KEYWORDS, CAT_REG_NUMBER, CAT_REG_SUBNUMBER, U_NAME

[Code]..

There are over 1500 records, but this query does not return any row. If i change rownum >= 100 to rownum <= 100 it returns first hundred records though... What is wrong here?

View 12 Replies View Related

SQL & PL/SQL :: Retrieve One Value For Phone Number?

Feb 24, 2010

I need to retrieve only one telephone number against the parent id. The preference is to get mobile if available if not home number else work phone. How can I achieve this

I have given the sql code and sample output below

select
pp.phone_id
,pp.parent_id
,pp.phone_type
,pp.phone_number
from per_phones pp

[code]....

Actual results should be

PHONE_ID PARENT_IDPHONE_TYPEPHONE_NUMBER
12960 2500H1 9385789
311108 146564M 04879652

View 6 Replies View Related

SQL & PL/SQL :: Query To Retrieve Value In Specified Schema

Jan 6, 2011

I need a query to retrieve a particular string from all the columns and all tables in a specified schema.

For example:i had a value "Current" in a particular column in a particular table. i need to find this value if i don't know the table name and column name.

View 2 Replies View Related

SQL & PL/SQL :: Retrieve Only Last Rank Row From Table?

Dec 2, 2010

how do i retrieve only last rank row from table ?

View 7 Replies View Related

SQL & PL/SQL :: How To Retrieve Distinct Cat_id

Nov 1, 2011

Category Table
Name Null? Type
------------------------- -------- --------------
CAT_ID NOT NULL NUMBER
CAT_NAME VARCHAR2(20)

Qusestion Table
Name Null? Type
--------------------------- -------- -------
QUS_PK NOT NULL NUMBER
CREATED_DATE DATE
VIEW_COUNT NUMBER
CAT_ID NUMBER

select category.cat_id , qus_pk , cat_name , created_date , view_count
from category , qusestion
where category.cat_id = qusestion.cat_id
order by view_count desc

How can I retrieve distinct cat_id here?

View 21 Replies View Related

SQL & PL/SQL :: Retrieve Records Of 2 Way Transaction

Apr 13, 2011

I want Query on this table. EXAMPLE TABLE:

SENDER RECEIVER AMOUNT

A B 1000
B C 2000
A D 3000
C A 3000
B A 4000
A B 5000
A B 1000
B C 2000
C A 3000
B A 4000
A B 5000
A C 10000

Dynamically I am giving user name A. I want to retrieve the data how much money user B taken from user A and how much money user B given to A lly for C and D.

CONDITION: I want to retrieve the records of 2 way transaction that means user A sent money to user B and user B must sent money to user A. I don't want one way transaction records i.e user A sent money to D but user A didn't get money from user D like this records.

OUTPUT LIKE:

USER MONEY_TAKEN_FROM_A MONEY_GIVEN_TO_A
B 12000 8000
C 10000 6000

View 14 Replies View Related

SQL & PL/SQL :: Cursor Defined To Retrieve Row By Row

May 26, 2011

In SQL server the cursor is defined to retrieve row by row and process it. We can fetch records as FIRST, LAST, NEXT, PREVIOUS and SCROLL like that. Can we do the above in oracle. Else what is the use of cursor in Oracle. I know that it is a private area in which the records are processed.

View 6 Replies View Related

SQL & PL/SQL :: Retrieve The Path Of A Directory

Oct 27, 2011

I create a directory using this:

CREATE DIRECTORY xml_data AS 'c:\mytest';

How can I retrieve the 'C:mytest' in a select statement?

View 5 Replies View Related







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