SQL & PL/SQL :: Add A Not Null Constraint On A Collection For Testing?

Nov 19, 2010

I am trying to add a not null constraint on a collection for testing. It not allowed null values as below

pointers@ORCL> declare
2 type test_type is table of number not null;
3 t_test test_type;
4 begin
5 t_test := test_type(2,4,null); --null is being added
6 dbms_output.put_line('Type variable is initialized and the first element value is '||t_test(1));

[code]....

But what I observed is, i can add, null elements by using 'EXTEND' routine though 'not null' constraint is used for that type.

The below is the example.

pointers@ORCL> ed
Wrote file afiedt.buf
1 declare
2 type test_type is table of number not null;
3 t_test test_type;
4 begin
5 t_test := test_type(2,4);

[code]....

View 6 Replies


ADVERTISEMENT

Query Testing For Null Value In IN

Nov 1, 2013

Oracle 10.2.0.4 I belive the following query is incorrect in my opinion. (there is an index on col but NULLS are ignored? 

SELECT COUNT(*) from <TABLE>

where col in (null,'a','b','c') this works (no errors) and returns pretty quick. However I think the correct query would be SELECT COUNT(*) from <TABLE> where col IS NULL OR col in ('a','b','c') This one takes a long time. As I see it it does a table scan for NULL part and uses the index for the rest as the index cannot be used for NULL values. explanation on this, especially why Oracle accepts the first query "where col in (null,'a','b','c')" without any issue. 

View 3 Replies View Related

SQL & PL/SQL :: Unique Constraint When 1 Column Is Not Null

Jun 15, 2012

I have 3 columns in a table: colX, colY, colZ.

Trying to find a way to prevent duplicates with these, but only if colX is not null.

For example, if there are already values for: colX = 1, colY = 1, colZ = 1

then:

Allowed: colX = null, colY = 1, colZ = 1
Not allowed: colX = 1, colY = 1, colZ = 1

I can't create a unique constraint on these columns because there are many null values for column colX, and as mentioned, when colX is null, colY and colZ can be any values.

I also tried using a before insert trigger to find duplicates before posting and raise an error if found, but this causes an ORA-04091 mutating error since the trigger in the table is referencing itself to check for duplicates.

Also, I know there is something called a function based index, but I cannot use those with my code, so I need another solution if possible.

View 4 Replies View Related

Creating Unique Constraint That Excludes Null

May 22, 2013

I have a table where I want user to fill in unique values for a field which is easy to do.

Problem is sometimes the values can be null so an ordinary unique constraint does not work because multiple null records. Is there a way of validating only non null values to ensure all data entered that is non null is unique?

View 1 Replies View Related

SQL & PL/SQL :: Why Cannot Create Null Constraint On Table Level

May 19, 2011

Why can't we create null constraint on table level?

View 20 Replies View Related

SQL & PL/SQL :: Is NOT NULL Needed If A CHECK Constraint Is On Column

Dec 13, 2010

In the below code, do I need the 'NOT NULL' after the 'state char(2)'? I am guessing that I do not need it since I have the CHECK constraint on the column.

CREATE TABLE employee(
id PRIMARY KEY,
first varchar(20) NOT NULL,
middle varchar(20),
[code]....

View 10 Replies View Related

SQL & PL/SQL :: Entering Values As Not Null Constraint To Column In Oracle Without Disturbing Old Records

Jul 1, 2013

previously i set null constraint to the column and creating some rows and need to change new entering values as not null constraint to the column in oracle without disturbing the old records. how can I do that.

View 5 Replies View Related

Get Table Name / Constraint Name / Constraint Type With Join Processes In String Type

Dec 25, 2007

i want to get table name, constraint name, constraint type with join processes in string type. this is what i want: alter table tablename add constraint constraintname constrainttype(columnname)

View 1 Replies View Related

PL/SQL :: Related Collection Within A Collection

Sep 20, 2012

There is a nested table with in a nested table type and i want to print the value and again assign a new value to the next subscript and i have tried a lot but couldn't find any solution.

declare
type type_name is table of varchar2(10);
type type_name1 is table of type_name;
names type_name1:=type_name1(type_name('hello'));
begin
-----HOW TO PRINT A VALUE--------
-----HOW TO ASSIGN A NEW VALUE TO NEW SUBSCRIPT
null;
end;

1) need to print the values of names(1)
2)Assign a value to names(2)

