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.
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?
I'm using the odp.net type factory (class IOracleCustomType and the specific annotations...) to map udts to c# classes.By now I had no problems with it, because I've never used "ref" attributes within the udts.Now I need the use of "ref" attributes within udts but it seems that the odp factory cannot handle those references.
For example:_*
A Simple Type: create or replace type bookcontent_t force as object ( id number(20) , name varchar2(1000) );*And a collection with references to objects of this type:*
create or replace type fms_bookcontent_ref_cln force as table of *ref* bookcontent_t;The c# class is generated by odp.net.
When I retrieve data by using odp.net I get a collection with the correct number of elements but the values are null.What can I do to solve this problem with "ref" attributes/lists? How can I get the referenced object instances?
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
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.
CREATE OR REPLACE TYPE TEST_OBJ_TYPE IS OBJECT ( TEST_ID NUMBER(9), TEST_DESC VARCHAR(30) ) / CREATE OR REPLACE TYPE TEST_TABTYPE AS TABLE OF TEST_OBJ_TYPE / [code]....
I need to include the above function in a plsql package. How I can declare a object type and table type in a pks file? the syntax to include the above code in a .pks and .pkb file?
I got this code snippet online when I was looking for function that returns a table type. what exactly that Exception block does? delete the table when there is an exception, otherwise return the table type?
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.
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?
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;
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;
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)
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 have uploaded some tables from MS Access to Oracle and in the process, some Number fields have been converted to Text. This means that any Queries where there are calculations with those fields (which have been converted to text) don't work properly anymore.
I would like to change the field type from text to number (with cmd and sqlplus) as that's the only way I can access the oracle backend.
I am not sure what commands to use to change this.
Also, if I change the data type from text to number, is it likely I will lose some data?
I have a data synch procedure where I am extracting data from an Oracle table and inserting it into a SQL Server table. The Oracle column is VARCHAR2 and the receiving SQL Server column is Binary. I am receiving a conversion error. I have tried HEXTORAW in the Oracle procedure but it does not work. What data type can I use.
I m trying to give table name as parameter to this function. It is compiling properly.But when an anonymous block is created to call this table value, it has to be fetched into a variable of Rowtype of this inputted table. So I am not able to create any ROWTYPE variable for this table dynamically.
//Function create or replace function instant_tabula(tabula in varchar) return sys_refcursor as cur sys_refcursor; str varchar2(20000); begin str := 'select * from ' || tabula; open cur for str; return cur; end; [code]....
How to call a function with a row type return in an Oracle select statement.
For e.g. :
If I had this function with a rowtype return: ------------------------------ create function abc return xyz%rowtype is rec xyz%rowtype; begin select * into rec from xyz where col1 = n; return rec; end; -------------------------------- How could I use this in a select clause, as there is a multi column return by the function ?
I'm trying to deduct x hours from a timestamp value. Everything works fine if x yields a result that is on the same day as the value I'm deducting it from. However, if it yields the day before then it gives the wrong answer using DML (although in SQL it works fine). Let me explain with an example:
Let's say current_timestamp is 20-MAR-12 09.40.00.000000 +00:00
If I deduct 8 hours from this I get the correct answer: 20-mar-12 01:40:00 (syntax: show current_timestamp-8/24)
If however, I deduct 10 hours from it (which takes it to the day before) I get garbage: 20-mar-12 00:20:00 (syntax: show current_timestamp-10/24). It should say: 19-mar-12 23:40:00
In SQL I get the correct result every time. So, my question is, is this a bug or intended behaviour? If the latter then what is it doing and how can I get it to give the answer I'm looking for?
What I'm trying to do is convert a timestamp from one timezone to another using DML. The NEW_TIME function will only give me what I want if I know whether the "to" timezone is currently in daylight saving or not. eg. Must I use CST or CDT?
The full syntax I'm using to try and achieve my aim is below.
show to_char( to_date( '2012-03-19--15-37-23' 'YYYY-MM-DD--HH24-MI-SS' )-(convert( extchars( tz_offset( 'AMERICA/CHICAGO' ) 3, 1 ) int )/24) 'YYYY-MM-DD--HH24-MI-SS' )
where 2012-03-19--15-37-23 is a user-supplied argument.
In the OWB mapping,I needed to transfer data in lob table1 to lob table2 .Both tables are in 2 different schema.The table1 has blob and clob columns.The table 2 has same structure like table 1 and the table 2 is empty.
I have created the mapping and connected directly from the source table to the target table.Also , I have created a error table for the target table table. As for the data rule method,I customized the error table to log the non unique rows.
When I start validating the mapping . I got the following error
VLD-3200 Deduplicator cannot contain LOB type columns
How to log the error in error table for transferring lob type table1 to lob type table 2
i have changed date's format and the calendar type in sqlplus through the following command so the date's format and the calender type both changed successfully and work, but in oracle form the they haven't changed.
alter session set nls_calendar='persian';
ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD';
ALTER SYSTEM SET NLS_DATE_FORMAT='YYYY/MM/DD' SCOPE=SPFILE;
Note: i know session would change the format temporally but i want to change it permanently so i tried alter_system but still no result
( 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
