SQL & PL/SQL :: Access Attributes Of Objects Of Nested Table In Member Function Of Oracle Database

Nov 17, 2011

I have created two types and a list of the first type:

create type type1;
/
create type type1_list as table of ref type1;
/
create type type2;
/

I have now just created the two types as follows:

create type type1 as object(
id# number
);
/
create type type2 as object(
attribute1 type1_list,
MEMBER FUNCTION function1 RETURN NUMBER
);
/

Ok, I've created the tables (I don't know if it's necessary to point out my problem)

CREATE TABLE type1_table OF type1;
/
CREATE TABLE type2_table OF type2
NESTED TABLE attribute1 STORE AS nested_type1_list_table;
/

And what I wanted to do now is to implement the member function1 and check something of the attributes of type1 in the list of attribute1... And that's where my question occurs, how does it work, I can't figure it out. I tried something like this:

Quote:
create or replace
TYPE BODY type2 AS
MEMBER FUNCTION function1 RETURN NUMBER AS

[Code]....

But I don't get the right way, it doesn't work

View 4 Replies


ADVERTISEMENT

PL/SQL :: Oracle Objects - Insert Particular Attributes

Apr 4, 2013

I'm using the below data base version

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production.

I have created a pl sql type as object with five attributes as below.Now im trying to insert only 3 attributes with the attribute names ( As like insert into statement with column names).

Create type address_test as object (
              Streetno varchar2(20),
              Locality varchar2(20),
              City varchar2(20),
              Pin varchar2(7),
              Country varchar2(20))
[code]....

Is that possible to use attribute names in the insert statement of column object type ?

View 9 Replies View Related

SQL & PL/SQL :: Create Nested Query Within Select Of Attributes

Dec 21, 2011

I'm trying to create a sort of nested-query within my select of attributes . i.e.

select A.a,
B.b,
(select count(C.*)
from C
where C. = B.d
group by C.y)
from A a,
B b
where A.d = B.d
and ...

Over-simplifying my query:

select B.desc "Location",
F.desc "Source",
A.amt "Amount",
sum(G.G_CNT) "No. Units",
c.desc "Status"
[code]....

I need to incorporate a count of the number of units from TableG that have a certain status. I tried the following but when I tried to run it, I get an error saying that it's not a Group By expression -the red part is highlighted in TOAD.

select B.desc "Location",
F.desc "Source",
A.amt "Amount",
sum(G.G_CNT) "No. Units",
(select count(*)
from TableG G2
where G2.D_ID = D.ID
and G2.status = 10
group by G2.D_ID)"Count",
c.desc "Status"
[code]....

Any thoughts how I can incorporate a query in my select of attributes?how to Group By something.

View 8 Replies View Related

Get SYS Objects Access?

Oct 26, 2010

What is the minimum access level ( or grant) needed be able to alter user defined (non sys or system) Stored Procedure in residing in SYS schema.

Example - User A need to alter SP sys.myStoredProc.

View 7 Replies View Related

SQL & PL/SQL :: Particular Column Of A Table Has Been Used In What All Database Objects

Jan 11, 2011

Is it possible that we can know that a particular column of a table has been used in what all data base objects?

i.e. like emp.empno is used in which all procedures/packages/triggers etc.

I would like to know the data dictionary view

View 15 Replies View Related

SQL & PL/SQL :: Oracle Nested Table Not Working?

Sep 21, 2011

I have a query which returns a nested table as a result of split function. I used any method to unnest the data. But I couldn't. I try it with this query.Note: To run the following query I attached everything needed.

