SQL & PL/SQL :: How To Select Data From REF Within REF (object Types)
Mar 3, 2012
how to display REFS from within a REF. I've not used my exact code here as its quite a big file so i've made a similar scenario to get me point across. Here is the code first:
1 CREATE OR REPLACE TYPE object1 AS OBJECT (
2 object_id NUMBER(5),
3 object_name varchar2(10),
4 object_added DATE);
5 /
6 CREATE TABLE object1_tab OF object1 (object_id PRIMARY KEY);
7 /
8 INSERT INTO object1_tab
9 VALUES (1,'Daniel',sysdate);
10 /
11 show errors;
12 /
13 CREATE OR REPLACE TYPE object2 AS OBJECT (
14 object_id NUMBER(5),
15 object_job varchar2(10),
16 object1_ref REF object1 );
17 /
18 CREATE TABLE object2_tab OF object2(object_id PRIMARY KEY);
19 /
20 INSERT INTO object2_tab
21 VALUES (1,'Developer',(SELECT REF(p) FROM object1_tab P
22 WHERE VALUE(p).object_id = &object_id));
23 /
24 select DEREF(object1_ref)
25 FROM object2_tab;
26 /
27 CREATE OR REPLACE TYPE object3 AS OBJECT (
28 object_id NUMBER(5),
29 object_location VARCHAR2(20),
30 object2_ref REF object2);
31 /
32 CREATE TABLE object3_tab OF object3 (object_id PRIMARY KEY);
33 /
34 INSERT INTO object3_tab
35 VALUES (1,'New York',(SELECT REF(p) FROM object2_tab P 36 WHERE VALUE(p).object_id = &object_id));
37 /
38 show errors;
39 /
40 select object_id,object_location,DEREF(object2_ref)
41 FROM object3_tab;
42 /
As yot can see in the code each object refernces from the previous. When I view the DEREF in the third table (object3_tab) i get the following output:
OBJECT_ID OBJECT_LOCATION DEREF(OBJECT2_REF)
--------- -------------------- ----------------------------------
1 New York [SANTA.OBJECT2]
Contained within the object2 is the following:
SANTA.OBJECT2(1,'Developer','oracle.sql.REF@c4cb4aa6')
How would i view the ref for object1 within object3? I will also need to be able to verify the data in these REF in the new table they are used in.
View 3 Replies
ADVERTISEMENT
Aug 19, 2010
I need to implement a "Select All" function in a Data Block with All the Check boxes. It's a Database data block with 10 records to be displayed at a time. Check Box Items are in default layout. The requirement is when user checks/un-checks that select all check box which is placed on the left hand side of the other check boxes, it should select/deselect all the check box database items in that particular record.
View 7 Replies
View Related
Oct 17, 2013
I am using 11.2 DB. I have created 2 object types. The contact_t has been embedded into the student_t object and I have created a view with the nested object.How do you select the values from the contact_t object type in SQL from the view? i want to see the values of the contact_t object in speerate fields (ie. contact_name, city, state...)
CREATE OR REPLACE TYPE contact_t AS OBJECT ( dcid NUMBER(10), contact_name VARCHAR2(50), city VARCHAR2(50), state VARCHAR2(10), zip VARCHAR2(10), email VARCHAR2(100) )
CREATE OR REPLACE
[Code]....
View 2 Replies
View Related
Dec 2, 2010
I need to transfer PL/SQL or NESTED table from LOCAL database to REMOTE.I need to be able to use that transfered table in SELECT statement.I tried:
--ON LOCAL
SELECT SYS_OP_GUID() FROM DUAL;
--966D2DFEAEEB80D6E0430A0166CB80D6
create or replace type varchar_number_oid OID '966D2DFEAEEB80D6E0430A0166CB80D6' as object (
v varchar2(10),
i number);
/
Type created
SELECT SYS_OP_GUID() FROM DUAL;
--966D2DFEAEFB80D6E0430A0166CB80D6
[code]....
I tried using PL/SQL types it worked, but then i have a problem that i can not use local collection in SQL.
View 2 Replies
View Related
Oct 25, 2013
He had created an object type and wanted to populate it in a query. So far so good. But then he wanted to select some attributes from that object in the same query. Basically, he wanted to do something like this:
With his_view
As
( select object_type( attr1, attr2) theobj
From his_table
Where ...
)
Select theobj.attr1
From his_view
But somehow he was hitting ORA-00904: "THEOBJ"."ATTR1": invalid identifier over and over again.Here's a test script:
Create type mhe_type As Object( col1 Number
, col2 Varchar2(30)
)
/
-- Basic select
With mhe_view
[code]...
Specify a correlation name, which is alias for the table, view, materialized view, or subquery for evaluating the query. This alias is required if the select list references any object type attributes or object type methods. link...
View 1 Replies
View Related
Jun 22, 2010
I am migrating sybase to oracle database. A Java developer needs money datatypes.I said to them, please change the cast(<value> as number(19,4) in java code side. but they are not accepted because money data type is used most of the places.
select cast(0 as money) from bank_trans; this sql statements present in java code. I need to create user defined type is equivalent to money datatype.
My steps
I have create user defined data types
create or replace type money as object(money as numbeer(19,4)
select cast(0 as money) from dual;
it shows inconsistent datatypes error.
create or replace type money is table of numbeer(19,4);
select cast(0 as money) from dual;
it shows inconsistent datatypes error.
View 10 Replies
View Related
Aug 15, 2012
what could be effective data type to store large integer values like, 50,000; 10,000,000 etc.?
View 3 Replies
View Related
Apr 3, 2010
I have following structures:
SQL> CREATE TYPE address_typ AS OBJECT (line1 VARCHAR2(20), city VARCHAR2(20), COUNTRY VARCHAR2(20));
2 /
Type created.
SQL> CREATE TYPE phone_typ AS OBJECT (area_cd VARCHAR2(10), phone# VARCHAR2(10));
2 /
Type created.
SQL> CREATE TYPE phone_list_typ AS TABLE OF phone_typ;
[code]...
My questions are:
1. I have created an object table person_obj of parent type. I have not created object tables of salesperson_typ and customer_typ and want to store data related to both in person_obj. What is the difference between storing data in an object table of parent type and object table of subtypes? In which case should I consider storing data of all subtypes in an object table of parent type instead of object tables of individual subtypes?
2. Second question is regarding validating the column values before the creation of object instance. Suppose before creating an instance of salesperson_typ, I want to check if salary is NOT NULL and greater than 0. I guess constructor is the only option to achieve this but how full-proof it is if constructors are used for this purpose? Is there any other alternate way?
View 4 Replies
View Related
May 31, 2013
I have a editing database with an eversions table:
NAME OWNER
---------------------- --------------------------------
WR5936_DN6676 FRED
WR6739_DN7507 FRED
WR12744_DN13994 FRED
WR6739_DN7511 BARNEY
WR6801_DN7513 BARNEY
I have a process database with a pversions table:
SOID
----------------
5936
6739
12744
6739
I need to select from the editing.eversions table all the records that do not have a matching record in process.pversion. The eversion table is text, and has some additional crap surrounding the characters I want to use for creating a join.
View 3 Replies
View Related
Sep 26, 2012
While practicing with Triggers, the following error was encountered. An Object Type and an object Table are created.
create or replace
type typPerson is object
(id number,
firstname varchar2(30),
lastname varchar2(30)
[code].........
I executed the below insert statement, and I got the following error.
SQL> insert into person_obj_tab values (10,'Object1','From Trigger');
insert into person_obj_tab values (10,'Object1','From Trigger')
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 4153
Session ID: 136 Serial number: 305
[code]......
SQL> insert into person_obj_tab values (10,'Object','Original');
1 row created.Question:
1) Trigger of version 1 did not report any error during compilation, but during DML execution, hangs for sometime and gives the above error.
2) Whether direct assignment of Objects of greater size is possible inside triggers built on object Tables?
3) Suppose an object contain multiple attributes (say more than 20), then how to assign them inside a trigger?
View 1 Replies
View Related
Jan 25, 2013
I have a problem with executing oracleCommand.ExecuteReader() method. Whatever I try it always returns null and it won't create OracleData reader object. I'm using ODAC 1120320_x64 for .net 4.0 and timesten112241.win64. Don't sure what to do. Debugger is showing strange thing in OracleConnection object : ConnectionState = Closed, but output of ttStatus shows connection to TimesTen data store and ExecuteNonQuery() command works just fine with or without (in or out) parameters. But when I try to execute some query with multile output such as select *, I can't get any result.
I also have a strange problem with connection.Open() When I execute Open() i throws AccessViolationException that can be handled with [Handle ProcessCorruptedStateExceptions] attribute, but connection is established after that and my application works fine until I try to instance OracleDataReader object.
Here is the code:
OracleCommand select = null;
OracleDataReader reader = null;
select = new OracleCommand(selectStmt, connection);
select.CommandType = CommandType.Text;
try
{
reader = select.ExecuteReader(); // this line throws NullReferenceException
if (reader.HasRows)
{
[code]....
Just to mention, I tried it with different queries (pl/sql, plane sql, stored procedure) and all of them works fine in SQL Developer, but not in app.
View 10 Replies
View Related
Dec 26, 2010
I had my previous table model as below.
CREATE TABLE Rel_Module (
Id NUMBER (6) PRIMARY KEY,
Type CHAR (7) NOT NULL,
[Code]...
How I can easily load data into this new REL_MODULE table either using SQL or PL/SQL (e.g., Procedures, Functions, and Triggers). I don't need to write so many INSERT statement.
View 20 Replies
View Related
Nov 11, 2012
I have existing table which needs to be copy to new table with object using the cursor. The exist table has a rank column which has some duplicate rank, which need to be remove and provide a series of numbers, like 1,2,3,4,5,...
create type UNIVERSITY as object (
U_RANK number(2),
U_SCHOOL varchar2(150),
U_COUNTRY varchar2(150),
U_SCORE number(3)
)
[code].....
DBMS_OUTPUT:
------------
Warnings: --->
W (1): Warning: execution completed with warning
<---
0 record(s) affected
[Executed: 11/11/2012 9:12:19 PM] [Execution: 31ms]
View 4 Replies
View Related
Dec 24, 2012
Is there any query to find the dependent object details for any object. Like if mview is built on a table, then i should be able to find the table name with out checking code of the mview. similar way for view and functions or procedures etc...
View 5 Replies
View Related
Aug 29, 2013
Quote:Oracle collects new statistics when enough of the data (about 10%) has changed.I read the above statement in the oracle documentation that Oracle collects new statistics when enough of the data (about 10%) has changed every day during 10 pm to 6am .
But still i see some object statistics are stale and empty.Do i need to collect statistics for these objects manually ? and would like to know why these objects statistics were not collected during maintenance window ?
View 4 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
Sep 23, 2011
I need the output like...
OUTPUT(MM/DD/YYYY): 2/03/2011
02/03/2011
create table test (trans_date varchar2(1000));
Insert into test values ('2/03/2011')
Insert into test values ('02/03/2011')
Insert into test values ('12/33/2011')
Insert into test values ('xxx')
Insert into test values ('33/33/2011')
Insert into test values ('03/03/11')
View 4 Replies
View Related
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 1 Replies
View Related
Jun 15, 2010
What are the various collection types available in Oracle 9i.
Mention the differences between them.
View 4 Replies
View Related
Nov 11, 2011
I read about many indexes as B tree index, function based index,Bitmap Index etc.how many types of Index are there in Oracle and what are they? wat are the categories and sub category? when to use what?
View 6 Replies
View Related
Jul 12, 2012
How many different types of indexes we have in oracle?
View 2 Replies
View Related
Jan 23, 2011
Send all type of matrix report with examples.
View 1 Replies
View Related
Sep 26, 2011
Types of oracle index in detail. consider this situation, if a select query without any where clause takes much time to get data,then we will create index for that table.
Now again run the same select query. this time, whether the query takes much time again or will it work fast? whether the index is used for a select query execution or not?
How we can confirm that whether the oracle using the index normally?
View 2 Replies
View Related
Nov 8, 2012
set server output on;
DECLARE
num number(1) :=1;
num2 number(1) := 1;
BEGIN
WHILE num < 5 LOOP
[code]...........
the output for this loop is:
1+1=2
2+2=4
3+3=6
4+4=8
this is what I wanted. Both variables are incrementing together. However, when I try to use a FOR loop to do the same thing:
set serveroutput on;
DECLARE
num number(1) :=1;
num2 number(1) := 1;
BEGIN
FOR i in 1..4 LOOP
[code].......
I get the result:
1+1=2
1+2=3
1+3=4
1+4=5
2+5=7
2+6=8
2+7=9
2+8=10
3+9=12
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at line 8
I understand the output means the inner loop is running its course before the outer loop is started and then continuing on to exceed its limit, but I don't understand why this is or how to make it so I get the same results as the WHILE loops.
View 4 Replies
View Related
Jul 7, 2011
I have four employee types such as 'C' for consultants, 'S' for staffs, 'W' for workers ,, ('E','S','W') for all types of employees. i have write four queries for showing four types of employees. can it is possible in a single query.
I have written this in oracle forms . I have taken a list item for workers i have taken 'W' , for staffs i have taken 'S' , for consultants i have taken 'C' and for all i have taken 'A'. my column name is emp_type in( :block.list_item ) but it is not taking the value from the list item when the value is emp_type in('C') etc etc..
like this for workers ,staffs . when it is 'A' it will take emp_type in ('E','S','W')
View 3 Replies
View Related
Dec 5, 2010
All nodes in RAC environment are available.But sessions are not load balanced for one/some of them.
Checking alert log the following oracle errors can be found:
alert_<INSTANCE>.log
ORA-21779: duration not active
ORA-06512: at line 1
The root cause of the problem is SMON that is not clearning up some transient types.
I've noticed this effect in Oracle 11g release 1, but there is a chance it happens for early Oracle versions.
View 1 Replies
View Related
Oct 25, 2010
Test data for the problem is as follows.
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for 64-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL> with t
2 as
3 (
4 select 1 id, 12 compid, 1 rel_type, null enddt from dual union all
5 select 1, 13, 1, to_date('31.12.1993','dd.mm.yyyy') from dual union all
6 select 1, 14, 1, to_date('12.06.1996','dd.mm.yyyy') from dual union all
7 select 1, 15, 1, to_date('23.04.2003','dd.mm.yyyy') from dual union all
[code].......
I want to find the the latest compid for individual types 1 and 2 only within a list of ids. Latest compid is defined as the compid associated with the latest end date (null is treated as the latest end date).
So in the above example for rel_type = 1, compid : 12 is latest, for rel_type : 2, compid : 6 is latest.
Datatype for the tables are :
Id : Number
compid : Number
Rel_Type : Number
enddt : Date
View 4 Replies
View Related
Mar 4, 2013
need to use Extend() in nested tables in Oracle? What could be the problem if I do not use this method in my code?
I have a nested collection type (TABLE OF VARCHAR2(32)) declared in my package. My stored procedure takes the TABLE type as input and inserts that data into a database table.
I see that my code works fine without using EXTEND method.
View 10 Replies
View Related
Jun 1, 2013
I'm trying to determine why subtype creation is not working in 11G R2 XE, while it works in 11G R2 EE.
Environment is XE 11.2.0.2 on Windows 7 X64 (yes, not supported but I don't have anywhere else to try this).
Here is a test case, using the SYSTEM account as that is what the developers use (yes, terrible practice) :
--type
-- this works, creates the type
CREATE OR REPLACE TYPE [schema].prod_type AS OBJECT (
pid INT,
pprice NUMBER,
MEMBER PROCEDURE display(pid IN NUMBER))
NOT INSTANTIABLE NOT FINAL;
[code]....
I've tried adding the schema name after UNDER, e.g. UNDER [schema].prod_type with the same result.
View 1 Replies
View Related
Aug 21, 2013
How to eliminate duplicates from record types?Below code errors out with "Wrong number of arguments in call to MULTISET...."
error. DeclareTYPE ln_x_tab IS RECORD(x1 number ,x2 VARCHAR2(4000) ,x3 VARCHAR2(4000) ,x4 VARCHAR2(4000) ,x5 VARCHAR2(4000)); TYPE ln_x_type IS TABLE OF ln_x_tab INDEX BY BINARY_INTEGER; ln_x1 ln_x_type; ln_dist_x1 ln_x_type; gc_stmt varchar2(4000); Begin gc_stmt := ' SELECT x1, x2, x3, x4, x5 FROM table WHERE dynamic_conditions; EXECUTE IMMEDIATE gc_stmt BULK COLLECT INTO ln_x1; ln_dist_x1:= ln_x1 MULTISET UNION DISTINCT ln_x1; End;
I need ln_dist_x1 to have distinct records from table.
View 12 Replies
View Related