View 3 Replies View Related

DB Query Load Testing?

Apr 12, 2013

10.2.0.5 on Solaris 10.

We need to do load testing on our DB before going live. Mainly with 5 queries which are mainly used in our web interface to pull data from the database. For example. I need to send morethan 400-500 sessions to our database using this 5 queries (with different values) and to note down the time taken with every 50 number of users..

View 2 Replies View Related

Testing Fields With Multiple Paths?

Jan 18, 2007

My task is to test a field in a certain database. We shall refer to the field as ship_to. There is an algorithm for which the field ship_to is populated and higher up in the algorithm, a variable we shall call X is created. The algorith states that I should assign the variable to ship_to unless X is = -1. If X is = -1, the algorithm continues with multiple joins and assignments. What would be the best way to go about coding this solution. There are 4 individual paths. I have only described the first path, but they are similiar in structure. I was thinking of using either Case's or decodes?The field ship_to is a number. I have already created a statement to test the sum of the Target, but now I need to test the entire algorithm to see if it too sums the Target.

View 1 Replies View Related

Testing 9i On Virtual Machine Under RHEL 4.8

Sep 1, 2010

I work as a Sys. Admin. for several RHEL 3.8 servers, most of them are clusters of 2 machines. All these servers are running Oracle 9.2.0.7 database. They are running fine on a separate filesystem. So everytime the system has to be formatted for some particular reason, there is no need to re-install the database.

I am trying to make some tests by running the same filesystem containing the oracle database in a fresh Red Hat Enterprise Linux 4.8 X86_64 Install. This task has become impossible, and I'm not quite sure why.

I installed all the compat- packages required for a fresh oracle 9 install in a RHEL4 machine, but at the time of stating the STARTUP sentence, it gives me the next error:

CMCLI ERROR: OpenCommPort: connect failed with error 2.
CMCLI ERROR: OpenCommPort: connect failed with error 2.
CMCLI ERROR: OpenCommPort: connect failed with error 2.
CMCLI ERROR: OpenCommPort: connect failed with error 2.
CMCLI ERROR: OpenCommPort: connect failed with error 2.

After this error (repeated) it says something like: Cannot start an already running database.... But if i stat a shutdown sentence, it says that the instance has not been initialized....

I don't know whether i have to re-install all the oracle software in order to make a clean install in the new kernel version or not, i tried to apply a patch, and the oracle installer recognized the installation i had.

I think it might be because the original system is configured to work as a cluster, and i'm running it on only a virtual machine.

View 6 Replies View Related

PL/SQL :: How To Create One-column Collection From Another Multi-column Collection

Mar 22, 2013

I am describing a SQL statement to get it's column list:DECLARE

cur     NUMBER;
col_cnt INTEGER;
rec_tab DBMS_SQL.DESC_TAB;

[Code]....

Now I need to get out the columns list from rec_tab.col_name and put it to my_colls collection. Have Oracle any build-in to do that?

View 4 Replies View Related

Unable To Open Physical Database For Testing?

Jun 13, 2011

I have problems in opening the database of the physical standby in read- write mode/ read only mode. I have a primary server which is running on 2 node RAC and the standby on a seperate single server being used as DR. I recently got this server and my aim was to isolate the standby server from primary server and perform few test. As it has never been tested even once.

Primary Database spec: (2 Node Rac on ASM)
Oracle Version : 10.2.0.3.0
O/s : HP-UX B.11.23

Standby Database spec: (Single Node)
Oracle Version : 10.2.0.3.0
O/s: HP-UX db01 B.11.23

Error:

--------------------------------------------------------------------------------

alter database recover managed standby database cancel;

Database altered.

SQL> alter database open
2 ;
alter database open
*
ERROR at line 1:
ORA-16004: backup database requires recovery
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '+DATA/dprod/datafile/system01.dbf'

