SQL & PL/SQL :: Table Inheritance In Oracle

Mar 25, 2010

If Employee and Customer are sub-types of Person, when creating the object tables, how can you name the necessary Nested Tables to represent the relationships of the Child objects?

[PERSON]
|
__________|__________
| |
| |
[EMPLOYEE] [CUSTOMER]
|1 |1
| |
| organises | attends
|(organisedBy) | (isAttendedBy)
| |
|* |*
------[EVENTS]-------

CREATE TABLE Person_TBL of Person (Person# PRIMARY KEY)
NESTED TABLE organises STORE AS EventListType;

Am I right in thinking that the Person table will hold both Employee and Customer objects (object types have been declared such)?

But then how would the Nested tables of Employee.organises and Customer.attends be named?

View 4 Replies


ADVERTISEMENT

SQL & PL/SQL :: Object Type Inheritance And Pipelined Functions?

Sep 24, 2010

It is often necessary to use pipelined function, in order to implement complex logic with ability to specify some parameters, but still return the results as "table" data. Let's assume, we have such function, with the following specification

function vip_turns_t(p_day_start date)
return VIPTURNS_TABLE_T
pipelined;

[Code]....

Now we can do "select from TABLE(vip_turns_t(sysdate))" from any client. Then it often turns out, that we want to expand existing pipelined function with new one, that would expand the column set with some extra columns (retaining all existing columns). It seems natural to me to create new object type UNDER VIPTURNS_ELEM_T and write the (second) pipelined function, foo_List(), which would "wrap" calls to vip_turns_t(), and supplement the values for extra columns, and "pipe" the broader row. The types for "pipelined wrapper" are created as follows:

create or replace type VIPTURNS_2_ELEM_T under VIPTURNS_ELEM_T
(
m_exported_flag char(1),

[Code]....

Next I write function foo_list(), but it compiles with strange error. Below is sceleton for foo_list(), and in comments are errors I encountered. It appears, that compiler does not believe, that variable turn_rec is of VIPTURNS_ELEM_T type. But of what type it could be, then? And, is it possible to know exact type of turn_rec variable?

create or replace
function foo_List return VIPTURNS_2_TABLE_T pipelined
is

[Code]....

View 3 Replies View Related

Oracle 10g - Take Snapshot Of Table Before Insert Or Update Happens To That Table

Dec 28, 2010

I need to take a snapshot of a table before insert or update happens to that table.... in oracle 10g. I am reading the MV docs from oracle and below link..

[URL].......

how MV should be written for this and how to schedule it in dbms_jobs for auto refresh?

assuming that t1 is the table where DML operation are goin to happen so before any insert or update, snapshot has to be taken, and I am assuming that to do this it would look something like this?

create materialized view my_view refresh fast as select * from t1;

create materialized view log on my_view;

and then schedule in a dbms_jobs?

View 1 Replies View Related

PL/SQL :: Static Table Creation In Oracle - Difference Between Static And Dynamic Table

Dec 11, 2012

Static Tables Creation In oracle & Diff Between Static table ,Dynamic table

View 2 Replies View Related

SQL & PL/SQL :: PDF To Oracle Table?

Jan 2, 2013

I need to develop a solution which will read data from a PDF file and insert them to a oracle table. The PDF file is text pdf, which is a kind of form. I need to read specific data from particular field and insert it to oracle table.

View 3 Replies View Related

Export A Table In Oracle 8?

Sep 24, 2013

i am using oracle 8 and i need to export a table and then import it how can i do it with export import command

View 1 Replies View Related

Connecting To Oracle Table With VB 6.0?

Jul 31, 2004

sample code to connect to Oracle table using VB6.0, as well as code to populate the table and to access a particular record in the table.

Asuming userID is 'MyID' and Password is 'MyPass', Server Database is 'Mydata'.

View 2 Replies View Related

Joining 2 Table Together In Oracle?

Jan 7, 2013

Basically, i was asked to join Table 1 and Table 2 together in oracle. I have been given the values of Table 1. For example, Depot_ID has value of DEP01, DEP02, DEP02 etc. Therefore, I have to create table 2 with those attributes given below. But the attribute name of the Table 2 is Depot_No which is different.

My question: Is it correct to give the same values of Depot_ID in Table 1 to Depot_No in Table 2, such as, DEP01, DEP02, DEP02?

How to join those two tables below:

Table 1:

Depot_ID
Address
Location

Table 2:

Product_ID
Depot_No
Quantity
Aisle
Bay

View 2 Replies View Related

SQL & PL/SQL :: RENAME Table In Oracle 8i?

Nov 21, 2011

how to rename table in oracle 8i ?

ALTER TABLE olname RENAME TO nwname;

Above command is not working.

View 2 Replies View Related

SQL & PL/SQL :: Take Table Structure In Oracle?

Jul 11, 2012

How to take table structure in oracle? Actually I got it through this command "SELECT dbms_metadata.get_ddl(a.object_type,a.object_name) FROM user_objects a where object_type != 'PACKAGE BODY'"

any other way to get it? I need like table name field name datatype

View 11 Replies View Related

SQL & PL/SQL :: Oracle Table Partitioning?

Jun 2, 2010

an existing normal table be converted to a partitioned table without recreating the table or truncating/reloaded data?

View 4 Replies View Related

Oracle Table Sort Order

Jul 16, 2010

I know the only way to guarantee a specific sort order result when querying table is by using the order by clause. However, I have an issue where I do not have access to the code for the web user interface of a very lightly used interface (has two users). In that user interface is a drop down box that is populated by a table - one table. The drop down box is populated by the query "select [column name] from [table name]". Right now there are 400+ rows in that table total, so it's small but not having the items ordered is a pain.

I would like to alter something on the db side so the result of "select [column name] from [table name]" is an ordering by the column descending. I don't want the sort order to be the same for all queries (including joins and all) just want to control the order for that one query. It is Oracle9i.

View 1 Replies View Related

Error While Creating Table In Oracle 10g?

Aug 19, 2008

If i create a table in Oracle 10g it givea me the follwoing Error.. what couls be the error...

ORA-00911: invalid character
create table shopper
(
cShopperId varchar2(30) primary key,
cPassword char(20) not null,
vFirstName varchar2(20) not null,

[code]....

View 1 Replies View Related

Add Partial Primary Key Into Oracle Table?

Oct 17, 2006

how to add a primary key into the oracle table but how do i add a partial primary key?

View 2 Replies View Related

Analyzing Partitioned Table On Oracle

Sep 6, 2010

A question regarding analyzing behavior of partition table on Oracle -

Is analyzing different sub partitions within a partition is same as analyzing that partition and what about vice-versa?

Attached File(s)

untitled.JPG ( 9.2K )
Number of downloads: 3

View 2 Replies View Related

Oracle - Display Another Column In Table?

Mar 28, 2010

As far as SQL is concerned, I am using oracle 9i and oracle sql *plus.

1.
This is a table 'spj'
SID PID JID QTY
---------- ---------- ---------- ----------
1 3 2 5
1 2 2 12
1 7 1 10
2 5 3 5

[code]...

I wish to count all the qty for each pid.This is what I have:
SQL> select pid,sum(qty) from spj group by pid;
PID SUM(QTY)
---------- ----------
1 5
2 12
3 29
4 10
5 5
6 6
7 10

and it seems to work fine. But I now I want to display another column in the table called 'pname' which is a primary key in another table 'p'. 'spj.pid' is the foreign key.

2. Another query I can't perform needs the table s
SID SNAME STATUS CITY
---------- -------------------- -------------------- -------------------
1 s1 y asansol
2 s2 y durgapur
3 s3 y durgapur
4 s4 y asansol
5 s5 y kolkata
6 s6 y asansol
7 s7 y tarakeshwar

the question says:

Quote: List supplier names(snames) who supply at least all parts supplied by supplier(sname) S2.

How may I achieve it.

View 5 Replies View Related

Oracle Trigger - Update Same Table?

Mar 30, 2010

I am trying to create some PL/SQL that will create a trigger for UPDATES and INSERTS which will update a column on the same row to the system date.

So far I have:

DECLARE
tablename VARCHAR(30) := 'table';
triggername VARCHAR(30) := 'mytrigger_' || tablename;

[Code]....

I have declared the tablename and triggername outside the trigger creation as I need this to be parameterised for a list of tables.

The procedure IsCDCUser just checks it should be updating for this user.

The problem I get is the following error:

Bind Variable "NEW" is NOT DECLARED

View 3 Replies View Related

Oracle 10g - Delete 1000 Table?

Dec 21, 2007

I have 1000+ tables in a DISCOVER tablespace in 10g.

I need to drop ALL tables in this area and leave the tblsp intact so that I can IMP a data update from 9i (prod) into 10g (test).

Is there an easy cmd to drop all the tbls, or am I missing someway to do this in DB Console for 10g.

View 3 Replies View Related

SQL & PL/SQL :: How To Mask Data In Oracle Table

Aug 4, 2011

In my project I am getting production dump for performance testing and I need to mask few columns with sensitive information.

How can I mask the data in Oracle?

View 5 Replies View Related

SQL & PL/SQL :: Oracle - How To Move Table To Another Schema

Feb 6, 2008

How can I move a table to another schema in Oracle?”

View 33 Replies View Related

SQL & PL/SQL :: Delete Redundant Table In Oracle?

Apr 23, 2012

Is there a SQL query to find out the redundant or unused table in Oracle.

Some tables shows no dependencies to itself or other schemas,but has got valid data in it, deleting it will affect the functionality of the system.

View 3 Replies View Related

SQL & PL/SQL :: Store Image In Oracle 9i Table

Jan 3, 2012

I want to store Image file in Oracle table without storing it physically in the system. how can i store the image file into the oracle table and also retrieve it without storing it physically in the system at any path.

View 5 Replies View Related

SQL & PL/SQL :: 11g Oracle Upgrade Stats Table?

Jan 30, 2012

When we upgrade from 10g to 11g oracle upgrades the stats table by EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE('OWNER','TABLE'); But When we import from 10g to 11g do we need to upgarde the stats table??

View 2 Replies View Related

SQL & PL/SQL :: Auto Rotation Table In Oracle

Nov 18, 2010

Is there any strcuture or fucntion that can make the table have something like rotation.I mean the record can be first in first delete.So,for example , even the table have limit row for example , 100 row .so, the new data can be replace the oldest row in the table auto and insert into the table .so the new row will replace the oldest 99 th row auto .the table still keep 100 row but the data can be update . Is it "partition" or other ??

View 8 Replies View Related

Forms :: Oracle Spread Table OCX

Nov 17, 2005

Any example of how to load data into an oracle spread table OCX control

I have successfully inserted the the oracle spread control in a form and also created the program units form the control but unable to load data into the it at run time.

View 10 Replies View Related

SQL & PL/SQL :: Deleted Table In Oracle 11g - How To Restore

Mar 11, 2013

I deleted a table in oracle sql 11g about two weeks ago and commited. Didn't realise that I would need it later on and now I needed. is there a way a can get reverse this.

View 13 Replies View Related

SQL & PL/SQL :: How Exactly Oracle Updates Rows Of A Table

May 17, 2011

Check the below simple sql stmts:

create table tab_tab(col1 number, col2 number);
insert into tab_tab values(1, 1);
insert into tab_tab values(2, 2);
insert into tab_tab values(3, null);
[code]......

My question is why the table content didn't look like:
col1 col2
----------
1 2
2 2
3 2
4 3
5 4

I thought that when oracle will update 1st, 2nd & 3rd rows then the sub query will get a count equal to 2, but the time when it goes to update the 4th & 5th row the sub query should get back a count equal to 3 & 4 resp. BUT that is not what exactly is happening!!

View 12 Replies View Related

SQL & PL/SQL :: How To Create External Table In Oracle 8i

Jul 6, 2011

oracle version : Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production

How we can create external table in oracle8i:

CREATE TABLE calls_external
(
call_id NUMBER,
emp_id NUMBER,
call_type VARCHAR2(12),
details VARCHAR2(25)
)
ORGANIZATION EXTERNAL

[code]....

Script not working in Oracle8i but working perfect in oracle10g.

View 19 Replies View Related

SQL & PL/SQL :: How To Find Who Created The Table In Oracle

Feb 8, 2011

somebody has created the table , who had created the table Note: not required the schema name,

View 4 Replies View Related

SQL & PL/SQL :: Truncating Table In Oracle 10g - ORA-00054?

Nov 7, 2012

I would like to resolve my problem about truncating table in oracle 10g, i have a table with more than 2 millions of data and i tr to delete data using delete statement but it take many time and i tried with truncate it's ok but when i want to insert data into the table it give me this error ORA-00054 ORA-00054: ressource occupée et acquisition avec NOWAIT

View 30 Replies View Related







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