-- This query gives very strange results.
SELECT *
FROM
(
SELECT

[code]...

View 18 Replies View Related

How To Get Youngest And Oldest Member In The Table

Sep 3, 2008

select
a.first_name,
a.agent_id,
a.birth_date
from
agents a
[code]........

from the above i cannot get the youngest one! this is giving me some mid age.

View 14 Replies View Related

Page 2 - Get Youngest And Oldest Member In Table?

Sep 5, 2008

Do u want to get the youngest & oldest member at each location?

View 1 Replies View Related

JVM :: How To Extract Java Class Objects From Oracle Database

Jun 26, 2013

How to Pick / Extract the java class files from the database.? We have not maintained the latest codes in the oracle application server where java class code is residing.

All the Java Classes are available only in database. So we need to pick the latest java class code from production environment. In TOAD we tried but all class objects are listing at the left side but we are unable to take the code. So how can we take the latest codes(java classes) from the Production Database as a backup. 

View 1 Replies View Related

SQL & PL/SQL :: Access Table From One To Another Database On Same Server

Jun 22, 2010

How can we access a table from one database to another database on the same server.

Let us say One database is "A" and the Other database is "B" both databases are running on one server "ABCD".

My question is how can we access a table from database B to database A.

View 21 Replies View Related

SQL & PL/SQL :: Can Use User Define Value In Nested Function Of A Query

Oct 14, 2010

can we use user define value (how to use '&') in nested function of a query ?

View 7 Replies View Related

Security :: How To Restrict Access To Table (Database Vault)

Nov 12, 2012

I configured a simple security configuration for the HR sample database schema. URL....

Now system user can't select data from table hr.employee, but HR user still can. How to restrict access to table for table owner?

View 1 Replies View Related

Replication :: Can Nested Materialized View Use Pivot Function

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

Forms :: How To Access Oracle Database From PDA Device

Feb 21, 2012

I have one PDA device. I need to run a Oracle form developed in Oracle forms 6i on this PDA by connecting it to the Oracle 10G database running on Linux Server.

I need to know is there any Oracle client for windows 6.0 OS. if yes then how can i install it on PDA and access the Oracle database through forms.

View 1 Replies View Related

Access Oracle Database From Root User?

Dec 18, 2012

i have an linux instance running oracle 11g as oracle OS user. i am able to connect the DB from clients.

i need to access this DB from OS root user. what should i do.

View 7 Replies View Related

PL/SQL :: Index Range Scan And Table Access By Index Rowid Versus Table Access Full

Oct 5, 2013

Let's consider such table that all rows fit into single block:

SQL> create table test as select rownum id, '$'||rownum name from dual connect by level <= 530;
Table created.
SQL> create index i_test on test(id);
Index created.
SQL>
SQL> begin

[code].....

why does approach with full scan take longer even if table occupies only one data block? PS. 11gR2

View 8 Replies View Related

Server Administration :: How To Change Attributes Of Table From Nologging To Logging

Feb 27, 2012

How to change the attributes of a table from nologging to logging?

SQL> select table_name,LOGGING from dba_tables where owner='HXL';

TABLE_NAME LOG
------------------------------ ---
TB_OBJECTS NO

SQL> alter table hxl.tb_test logging;

Table altered.

SQL> select table_name,LOGGING from dba_tables where owner='HXL';

TABLE_NAME LOG
------------------------------ ---
TB_OBJECTS NO

View 4 Replies View Related

ORA-12547 / TNS / Lost Contact / Access Oracle Database 11.2.0.3

Dec 7, 2012

I installed a server on a Linux server Oracle11.2.0.3 5.8 from 64 bits.The user who installed the Oracle software is "oracle" and belongs to oinstall and dba groups.

ID ORACLE
uid=503(oracle) gid=504(oinstall) groups=504(oinstall),505(dba),506(cvargas) context=user_u:system_r:unconfined_t

The software has been installed correctly and have been able to create two databases. As a requirement of the application, you must create an operating system user that launches processes.This user belongs to dba.But when trying to access Oracle via sqlplus gives this error:

-bash-3.2$ sqlplus davinci/xxxxxxx
SQL*Plus: Release 11.2.0.3.0 Production on Fri Dec 7 23:22:40 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
ERROR:
ORA-12547: TNS:lost contact
Enter user-name:

However, if you connect using a network descriptor works correctly.

SQLPLUS DAVINCI/xxxx@tns_alias

So there must be a problem with access to local resources.This user has the same profile file variable (. Bash_profile)

View 6 Replies View Related

Windows / .NET :: Procedure To Connect To Oracle Database From MS Access?

Nov 29, 2012

let me know the procedure to connect to Oracle Database from Ms-Access.

We are using Windows as Operating System.

View 1 Replies View Related

Error In OS Level Authentication For Oracle 11gR2 Database Access?

Mar 18, 2011

We have an issue regarding OS level authentication to access Oracle 11gR2(11.2.0.1) database.

Our environment -
UNIX - AIX 5.3 (OS user id password is having kerberos security).
Oracle 11.2.0.1 (32 bit client) installed in server 1
Oracle 11.2.0.1 (64 bit server) installed in server 2.
Everything works fine when we created a general userid test_db in the database and connect through sqlplus test_db@dbname.

But when we try to use the option of OS level authentication using "sqlplus /", it throws following error and could not be connected.

ERROR: ORA-12545: Connect failed because target host or object does not exist

I have created the same OS user name in database (with external password authentication) with prefix OPS$. we have set ORACLE_SID as well.

View 1 Replies View Related

Server Administration :: How To Access Active Directory With Oracle Database

Oct 12, 2012

How to access Active directory(Microsoft 2003) with oracle database 11g.

View 1 Replies View Related

Enterprise Manager :: Client V9.2.0 Fail When Access Oracle Database 8.1.7?

May 12, 2010

behaviour of oracle enterprise manager. i want to edit a table of a database. after i clicked on the table the oem crashes without any messages - simply disappearing.

i installed oracle9i release2 V9.2.0.1.0 windows client and want to access an oracle8i V8.1.7 server on linux.

View 4 Replies View Related

SQL & PL/SQL :: Open Oracle Table In MS Access Frontend - Gives Error?

Oct 26, 2011

I have a Database that has a frontend in MS Access and a backend in Oracle. When I try to open the Oracle Table/s in the MS Access Frontend, it gives me this error message:

Reserved error (-7711); there is no message for this error.

There is some stuff on the internet about this error but none of it fixes my issue. It seems to be an MS Access error rather than an Oracle error. Only thing is this issue is only on Machines that are running on XP. I have no problem on mine which is Windows 7.

View 7 Replies View Related

Server Utilities :: How To Load Data In Oracle From Microsoft Access Database (MDB) File

Aug 24, 2012

I have one .mdb (Microsoft Access Database) file and it has some tables in it. I had load it once using toad. But now i have to load it frequently into the database. Is it possible using external table, so i can access that tables using "select" statement.

View 6 Replies View Related

SQL & PL/SQL :: Access Rights To Be Given For Using Pivot Function

Dec 28, 2011

Is any access rights to be given for using pivot function...i tried below query but it throws error

select * from BI_BALNCE_SHEET
PIVOT(sum(balance) for gl in('Income','Expenditure'));

View 8 Replies View Related

Table / View V$database Not Found In PL/SQL Function

Feb 18, 2011

I'm trying to create a function that simply returns the current database name (e.g: select db_unique_name FROM v$database ) from a function but when I compile it comes up with :

Error(9,5): PL/SQL: SQL Statement ignored
Error(9,44): PL/SQL: ORA-00942: table or view does not exist

I am compiling and running this as the SYSTEM user and I do think that I need to set privledges/roles, etc to allow this (since I have read that using synonyms in functions/procedures requires permissions...but I cannot seem to find anything that tells me exactly what role/priveledge I need to grant/allow to let this happen.

View 5 Replies View Related

SQL & PL/SQL :: Predefined Function Names As Column Name In Database Table

Apr 9, 2012

Why Oracle allowing the Built-in function names as column name in data base table

Ex :

create table to_char
(to_number number(8) , to_char char(20) , to_date date ,substr varchar2(20))

View 3 Replies View Related

SQL & PL/SQL :: DBA User Access To Other User Objects

Dec 6, 2010

when a dba user'AA' try to access other user'BB' object it gives error pls-00201 identifier 'BB.function_name' must be declared. However the procedure of BB user are being accessible.

View 17 Replies View Related

Truncate / Drop Partitions In Table Having Nested Table Columns

Sep 7, 2012

I have a table that has 2 columns of type nested table. Now in the purge process, when I try to truncate or drop a partition from this table, I get error that I can't do this (because table has nested tables). how I will be able to truncate/drop partition from this table? IF I change column types from nested table to varray type, will it work?

Also, is there any short method of moving existing data from a nested table column to a varray column (having same fields as nested table)?

View 1 Replies View Related

SQL & PL/SQL :: Can Oracle Database Function Return Images

Dec 12, 2012

Can a Oracle Function return Images? I have been getting and able to read lot of solutions but still unconfirmed. As in certain forums it has been mentioned that the difference between procedure and function is that. With procedures you are able to return images but not with function.

View 1 Replies View Related







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