problem with populating the collection object.
I am getting an error -
'ORA-06530: Reference to uninitialized composite"
My Code is as below
create type test_t as object (empno number, ename varchar2(20))
/
create type test_tt as table of test_t
/
create or replace procedure test_p
is
[code]....
I am getting the error ORA-06531: Reference to uninitialized collection while calling a table function which is of collection type.
CREATE OR REPLACE FUNCTION FN_GET_LINK_SYS_ID
RETURN PGIPK_DM_PROCESS.DM_SYSID
PIPELINED IS
T_SYS_ID PGIPK_DM_PROCESS.DM_SYSID;
BEGIN
T_SYS_ID := PGIPK_DM_PROCESS.DM_SYSID();
[code]....
Definition of Type is below
--NESTED TABLE TO STORE SYS_ID S OF TABLE
TYPE TAB_SYS_ID IS RECORD(
POL_NO VARCHAR2(60),
POL_END_NO_IDX NUMBER,
PSEC_SEC_CODE VARCHAR2(30),
PRAI_RISK_ID VARCHAR2(10),
[code]....
when i m writing select * from table(FN_GET_LINK_SYS_ID) , its throwing ORA-06531: Reference to uninitialized collection.
concept of composite and candidate keys. i have gone through all the definition part but did not understand the concept of creating candidate key for example:-
composite key
create table test(id number,pincode number(4),name varchar2(15),primary key(id,pincode));
table created with composite bcz i took pair of id and pincode.
here base on the same example want to create candidate key with the syntax.
understanding composite primary key.
I know what is composite key and how it stores the data.. but i am not able to find when i have to choose the columns to be composite keys.
I have the following 3 columns in my VERSIONS table.
MAJOR
MINOR
MICRO
The MAJOR,MINOR and MICRO columns create a unique composite primary key. I need to query out the last version by doing the following 3 queries and combine them.
select max(major) as max_major from versions
select max(minor) as max_minor from versions where major = :max_major
select max(micro) as max_micro from versions where minor = max_minor and major = max_major
Is there any way I can query out max(major),max(minor),max(micro) in a single query.
I have the following case:
create table try_o
(pk1 NUMBER, pk2 NUMBER, fld1 VARCHAR2(10), fld2 NUMBER,
PRIMARY KEY (pk1, pk2));
insert ALL
into try_o values (1,1,'f1',5)
into try_o values (1,2,'f1',5)
[Code]....
I need to get the row that contains the least pk2 among the group of records with the same fld1 and fld2. but if I used
select min(pk1), min(pk2), fld1, fld2
from TRY_o
group by fld1, fld2;
I get a record that doesn't exists as the min(pk1) and min(pk2) are from different records. I need to get the min(pk1) and its corresponding pk2.
I have a lot of queries on a table with WHERE clause:
WHERE CR_DATE > :y AND CR_VALUE = :x
Actually, there is an index on (CR_DATE) but much more selective index is on (CR_DATE, CR_VALUE). If I do not UPDATE any records on this table, is there any difference in INSERT operation (or another problem) when I replace actual index with multi-column index?
I am following Lynda Tutorial for Normalization. I have understand normalization.The core concept of primary key is that it uniquely identifies each record in the table, but here in the given below image the 'COURSE' field is repeating 'SQL101' value again and again but still the teacher in the video is calling it primary key and combination of date & Course column Composite key.how can a field which vales are repeating can be called as Primary Key?
View 12 Replies View RelatedI am replacing a composite PK with a new single PK as business changed. I am creating sequence to fill the new field that should present the PK but I need to fill it according to the values of the old composite PK.
CREATE TABLE ABC
(
COMP_PK1 NUMBER,
COMP_PK2 NUMBER,
COMP_PK3 VARCHAR2(8),
VAL VARCHAR2(50),
PRIMARY KEY (COMP_PK1, COMP_PK2,COMP_PK3)
);
[code].....
I need to update ABC and set ABC_SERIAL = SEQ_ABC.nextval but i need this to be done according to the order of the old composite primary key... i.e. rows with COMP_PK1 = 1 are filled before rows with COMP_PK1 = 2 and so on..
I have to create six tables as part of my assignment. I have created 5 tables. I am having trouble with the 6th. How can I reference a foreign key to a composite primary key? I have colored red the areas that I think are giving me trouble which is in the 6th table, the Registration Table.
Create Table Student
(
StudentID number (6,0) NOT NULL,
Student_Name varchar2 (20) NOT NULL,
Constraint Student_PK Primary Key (StudentID)
);
Create Table Faculty
(
FacultyID number (5,0) NOT NULL,
Faculty_Name varchar2 (20) NOT NULL,
Constraint Faculty_PK Primary Key (FacultyID)
);
Create Table Course
(
CourseID varchar2 (10) NOT NULL,
Course_Name varchar2 (20) NOT NULL,
Constraint Course_PK Primary Key (CourseID)
);
Create Table Qualified
(
FacultyID number (5,0) NOT NULL,
CourseID varchar2 (10) NOT NULL,
Date_Qualified varchar2 (10) NOT NULL,
Constraint Qualified_PK Primary Key (FacultyID, CourseID),
Constraint Qualified_FK1 Foreign Key (FacultyID) references Faculty (FacultyID),
Constraint Qualified_FK2 Foreign Key (CourseID) references Course (CourseID)
);
Create Table Section
(
Section_No number (5,0) NOT NULL,
Semester varchar2 (7) NOT NULL,
CourseID varchar2 (12) NOT NULL,
Constraint Section_PK Primary Key (Section_No, Semester, CourseID),
Constraint Section_FK Foreign Key (CourseID) references Course (CourseID)
);
Create Table Registration
(
StudentID number (6,0) NOT NULL,
Section_No number (5,0) NOT NULL,
Semester varchar2 (7) NOT NULL,
Constraint Registration_PK Primary Key (StudentID, Section_No, Semester),
Constraint Registration_FK1 Foreign Key (StudentID) references Student (StudentID),
Constraint Registration_FK2 Foreign Key (Section_No) references Section (Section_No, Semester, CourseID),
Constraint Registration_FK3 Foreign Key (Semester) references Section (Section_No, Semester, CourseID)
);
This is the error I am receiving:
Error starting at line 1 in command:
Create Table Registration
(
StudentID number (6,0) NOT NULL,
Section_No number (5,0) NOT NULL,
Semester varchar2 (7) NOT NULL,
Constraint Registration_PK Primary Key (StudentID, Section_No, Semester),
Constraint Registration_FK1 Foreign Key (StudentID) references Student (StudentID),
Constraint Registration_FK2 Foreign Key (Section_No) references Section (Section_No, Semester, CourseID),
Constraint Registration_FK3 Foreign Key (Semester) references Section (Section_No, Semester, CourseID)
)
Error at Command Line:8 Column:104
Error report:
SQL Error: ORA-02256: number of referencing columns must match referenced columns
02256. 00000 - "number of referencing columns must match referenced columns"
*Cause: The number of columns in the foreign-key referencing list is not equal to the number of columns in the referenced list.
*Action: Make sure that the referencing columns match the referenced columns.
I have an employee table which has a primary key and a self referencing foreign key, as shown here
create table employee (
id not null,
name not null,
department not null,
supervisor_id not null
,constraint constraint_1 primary key (id)
,constraint constraint_2 foreign key (supervisor_id) references employee (id));
Now if i make the primary key composite, as shown below -
create table employee (
id not null,
name not null,
department not null,
supervisor_id not null
,constraint constraint_1 primary key (id, name)
,constraint constraint_2 foreign key (supervisor_id) references employee (id));
Oracle is throwing the following error -
ORA-02270: no matching unique or primary key for this column-list
How can this error be fixed without changing the composite primary key?
How can i apply composite key on a table?
View 2 Replies View RelatedI have a table say MY_TAB with columns as below
emp_id number,
name varchar2(30),
from_dt date,
remarks varchar2(60)
insert into MY_TAB values (1,'TOM','01-JAN-13', 'some remark');
insert into MY_TAB values (1,'TOM','02-JAN-13', 'some remark');
insert into MY_TAB values (2,'TOM','01-JAN-13', 'some remark');
insert into MY_TAB values (3,'TOM','01-JAN-13', 'some remark');
insert into MY_TAB values (4,'TOM','01-JAN-13', 'some remark');
insert into MY_TAB values (4,'TOM','02-JAN-13', 'some remark');
How do I ensure that when a user tries to insert record with emp_id as 1, then he should only be allowed to enter another from_dt but the value in the name column have to be the same as in the previous row of emp_id 1.
insert into MY_TAB values (1,'TOOM','03-JAN-13') --shld not be allowed.
I need to create a composite unique index on varchar2, number and CLOB column. I haven't used such index before that have the CLOB column indexing. I found the below link related to CLOB indexing...
[URL]......
Links from where I can get related info. Also I would like to know the impact of such index on performance. I have to store and process around 50 million records in such a way, will it be beneficial to use this index?
I Want to tune the attached query. I have tried by creating the normal indexes and composite indexes on the fields . I feel that , Only normal index is required for this instead of composite index?
11:15:19 SQL> @slot.sql
11:16:03 SQL>
11:16:03 SQL> drop table slot purge;
Table dropped.
Elapsed: 00:00:00.05
11:16:03 SQL>
11:16:03 SQL> create table slot
11:16:03 2 (
11:16:03 3 id varchar2 (40) not null,
[code]....
- dynamic sampling used for this statement
22 rows selected.
I have created an non unique index lk_fein on lookup_fein( code,map_id,trash). When I check the explain plan it does a full table scan on lookup_fein. if I force it to use index by it does and the cost also decreases.
SQL> SELECT WORK_FEIN,
2 NON_FEIN ,
3 FI_FEIN ,
4 MFEIN ,
5 TOTAL_FEIN ,
[code]...
Two tables: article i mutations
Article:
artno descr qty sales
1 beer 1 5
2 coke 1 7
3 wine 1 4
4 beer ct 12 2
5 coke ct 6 3
6 wine ct 12 2
7 beer pl 336 1
8 coke pl 336 0
and mutations:
artno mutation
1 4
1 7
2 5
2 8
3 6
I want to get the result like
article sales
beer 365 '5+2*12+1*336
coke 25 '7+3*6
wine 28 ' 4+2*12
How to do a query.
I am working on an application that requires very dynamic access to data. Users will build queries through the interface and the queries will be stored in VARCHAR fields for later use. The function that later uses the query has no way of knowing the field names or data types used in the selection query.
This isn't an issue in any program language that I have used before but in this case, we want to do this in a package within Oracle rather than an external application.
how to reference a field by its location or position in the query.
We need to get the list of fields so we know what each field is named and we need to be able to get at its value dynamically as well. If possible, checking the data type would be useful too but that is less important in this case.If we were doing this in say PHP, we could simply reference the query row and use a command like...
foreach($myrow as $field=>$value)
and this would walk through each field in the row giving us the field name and its value.We need to do this same type of thing in our package.
how I can write a query that get a column data point to another column data in a csv file? For example:
Column A Column B
First Name Mike
Last Name Smith
update one table with reference to another.
Table1:event_channel
Table2:event_inst
I have to update event_channel one column with 4 record(channel_type_id,values 1,2,3,4) with respect to one record of event_inst table column(event_instance_id).
event_inst table column(event_instance_id) has respective 4 records in event_channel
I simulated a sample procedure for my requirement.When i try to compile procedure it throws error 'cannot mix single and multiple rows ( bulk) into'...I have to pass a table as dynamic in a cursor ,collect the data and process it using and forall.
create table dynamic (emp_name varchar2(20),emp_id varchar2(20), tel_no varchar2(20);
create table dynamic_1 (emp_name_1 varchar2(20),emp_id_1 varchar2(20), tel_no_1 varchar2(20);
insert into dynamic values ('Mike','1','123456');
insert into dynamic values ('Nike','2','1234567');
create or replace PROCEDURE proc_1(t_name varchar2) IS
TYPE parent_rec IS RECORD (part_num dynamic.emp_name%type,part_name dynamic.emp_id%type,part_id dynamic.tel_no%type) ;
p_rec parent_rec;
rec_array SYS_REFCURSOR;
BEGIN
OPEN rec_array FOR 'select EMP_NAME, EMP_ID,TEL_NO FROM '||t_name ||' WHERE EMP_ID = ''1''' ;
[code]....
I use some quantity of functions with list of account id like in example below. Some functions use the same account id list, some use another. Everything works fine except those days when changes come and lists should be updated. Then I should edit each function... I think about creating new table for reference list like
CREATE TABLE MYREFERENCELIST
AS
SELECT XXXX AS ACCOUNTID, YYYY AS LIST1 FROM DUAL
UNION ALL
SELECT ZZZZ AS ACCOUNTID, UUUU AS LIST2 FROM DUAL
FUNCTION ACCOUNTID
(arc_date date,
cid number )
RETURN NUMBER
AS
[code]..........
I have two tables A and B. In table A there is a field which contains a string of 20 characters; this essentially holds 5 codes of 4 characters each.
Table B is a reference table. It holds the 4 character code and the description.
I am trying to run a select query to bring back the description of the code for the first 2 codes in table A but i am not sure how to bring back the descriptions! The below is what i am trying to achieve.
SELECT
TableA.ID,
SUBSTR(TableA.Code,1,4) Primary_Code,
[Code].....
I have a table DW_ORDER_CHANNEL and I need to know what are the other objects accessing this table. As i need to alter this table so the dependent objects get invalid. how to get the dependent object on this table?
View 2 Replies View RelatedI have the following table:
CREATE TABLE test_A(member_id NUMBER(2) PRIMARY KEY, MEMBER_name VARCHAR2(20), MEMBER_parent NUMBER(2) DEFAULT NULL);
INSERT ALL
INTO test_A VALUES (1, 'mem1', NULL)
INTO test_A VALUES (2, 'mem2', NULL)
INTO test_A VALUES (3, 'mem3', NULL)
INTO test_A VALUES (4, 'mem4', 1)
INTO test_A VALUES (5, 'mem5', 1)
[code]....
As the actual data is huge, I need to know the best (least expensive) way to select each parent and then all its direct child ordered by member_id the output should look like:
1mem1(null)
4mem41
5mem51
6mem61
2mem2(null)
7mem72
8mem82
10mem102
3mem3(null)
9mem93
11mem113
I have created the following partition. but i want to alter in future . how to alter the reference partition. is it avilable in reference partition.
CREATE TABLE EMD_FILE_LOAD_DETAILS_PR
(
FILE_REFERENCE_ID ,
FILE_NAME ,
FILE_TYPE ,
FILE_GROUP_NAME ,
[Code]...
When I am executing below code it is working fine
DECLARE
CURSOR C1 IS
SELECT EMPLOYEE_ID FROM EMPLOYEES;
TYPE EMP_ID_TYPE IS TABLE OF EMPLOYEES.EMPLOYEE_ID%TYPE INDEX BY BINARY_INTEGER;
TABLE_EMP_ID EMP_ID_TYPE;
BEGIN
[code].....
But when i am executing below code it is showing error as subprogram or cursor 'C1' reference is out of scope
DECLARE
CURSOR C1 IS
SELECT EMPLOYEE_ID FROM EMPLOYEES;
TYPE EMP_ID_TYPE IS TABLE OF EMPLOYEES.EMPLOYEE_ID%TYPE INDEX BY BINARY_INTEGER;
TABLE_EMP_ID EMP_ID_TYPE;
BEGIN
[code].....
The only difference in above two blocks is that EXIT WHEN Statement.
CREATE OR REPLACE FUNCTION CALC_PLAYER_AVG
(V_ID in PLAYER_BAT_STAT. PLAYER_ID%TYPE)
RETURN NUMBER
IS
V_AVG NUMBER;
[code]...
This function must be moved to a package. Which additional statement must be added to the function to allow you to continue using the function in the group by the clause of a select statement?
A. PRAGMA RESTRICT_REFERENCES (CALC_PLAYER_AVG, WNDS, WNPS);
B. PRAGMA RESTRICT_REFERENCES (CALC_PLAYER_AVG, WNPS);
C. PRAGMA RESTRICT_REFERENCES (CALC_PLAYER_AVG, RNPS, WNPS);
D. PRAGMA RESTRICT_REFERENCES (CALC_PLAYER_AVG, ALLOW_GROUP_BY);
Answer: A
----I had gone through this question in dumps,about PRAGMA_RESTRICT_REFERENCES, but i m not aware of WNDS, WNPS..or reference for this term, & how it works in above exampl
I've successfully created the below two tables (disregard my use of "char" variable type; beyond the scope of my question):
create table Test_req (
Req_ID varchar2(4) not null,
Req_Comment varchar2(50) null
);
create table Test_n_cycle (
Req_ID varchar2(4) not null,
[code].....
Then I successfully performed the two alter statements:
Alter table test_n_cycle
add constraint test_n_cycle_pk primary key (req_id, test_id);
Alter table test_req
add constraint test_req_pk primary key (req_id);
Then tried this freign key reference statement
Alter table test_n_cycle
add constraint
foreign key (req_id) references test_req (req_id)
Error report:
SQL Error: ORA-00902: invalid datatype
00902. 00000 - "invalid datatype"
Is this because req_id is also defined in the Primary key definitions on these two tables, thus making the field not valid for referential constraint definition?
is it in Orcale not possible to refenerence two tables in a with clause?
With CTE
AS
(
Select ...
),
[code]...
If I do a select on my first table I get this error: ORA-32035: unreferenced query name defined in WITH clause - With CTE AS
In Sql Server it works fine ...but how can I create two CTE Tables in Orcacle?