PL/SQL :: Selecting From A View With Nested Object Types
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
ADVERTISEMENT
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
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
View Related
Sep 13, 2010
I am trying to select the owner of a certain object, only knowing the name of the object and the user calling it.
Problem is this object might exist in more than one schema with the same name, and I only need the one that is called by the active user, which himself/herself might have access to other schemas containing their own copy of the object.
Example:
SELECT OWNER
INTO v_schema_name
FROM ALL_OBJECTS
WHERE OBJECT_NAME = p_object_name;
This works fine, until the object exists in more than one schema to which the current user has access.
View 4 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
Dec 2, 2010
I try to do this:
CREATE MATERIALIZED VIEW MV_NESTED_DATA
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
USING NO INDEX
REFRESH COMPLETE
ON DEMAND
START WITH ROUND(SYSDATE)
NEXT ROUND(SYSDATE) + 1
WITH ROWID
AS
select NESTED_TABLE_FIELD from MY_TABLE@Y_DB_LINK;
where NESTED_TABLE_FIELD is a nested table stored as T_NESTED_TABLE
And I get the error: ORA-12014: table 'T_NESTED_TABLE' does not contain a primary key constraint
Why should it if I try to create a MV with "WITH ROWID" refresh option and not "WITH PRIMARY KEY" one?
View 4 Replies
View Related
May 17, 2012
I have a question about nested materialized view.
Firstly, I have created 3 mv log on 3 table(target,target extension,brand). Secondly, I created the first mv and its log.
Lastly, I created the second mv from the first mv. This time I used the pivot function, but it cannot work now.
--1 create mv log
drop MATERIALIZED VIEW LOG ON target;
drop MATERIALIZED VIEW LOG ON targetextension;
drop MATERIALIZED VIEW LOG ON brand;
CREATE MATERIALIZED VIEW LOG ON target with rowid, sequence(id);
CREATE MATERIALIZED VIEW LOG ON targetextension with rowid, sequence(targetid,brandid,EmailPermission,NumberOfAllOrders);
CREATE MATERIALIZED VIEW LOG ON brand with rowid, sequence(brandid, brandname);
--2 create the first mv and it's log
drop MATERIALIZED VIEW mv_target1;
CREATE MATERIALIZED VIEW mv_target1
REFRESH FAST START WITH SYSDATE
NEXT SYSDATE + 1/1440
AS
select t1.rowid t1_rowid, t2.rowid t2_rowid, t3.rowid t3_rowid,
t1.id targetid,
t3.brandname,
[code].....
--3 create the second mv
drop MATERIALIZED VIEW mv_target2;
CREATE MATERIALIZED VIEW mv_target2
REFRESH FAST START WITH SYSDATE
NEXT SYSDATE + 1/1440
AS
select * from mv_target1
pivot(max(EmailPermission) EmailPermission, max(NumberOfAllOrders) NumberOfAllOrders for brandName in ('XXX' XXX,'YYY' YYY ,'ZZZ' ZZZ));
Now, Here is a problem, it throws "ORA-12015: cannot create a fast refresh materialized view from a complex query". Then I used dbms_mview.explain_mview to see the reason, and it tell me the following
REFRESH_FAST_AFTER_INSERT "inline view or subquery in FROM list not supported for this type MV"
declare
lv_sqltext varchar2(4000);
begin
execute immediate 'truncate table mv_capabilities_table';
lv_sqltext := 'select * from mv_target1
pivot(max(EmailPermission) EmailPermission, max(NumberOfAllOrders) NumberOfAllOrders for brandName in (''XXX'' XXX,''YYY'' YYY ,''ZZZ'' ZZZ))';
dbms_mview.explain_mview(lv_sqltext,'nested=>TRUE');
commit;
end;
/
View 6 Replies
View Related
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
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 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
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
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
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
May 9, 2010
how to select the sixth highest earner in my employees table.how to select 6th lowest earner..
View 18 Replies
View Related
Apr 9, 2012
I'm trying to call a custom made PL/SQL function in a SQL query. I want to supply the values of the parameters during the query. I can call the function if I "hard code" the parameter values, but when I try to supply them I get the ORA-06553 error.
This call works:
select pkg_tm_import_util.wb_screen_hr_refresh_func('','','','','','','','','','','','') from dual
However, this does not, but should be the same as the call that works:
select pkg_tm_import_util.wb_screen_hr_refresh_func(
''''','||
''''','||
''''','||
''''','||
''''','||
''''','||
''''','||
[code]....
View 1 Replies
View Related
Jul 11, 2011
i am getting PLS-00306: wrong number or types of arguments in call to 'SECURITY_AUDIT_DTL_TYPE' error below code.
CREATE OR REPLACE PROCEDURE load_data_audit_trail_dtl
AS
TYPE security_type IS TABLE OF SECUIRTY%ROWTYPE
INDEX BY PLS_INTEGER;
security_type_var security_type;
[code]....
View 7 Replies
View Related
Mar 1, 2010
I am a programmer in Oracle PL / SQL in Oracle 10g I'd like to use the same time on one Database two types of fonts English and Russian (Cyrillic). Is this possible and how? NLS_LANG ?
View 2 Replies
View Related
Jul 3, 2011
I am having trouble selecting the row that has max(pay_period) of 13. Here is some test data.
CREATE TABLE TESTME
(
SSN VARCHAR2(11 BYTE),
PAY_PERIOD VARCHAR2(3 BYTE),
PAY_YEAR NUMBER,
KRONOS_ID VARCHAR2(6 BYTE),
LAST_NAME VARCHAR2(15 BYTE),
FIRST_NAME VARCHAR2(14 BYTE),
ADJ_SALARY NUMBER
)
[code]....
View 17 Replies
View Related
Sep 7, 2007
Have a table like this:
ID1ID2DATEID2Value
1121/1/20066
1241/1/2006400
1246/1/2006410
1366/1/2006100
2121/1/20077
2246/1/2007350
2247/1/2007360
I need to return 1 row for each ID1 value - and only the ID2 value of 24 and only the most recently dated record for the multiple ID2 values - query would return:
1246/1/2006410
2247/1/2007360
I have worked and worked on this and I am still stumped (part of the problem may be I am also trying to make this work in Crystal Reports but that is for another day). I need to make this work in Oracle first.
View 3 Replies
View Related