--------------------------------------------------------------------------------

Parameters :

log_archive_dest_2 string SERVICE=PROD1 LGWR ASYNC VALID
_FOR=(ONLINE_LOGFILES,PRIMARY_
ROLE) DB_UNIQUE_NAME=PROD
remote_archive_enable string true
fal_client string DPROD
fal_server string PROD1, PROD2

Steps tried so far:
Changed log_archive_dest_2 = DEFER on both the primary nodes

Standby :

startup nomount
alter database mount standby database;
alter database recover managed standby database disconnect;
alter database recover managed standby database cancel;
alter database open/readonly (tried both)
Same error.

On Primary:
SQL> select max(sequence#) from v$log_history;

MAX(SEQUENCE#)

--------------------------------------------------------------------------------
55702

on Standby:

MAX(SEQUENCE#)

--------------------------------------------------------------------------------
33289

Primary Database:

SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# FIRST_TIME NEXT_TIME

--------------------------------------------------------------------------------

55700 13-JUN-11 13-JUN-11
55700 13-JUN-11 13-JUN-11
55701 13-JUN-11 13-JUN-11
55701 13-JUN-11 13-JUN-11
55702 13-JUN-11 13-JUN-11

60824 rows selected.

Standby Database:

SEQUENCE# FIRST_TIME NEXT_TIME

--------------------------------------------------------------------------------

55698 13-JUN-11 13-JUN-11
55699 13-JUN-11 13-JUN-11
55700 13-JUN-11 13-JUN-11
55701 13-JUN-11 13-JUN-11

15206 rows selected.

Additional Information : There is a delay of 20 minutes before the logs get applied. which has been intentional set by team. Dataguard broker is not configured as well.

View 7 Replies View Related

Workflow :: Run Procedure Manually For Testing Purpose

Oct 23, 2012

i want to run this procedure manually for testing purpose

procedure Reject_Doc(itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out NOCOPY varchar2 )

what are the possible values for the variables itemtype,itemkey, actid,funcmode?

View 3 Replies View Related

SQL & PL/SQL :: Hierarchical Query - Connect NON-NULL Rows To Preceding NULL Row

Aug 29, 2012

I have the following query:

select col_1,col_9 from
book_temp b
where b.col_1 is not null
order by to_number(b.col_16)
;

What I want to add is the following:

COL_9
=====
NULL
A
B
NULL
C
D
E
F
NULL
G

I need to connect the NON-NULL rows to the preceding NULL row.

View 15 Replies View Related

SQL & PL/SQL :: Testing Nested Blocks But Logic Is Apparently Not Correct?

Apr 9, 2013

I am testing nested blocks, but my logic is apparently not correct.

I am running this script:

DECLARE
v_one number(1) := 1;
v_two number(1) := 2;

[Code]....

I receive the output from only one of the nested blocks:

bad
PL/SQL procedure successfully completed.

SQL>

I understand that I don't need nested blocks for the example above, but this was just a condensed version of what I'm trying to do. I think nesting blocks will be easier to read and maintain, instead of having a huge CASE statement.

How can I execute only the nested block for which the condition is true and ignore the nested blocks that follow?

Are nested blocks not the correct answer here? Should I be looking at invoking procedures/functions instead?

View 8 Replies View Related

Clone Testing Database From X86 To X64 Using DBCA Clonic Templates

Sep 14, 2012

I'm using Oracle Database 11g R2 for study purposes.Currently I'm learning about the DBCA clonic templates.I have an Oracle DB 11g R2 X86 running properly in Ms Windows XP Professional SP 3 X86, using DBCA I created the seed template file .dbc and the .CTL and .DBF files, later I copied those files to a server running Oracle DB 11g R2 X86 in Ms Windows 7 Ultimate X86. Again, using DBCA I successfully created the source database through the seed template file, everything was ok.

Now, I formatted my testing server and I installed Ms Windows 7 Ultimate X64 and Oracle Database 11g R2 X64. I copied the seed template file .dbc and the .CTL and .DBF files to "assistantsdbca emplates" directory. Well, I started DBCA to try create the source database and when the DBCA is creating and starting the Oracle instance it shows the errors:

ORA-00604: error occurred at recursive SQL Level 1
ORA-06553: PLS-801: Internal error [56327]

Is it possible to clone my testing DB from X86 to X64 using DBCA clonic templates?

View 8 Replies View Related

Networking And Gateways :: Testing Private Database Link With Same Name As Public

Oct 13, 2010

We are currently replacing all public database links with private db links in our prod environment. The requirement is

1) private db link name should be same as public
2) Need to create and test out private db links before we can drop public db links.

How can we test out private db links while public db links are still there with the same name?

when I execute SELECT * FROM DUAL@DBLINK from user who created this private db link, which db link it will be using- public or private?

View 9 Replies View Related

Performance Tuning :: How To Clear Cache For Testing Tuned Code

Jul 4, 2011

i am testing a proc after tuning it but the problem is, it is taking a very very less time which it shouldn't. I know that it is because of the buffer cache and the shared pool. that why i need to clean the cache to retest it.

I cannot bounce the database as other schemas are part of it. so is there any way to clean the cache for that particular schema i.e bouncing any particular schema(i know that the term is not appropriate).

View 4 Replies View Related

SQL & PL/SQL :: Counting NULL Vs NON-NULL In A GROUP BY Clause

Jun 21, 2010

I am running a GROUP BY query on a few columns of enumerated data like:

select count(*), Condition, Size
group by Condition, Size;

COUNT(*) CONDITION SIZE
-------- ---------- --------
3 MINT L
2 FAIR L
4 FAIR M
1 MINT S

Well, let's say I also have a timestamp field in the database. I cannot run a group by with that involved because the time is recorded to the milisec and is unique for every record. Instead, I want to include this in my group by function based on whether or not it is NULL.

For example:

COUNT(*) CONDITION SIZE SOLDDATE
-------- ---------- -------- ----------
3 MINT L ISNULL
2 FAIR L NOTNULL
2 FAIR M NOTNULL
2 FAIR M ISNULL
1 MINT S ISNULL

View 9 Replies View Related

SQL & PL/SQL :: Date Field - Not Null Column To NULL

Mar 16, 2011

I have a table which has a not null column. the column is date field. I am trying to change it to Null. But it is giving a error.

I am using below query.

ALTER TABLE T_test
modify (paid_to_date null)

View 9 Replies View Related

SQL & PL/SQL :: Unique Null And Multiple Non-null

Oct 24, 2013

create table test
(
id int ,
dat date
)
/

I want to implement a business rule such as we have for each id at most 1 dat null. So, I've created this unique index on test.

create unique index x_only_one_dat_cess_null on test(id, case when dat_cess is null then 'NULL' else to_char(dat_cess, 'dd/mm/yyyy') end);

insert into test values (1, sysdate);
insert into test values (1, sysdate - 1);
insert into test values (1, null);
insert into test values (1, null);
-- -----
insert into test values (2, sysdate);
insert into test values (2, sysdate - 1);
insert into test values (2, null);

The 4th insert will cause an error and this is what I wanted to implement. OK. Now the problem is that for non-null values of dat, we can't have data like this

iddat
------------
124/10/2013
123/10/2013
123/10/2013
1

because of the unique index (the 2nd and the 3rd row are equal). So just for learning purposes, how could we allow at most one null value of dat and allow duplicates for non-null values of dat.

View 2 Replies View Related

SQL & PL/SQL :: How To Modify Null Column To Not Null

Jan 9, 2012

when i follow this steps mention on this website

[URL].........

to modify column from null to not null i got this error and on this website its show successful

my steps are

first i create a table

SQL> create table Stu_Table(Stu_Id varchar(2), Stu_Name varchar(10),
2 Stu_Class varchar(10));

Table created.

Then insert some rows into Stu_Table

SQL> insert into Stu_Table (Stu_Id, Stu_Name) values(1,'Komal');

1 row created.

SQL> insert into Stu_Table (Stu_Id, Stu_Name) values(2,'Ajay');

1 row created.

SQL> insert into Stu_Table (Stu_Id, Stu_Name) values(3,'Rakesh');

1 row created.

SQL> insert into Stu_Table (Stu_Id, Stu_Name) values(4,'Bhanu');

1 row created.

SQL> insert into Stu_Table (Stu_Id, Stu_Name) values(5,'Santosh');

1 row created.

SQL> select * from Stu_Table;

ST STU_NAME STU_CLASS
-- ---------- ----------
1 Komal
2 Ajay
3 Rakesh
4 Bhanu
5 Santosh

Table Structure is like this

SQL> Describe Stu_Table
Name Null? Type
----------------------------------------- -------- ----------------------------
STU_ID VARCHAR2(2)
STU_NAME VARCHAR2(10)
STU_CLASS VARCHAR2(10)

now when i try to modify this Stu_id column to not null its give me error.

SQL>ALTER TABLE Stu_Table MODIFY Stu_Id int(3)not null;
ALTER TABLE Stu_Table MODIFY Stu_Id int(3)not null
*
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option

and when i try to add new column with not null its also gives me error

SQL> ALTER TABLE Stu_Table add C1_TEMP integer NOT NULL;
ALTER TABLE Stu_Table add C1_TEMP integer NOT NULL
*
ERROR at line 1:
ORA-01758: table must be empty to add mandatory (NOT NULL) column

View 6 Replies View Related

SQL & PL/SQL :: Function In Oracle To Select Not-null Columns At Beginning And Null Columns At End?

Jul 12, 2012

I have 8 columns. Some of them might be null.I want to display all 8 columns in my result. Not null columns will be first and null at the end.Here is a sample data :

Employee table :
Employee_id Emp_fname emp_lname emp_mname dept salary emp_height emp_weight
1 aaa ddd d1 100 6 180
2 bbb ccc 120 169
3 dfe d2 5.9 223

The expected result is :
result1 result2 result3 result4 result5 result6 result7 result8
1 aaa ddd d1 100 6 180
2 bbb ccc 120 169
3 dfe d2 5.9 223

View 8 Replies View Related

Client Tools :: Tools For Load Testing On Oracle - J2EE Application?

Jan 5, 2012

which are recommended Tool for load testing (for performance) on Oracle-J2EE, 3 Tier applications?

Is 'Oracle Application Test Suite' the best for such test where we can simulate numbers of users and their various actions?

Does it come with Oracle Database license or we have to buy it separately?

View 1 Replies View Related

PL/SQL :: How To Update Null Data With Previous Not Null Data

Oct 25, 2012

Initially i have inserted the data into table like

Date                   xxx       yyyy
1/1/12 1 1
2/1/12 null null
3/1/12 null null
4/1/12 1 1
5/1/12 1 1
6/1/12 null null

in above example data is null for some date here my requirement is how can i copy before not null data(1/1/12) to *2/1/12, 3/1/12* .

View 3 Replies View Related

SQL & PL/SQL :: Collection Over Cursor?

Jun 30, 2010

What is the advantage of collections over CURSOR? What is the use for collections and when collections is used?

View 1 Replies View Related

SQL & PL/SQL :: Collection Of Records

Sep 29, 2010

I have been doing some code in collection for testing. I have been doing the below but getting the error.

SQL> desc t_mine;
Name Null? Type
----------------------------------------------------- --------
OWNER NOT NULL VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(30)

SQL> ed
Wrote file afiedt.buf

1 declare
2 v_start_time PLS_INTEGER := DBMS_UTILITY.GET_TIME;
3 v_elapsed PLS_INTEGER;
4 type allobjects_record is record
5 (owner varchar2(1000)
[code].......

ERROR at line 15:

ORA-06550: line 15, column 15:
PL/SQL: ORA-00947: not enough values
ORA-06550: line 15, column 3:
PL/SQL: SQL Statement ignored

I followed the below link

[URL].......

View 5 Replies View Related

SQL & PL/SQL :: Table Variable Or Collection

Jul 11, 2012

Is there any table except (global temp table and permanent table) which can be used to store data and be used in inner, left and right join for a session.

View 7 Replies View Related







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