SQL & PL/SQL :: Creation Of A Object Relational Database
Feb 25, 2012
i am working on a project that requires the creation of a object relational database. A number of different methods are required in order to insert different toy types into the appropriate toy related Object Table(s). Within this there will be the following fields:
Toy_name
Cost
Age_Range
rdy_allocation
and then some more specific information. The specific information for each toy is:
Toy_Type.
The toy_type can be electronic, game, or wooden.
For electronic I need to store Battery_Type and Number_of_Batteries.
For type Game, I need to store, Type, this could card_game, board_game or other_game.
For type wooden, I need to store Painted(Y/N).
The toys are requested by a child and created by elfs. This information also needs to be recorded but im more concerned with the creation of the toy class itself first. Now this was my first attempt at the solution but I cant seem to link the specific toy information into the same table. Will i have to create three separate tables then link them together?:
DROP TABLE TOY_OBJTAB;
DROP TYPE electronic_toy;
DROP TYPE wooden_toy;
DROP TYPE board_toy;
DROP TYPE toy_typ;
[code]....
View 9 Replies
ADVERTISEMENT
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
Jun 19, 2013
I have below query which uses between clause. For some constraints I cannot use between clause so this need this to converted to use only relational operator.
select * from TABLE1
where "+ width +" between start_width " + "and end_width and "+ height +" between start_height " + "and end_height " + "and id = "+ id
View 2 Replies
View Related
Jul 30, 2010
The following runs no problem
SELECT
ACCO.SEQUENCE,
ACCO.DESCRIPTION,
MAX (ACCO.AUDIT_DTE)
FROM
PAS.AUDIT_CLINICAL_CARE_OPTIONS ACCO
WHERE
ACCO.AUDIT_DTE < :AuditDate AND
[code]....
However, when I try to add extra conditions to the status' in the sub select i get the error. This is one way I tried:
SELECT
ACCO.SEQUENCE,
ACCO.DESCRIPTION,
MAX (ACCO.AUDIT_DTE)
FROM
PAS.AUDIT_CLINICAL_CARE_OPTIONS ACCO
WHERE
[code]....
I've tried repeating the sub select for each of the extra status parts but everytime i hit the same problem.
View 1 Replies
View Related
Apr 18, 2013
I am trying to use the REGEXP_LIKE and I am getting ORA-00920: invalid relational operator.
I have run very simplified code to try to sort it out but no success:
SELECT id, address
FROM test_lob_tab
WHERE REGEXP_LIKE(address,'^A');
select 1 from dual where regexp_like('123', '^[0-9]$');
same result from both.
I am using Oracle 11 and freetoad 11.
View 5 Replies
View Related
Oct 23, 2013
Whey I try to run this query in TOAD I get an ORA-00920: invalid relational operator error. It's part of a 10g stored procedure. When I highlight it and run it it prompts me for the missing values and then the error pops up. The AND in line 4 is highlighted.
select CRIME_CLASSIFICATION_ID, crime_type, nvl(count(CRIME_CLASSIFICATION_ID),0) as CRIMECNTFrom vaps.vw_offenses where regexp_instr(valoc,to_char(location_id)) AND ( fromdate is null or offense_date between to_date(fromdate, 'mm/dd/yyyy') AND to_date(todate,'mm/dd/yyyy'))group by crime_classification_id, crime_type
View 3 Replies
View Related
Jan 28, 2013
I have installed oracle database 11gr2 in laptop1 and installed oracle database 11gr2 in laptop2.Both the laptop's are connected to same internet connection through wifi. So my question is can i create a database link between these two databases using this wifi? give some steps in creation of a database link.
View 13 Replies
View Related
May 15, 2012
I am having a template file (only .dbt file) to create the database instance.I Kept it in $ORACLE_HOME/assistant/dbca/template/,after running DBCA it was loaded with details mentioned in it ,but at the second last step when i try to see initialization parameters control file ,datafile and tablespace detail was not mentioned.when it started database creation ,got hanged on 5% completion showing failure in processing system parameters. Is it the right way to create database using DBCA?what should I do to create database using template?
View 1 Replies
View Related
Jan 17, 2003
Creating RAX 9.2.0 on SP2-AIX-4.3.3 64 bit hangs (2 node configuration) tablespacess are on VSD/raw partitions.
View 3 Replies
View Related
Dec 21, 2011
when i connected with oracle user i got error message like "
ERROR:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_APPLICATION_INFO.SET_MODULE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
"
View 1 Replies
View Related
Nov 7, 2013
During the database installation the two nodes after all the prerequisite check and copy of files i am getting the below error message
"Template4 General_Purpose.dbc does not exist. Please specify an existing template for database creation".
How can i overcome this error ? I am installing Oracle 11g R2 11.2.0 On Oracle Enterprise Linux (5.1.19) running on [Oracle Virtual Box 4.1.12 ]. Also what are the steps to uninstall the previously installed db_home1 which failed due to this error.
View 1 Replies
View Related
Jul 9, 2012
During ASM Disk Groups creation after the ASM instance creation, receive the following error: Disk Group ORAASMGROUP2 already exists. Cannot be created again
The Grid infrastructure was deinstall one time and still the same issue.
View 4 Replies
View Related
Jan 21, 2011
manual DB creation.I gone through the below steps:
1) created appropriate folders.
2) Copied a PFILE from an existing DB and changed the contents like DB name etc...
3)Then I Created a text file called %ORACLE_HOME%DATABASEinitmuktha.ora with the following contents: ifile=c:pragpfileinitmuktha.ora
4) Then i set Oracle SID (Through Environment variable in MY COMPUTER --> PROPERTIES...)
5) Then I Created a Windows service for the DB.
6) startup nomount pfile= c:pragpfileinitmuktha.ora
7) Then I ran the database creation command.
The DB was created but when i log in again, it is looking for the pfile in the another DB's location rather than from "%ORACLE_HOME%DATABASE". Where i missed out to create new oracle_home folder?
View 6 Replies
View Related
Nov 5, 2012
Is it possible to create Dictionary Tablespace creation in 10g database?
View 5 Replies
View Related
May 29, 2013
I try to install RAC with 2 nodes OEL 5.8.
GI 11.2.0 is installed...The installation of the database crashed in dbca.
When I tried to rerun, I got: PRCD-1121: Speciffied server pool RAC for RAC is already hosting an administrator-managed configuration for database(s) RAC
View 3 Replies
View Related
Dec 7, 2010
I was able to create the standby database on different server with the same name for the primary and standby database but I want to know that can I create a standby database with different database name than that of the primary database server.
View 1 Replies
View Related
Apr 8, 2013
While Creating database on AIX Machine using DBCA utility I am getting following error
Error Details:
-------------------------------------------------------------------------------------------------------------------
Allocating device....
Specifying datafiles...
Specifing datafiles...
Restoring ...
[code]....
View 2 Replies
View Related
Sep 16, 2011
i am creating database manually in oracle 9i os version ibm-aix5.3 it showing following error
CREATE DATABASE "varathu"
*
ERROR at line 1:
ORA-01501: CREATE DATABASE failed
ORA-00200: controlfile could not be created
ORA-00202: controlfile: '/backup/varathu/control01.ctl'
ORA-27040: skgfrcre: create error, unable to create file
IBM AIX RISC System/6000 Error: 13: Permission denied
View 18 Replies
View Related
Aug 18, 2010
I want to know what is the difference between the file catproc that we run after creation of the database in the Oracle 9i,10g and 11g r2. I have run that file in 11g R2 but it is taking more time and more over there are many new things that i saw in 11g R2 than what were there in 9i and 10g.
View 11 Replies
View Related
Feb 21, 2013
how to create database manually(without dbca).
View 2 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
Aug 15, 2010
I'm getting a "TNS disconnect" after database creation script creation in DBCA. Haven't figured out what's going on. I'm attempting to create a 2nd database on one linux 64 server. I get all the way through DBCA, but it won't go the final mile. Any thoughts? I've tried setting the port to a different port from the existing LISTENER, creating a LISTENER2 and associating it with the 2nd instance.
View 1 Replies
View Related
Feb 16, 2010
As a part of requirement need to clean up some of database objects from database. Before cleaning up need to check for dependencies with in database and also on remote database.
Is there any data dictionary in oracle which proivides information about remote dependencies.
Any other way to get a list of remote dependencies other then manual checking.
View 3 Replies
View Related
Dec 13, 2012
How do I trace an particular object (procedure/package) on the database. my database version is Oracle 11g(11.2.0.2)
View 2 Replies
View Related
Nov 16, 2012
how many rows created (replaced ?) und stored in v$object_usage on each fact of index scan? I'm using 11.2 version of database server
View 7 Replies
View Related
Nov 7, 2013
How to write and fire trigger if any database object will created?
View 1 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
Oct 19, 2012
Just started to learn oracle DBA. I am creating database manually.. I am facing following errors
I executed catproc.sql script it was running suddenlt oracle throwd following error.
CODEPL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Package body created.
No errors.
Package body created.
BEGIN
*
ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system tablespace 'TEMP'
ORA-06512: at "SYS.DBMS_STATS", line 13210
ORA-06512: at "SYS.DBMS_STATS", line 13517
[code]...
Script detail's
CODECREATE DATABASE drprod
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/drprod/redo01.log') SIZE 100M , GROUP 2 ('/u01/app/oracle/oradata/drprod/redo02.log') SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
[code]...
View 14 Replies
View Related