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
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
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
Dec 11, 2012
Static Tables Creation In oracle & Diff Between Static table ,Dynamic table
View 2 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Feb 6, 2008
How can I move a table to another schema in Oracle?”
View 33 Replies
View Related
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
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
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
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
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
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
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
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
Feb 8, 2011
somebody has created the table , who had created the table Note: not required the schema name,
View 4 Replies
View Related
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