SQL & PL/SQL :: Oracle SQL Simple Table Verification?

Feb 27, 2012

I'm taking my first SQL class and trying to replicate the code in this picture. I'm wondering if the code is space-sensitive in certain areas

For example, in the very last line, does it matter if I have:

CONSTRAINT ORDER_LINE_FK2 FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCT_T(PRODUCT_ID));

vs.

CONSTRAINT ORDER_LINE_FK2 FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCT_T (PRODUCT_ID));
(where the second one has a space between PRODUCT_T and (PRODUCT_ID));

Also, I don't have Oracle SQL on my computer.

CREATE TABLE CUSTOMER_T
(CUSTOMER_IDNUMBER(11,0)NOT NULL,
CUSTOMER_NAMEVARCHAR2(25)NOT NULL,
CUSTOMER_ADDRESSVARCHAR2(30),
CITYVARCHAR2(20),
STATEVARCHAR2(2),

[code]....

View 7 Replies


ADVERTISEMENT

Forms :: Verification Of Item Text?

Mar 19, 2013

I want to make a verification of an item text ,the user must enter a number of length 4. So I created a text item :number(4). and in when-validate-item trigger of this item ,I put this

if length(:block.empno) != 4 then
message('this item must containonly 4 digit');
end if;

=> the problem, when the user entered "0123" ,it should be acceptable !! but this is not the case ...

View 12 Replies View Related

Forms :: Verification Of Item Manually?

Jan 15, 2013

I want to check an item manually. Here is the

//when-validate-item
declare
i number;
begin
if :a.b is null then
i:=show_alert('null');
else if :a.b between 1900 and 2013 then
message('date valid');
else
i:=show_alert('date_invalid');
:a.b:=null;
raise form_trigger_failure;
end if;
end if;
exception when others then
i:=show_alert('exception');
end;

the problem for example when I entered a value "3000" I received two messages (the first on the alert "date_invalid" and the 2nd on the exception message) but normally I must received one message, the relative "date_invalid"

View 4 Replies View Related

Database File 1 Failed Verification Check

Apr 3, 2011

i did the following steps for resolving.

1 alter database backup controlfile to trace;

2 startup nomount;

3 recover database using backup controlfile;//in this step generate error msg "database not mounted"

4 alter database open// this is also happend above error

ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/u01/app/oracle/oradata/DB11G/system01.dbf'
ORA-01207: file is more recent than control file - old control file

View 6 Replies View Related

Forms :: Checking For Password Verification For 3 Counts?

Dec 22, 2011

I have made a simple form in which user will be entering his username and relevant password to get authorised. My issue is , if the user is entering wrong password for 3 times then both fields should get disabled. I did the form until the point where it checks wether the user is authorised or not. But how to give count on failure of passwords.

View 5 Replies View Related

Forms :: Verification That User Selected Valid Date

Mar 19, 2013

I have 3 types list items:

1) :1 Day -> 31
2) Month: 1 -> 12
3) Year :1990 -> 2013

I want to check that the user has selected a valid date .. if not an error message will be displayed, for example: 31-02-2013: will be rejected

View 9 Replies View Related

RAC/ASM Clusterware Installation :: Verification Of Steps To Convert Single Node To RAC

Jan 16, 2013

I have a single node Oracle E-Biz 12.1.3 Installation.We plan to convert this to a dual node RAC install and I wanted to run by the steps to perform this AND clone over the current production system to the new RAC system (note this will not be using ASM - but will be based on NetApp NFS)

1. Install GRID 11.2.0.3
- installed acorss two nodes, Binaries can be shared
- voting disks / cluster rigistry MUST be shared

2. Install Database Software for RAC
- Binaries cannot be shared and must be installed on each node independently

3. Configure the GRID for database (database / listeners)

4. Clone over the stand alone system (clone database as RAC)

I will be using SCAN - and therefore would expect all the web services / concurrent manager nodes to point to the SCAN hostname as opposed to individual host names.

View 1 Replies View Related

Server Utilities :: Not A Valid Export File Header Failed Verification

Nov 22, 2012

Sometimes during recovery I encountered the following message "

This error occurs when Backup And Restore Occur between different versions

View 4 Replies View Related

Server Utilities :: ORA-01187 / Cannot Read From File 203 Because It Failed Verification Tests

Apr 26, 2011

I want to export all db using data pump. I got this error when using it:

Export: Release 10.2.0.4.0 - Production on Thu Nov 25 11:46:48 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export the entire database ...

. exporting tablespace definitions
EXP-00068: tablespace TISDATA is offline
EXP-00008: ORACLE error 1187 encountered
ORA-01187: cannot read from file 203 because it failed verification tests
ORA-01110: data file 203: '/u03/app/oracle/product/10.2.0/oradata/unidev/TISTEMP01.dbf'
EXP-00000: Export terminated unsuccessful

View 2 Replies View Related

Real Application Clusters :: 11gR2 Verification Of Shared Storage Accessibility

Sep 18, 2012

I am trying to apply 11.2.0.2.5 psu on a 2-node cluster running on RHEL 5.5 vms. Followed ORACLE-BASE examples when installed this laptop RAC. I am not using any ACFS and none of GI and DB homes are shared. But, on node 2, cluvfy THINKS database home is shared.

[oracle@rac1 trace]$ /oracle_grid/product/11.2.0.2/bin/cluvfy comp ssa -t software -s /oracle/product/11.2.0.2/db_1 -n rac1,rac2 -display_status
Verifying shared storage accessibility

Checking shared storage accessibility...

"/oracle/product/11.2.0.2/db_1" is not shared

Shared storage check failed on nodes "rac2,rac1"

Verification of shared storage accessibility was unsuccessful on all the specified nodes.

NODE_STATUS::rac2:VFAIL
NODE_STATUS::rac1:VFAIL
OVERALL_STATUS::VFAIL
[oracle@rac1 trace]$ /oracle_grid/product/11.2.0.2/bin/cluvfy comp ssa -t software -s /oracle_grid/product/11.2.0.2 -n rac1,rac2 -display_status

Verifying shared storage accessibility

Checking shared storage accessibility...

"/oracle_grid/product/11.2.0.2" is not shared

Shared storage check failed on nodes "rac2,rac1"

Verification of shared storage accessibility was unsuccessful on all the specified nodes.

NODE_STATUS::rac2:VFAIL
NODE_STATUS::rac1:VFAIL
OVERALL_STATUS::VFAIL
[oracle@rac1 trace]$ hostname
rac1
[oracle@rac1 trace]$ echo $ORACLE_HOSTNAME
rac1
[oracle@rac1 trace]$
##################################################################################
[oracle@rac2 trace]$ /oracle_grid/product/11.2.0.2/bin/cluvfy comp ssa -t software -s /oracle/product/11.2.0.2/db_1 -n rac1,rac2 -display_status

Verifying shared storage accessibility

Checking shared storage accessibility...

"/oracle/product/11.2.0.2/db_1" is shared

Shared storage check was successful on nodes "rac2,rac1"

Verification of shared storage accessibility was successful.

NODE_STATUS::rac2:SUCC
NODE_STATUS::rac1:SUCC
OVERALL_STATUS::SUCC
[oracle@rac2 trace]$ /oracle_grid/product/11.2.0.2/bin/cluvfy comp ssa -t software -s /oracle_grid/product/11.2.0.2/ -n rac1,rac2 -display_status

Verifying shared storage accessibility

Checking shared storage accessibility...

"/oracle_grid/product/11.2.0.2/" is not shared

Shared storage check failed on nodes "rac2,rac1"

Verification of shared storage accessibility was unsuccessful on all the specified nodes.

NODE_STATUS::rac2:VFAIL
NODE_STATUS::rac1:VFAIL
OVERALL_STATUS::VFAIL
[oracle@rac2 trace]$ hostname
rac2
[oracle@rac2 trace]$ echo $ORACLE_HOSTNAME
rac2
[oracle@rac2 trace]$

I can not determine any reasons and do not know how to fix.

View 3 Replies View Related

XML DB :: Simple Node With Attribute?

Jan 4, 2013

1. i register scheme like this
DECLARE
  schemaurl VARCHAR2(256);
  xmlschema xmltype;
BEGIN

[code]...

/2. i try to insert XML
insert into TMP_SPM_ACTLOGLINE values (xmltype(
  '<ACTLOGLINE xmlns:xs="http://www.w3.org/2001/XMLSchema"
               xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
               xmlns:xsd="http://www.w3.org/2001/XMLSchema">
     
[code]...

The question is how should the scheme look like to insert this XML?

View 5 Replies View Related

Simple Update Query Not Returning More Than One Row?

Sep 9, 2009

I've just started with the Oracle SQL and come from a heavy MS SQL background and I understand that here are some natural differences in the syntax but I'm stumped as to why the following sql represents a problem:

update MASTERMICODES t1
set t1.TEMPTA = ( select t2.TAFCODE
from TA_FEATURES t2
where t2.FCODE = t1.FCODE
)

It returns Error report:
SQL Error: ORA-01427: single-row subquery returns more than one row
01427. 00000 - "single-row subquery returns more than one row"
*Cause:
*Action:

I want it to return more than one row...in fact I want it to make on all rows that have the same fcode between tables.

View 2 Replies View Related

SQL & PL/SQL :: Get Number Of Records (not A Simple Count())

Mar 4, 2010

using the sql statement in SQL server. I noticed some of the sql syntax are different. My sql statement is as below;

---Select fieldA, Sum(fieldB+fieldC) as fieldD, fieldE, fieldF from TableA group by fieldA, fieldE, fieldF ---

the output will be as below;

fieldA fieldD fieldE fieldF
------ ------------- ------ ------
fieldA fieldB+fieldC fieldE fieldF

However, I need to have the total of no of records displayed for the sql statement and I need it in one sql statement with the one above so that I can display the total no of records along with the information for each field.

View 2 Replies View Related

SQL & PL/SQL :: Compilation Error With Simple ELSIF

Mar 12, 2010

what I don't get is what is wrong with my ELSIF statement? Majic program that will guess how much you are worth

ACCEPT s_first PROMPT 'Enter your random letter'
DECLARE
random_letter VARCHAR(20);
personal_worth VARCHAR(20);
BEGIN
[code]....

View 6 Replies View Related

Forms :: How To Design A Simple Graph

May 23, 2011

How to design a Simple Graph?

View 1 Replies View Related

PL/SQL :: Simple Character Sorting Of Results

Jun 8, 2012

I have three SQLS which I am running using "Union"

e.g

select col1,count(*) from table where type ='parts'
group by col1
union
select col1,count(*) from table where type ='consumables'
group by col1
union
select 'Total',count(*) from table
group by 'Total'

I want to force the total column to be the last row of the select and the parts column to be the first row always. I did try something like prefixing the col1 with an alphabet. But that shows up in the output also. Was looking for a better way!

View 5 Replies View Related

ODP.NET :: Ora-00936 With Simple Select Statement?

Dec 19, 2012

I get "ORA-00936: missing expression" when I issue the following SQL:

select 4 - .2 from dual

from the following VB.NET code (3.5 framework):

Dim conn As Oracle.DataAccess.Client.OracleConnection = New Oracle.DataAccess.Client.OracleConnection(My.Settings.oraconn)
conn.Open()
Dim da As Oracle.DataAccess.Client.OracleDataAdapter
Dim ds As New DataSet
da = New Oracle.DataAccess.Client.OracleDataAdapter("select 4 - .2 from dual", conn)
da.Fill(ds)
MsgBox(ds.Tables(0).Rows(0)(0))

I have the following installed when connecting to 10.2.0.3 database:
11.2.0.1 client installed
11.2.0.3.20 ODP installed

This is really strange behaviour as the following SQL variants work:
select 4 - 0.2 from dual
select 4 - '.2' from dual
select -.2 + 4 from dual

This is a big problem as I have a complex application deployed in a number of sites so won't be able to rollout ODP 11

View 11 Replies View Related

SQL & PL/SQL :: Using UTL_SMTP To Send Simple Emails

Aug 2, 2006

I tried using UTL_SMTP to send simple emails. My code is as follows:-

PROCEDURE prc_send
IS
- variable to hold the smtp server connection
v_smtp_connection utl_smtp.connection;
-- variable to hold the contents of the email message
v_smtp_host VARCHAR2(100) DEFAULT 'smtp.server.com';
-- variable to hold the smtp port
v_smtp_port NUMBER DEFAULT 25;
BEGIN
-- establish the connection to the smtp server
[code].........

When I execute this, It shows authentication error. Error looks like ORA-29278: SMTP transient error: 454 5.7.3 Client was not authenticated. I know my smtp server requires password authentication. But I am not aware of how to do it with UTL_SMTP.

View 9 Replies View Related

Write Simple Sql Which Would Delete Data From Last N Months?

Apr 20, 2009

am trying to write a simple sql which would delete data from last n months but it will keep the data for the first of each of those month from current sysdate

e.g

Jan 1 - 30 deletes 2 - 30 keeps data for 1st
Feb 1 - 28 deletes 2 - 28 keeps data for 1st
Mar
Apr Current sysdate
May

View 3 Replies View Related

Precompilers, OCI & OCCI :: Simple Program Compilation

May 11, 2009

I am trying to use OCIColl datatype and thus wanted to check if a simple program using OCI functions and data types gets compile or not. sO I wrote the follwoing function which I want to compile as a DLL.

extern "C" CLUSTERINGDLL_API OCIColl *fnClusteringDLL(OCIColl *dxm[])
{
OCIError *errhp;
OCINumber num_1, num_2, sum;
OCINumberAdd(errhp, &num_1, &num_2, &sum);
std::cout<<"Members in the array are "<<dxm[1]<<std::endl;
std::cout<<"2 Member in the array are "<<dxm[0]<<std::endl;
printf("Is this printed %f", dxm[0]);
return dxm[1];
}

But I am getting the following error:-
Error6error LNK2019: unresolved external symbol _OCINumberAdd referenced in function _fnClusteringDLLclusteringdll.obj...

I have included the library folder containing oci.lib in the project directories.

View 7 Replies View Related

SQL & PL/SQL :: How To Commit After 1000 Records In Simple Insert Statement

Oct 10, 2011

I am using Oracle 11g Release 11.2.0.1.0

OS: windows

I am taking an example of EMP table in a scott schema.

EMP_1 is also a same structure of EMP.

Here is the Insert statement used.(i might get where clauses etc in the real scenario i am taking a simple example here)

INSERT INTO SCOTT.EMP_1(JOB,ENAME,EMPNO) SELECT JOB,ENAME,EMPNO FROM SCOTT.EMP

table EMP is having around 25 million records.

i would like to do a commit after every 1000 records. as my redo log is getting full if i use a single commit at the end.

Quote:pls note: Increasing of redo log is not possible as of now

is there anyway i can acheive this without cursor bulk collect.

View 7 Replies View Related

Precompilers, OCI & OCCI :: How To Compile Simple PRO*C Code In Linux

Nov 6, 2008

Linux 2.4.21-37.ELsmp #1 SMP Wed Sep 7 13:28:55 EDT 2005 i686 i686 i386 GNU/Linux...As you'll see I do not understand anything about PRO*C. I need to compile this PRO*C code to use it to retrieve DBMS PIPES being sent.

I'm trying to compile it using the following command:
gcc -o IAPIPE IAPIPE.c

The code - file IAPIPE.c
#include <stdio.h>
#include <string.h>

EXEC SQL BEGIN DECLARE SECTION;
VARCHAR username[20];
int status;
int msg_length;
char retval[2000];
EXEC SQL END DECLARE SECTION;
[code]....

View 3 Replies View Related

SQL & PL/SQL :: Code Returning A Null For Simple Subtraction And Division?

Aug 7, 2012

Why does is the code blow reuturning a null for a simple subtraction and division?I am trying to do 4-13/9 for one and 4-22/9 for 22

select unit,
term_1201,term_1202,r_slope,r_intercept,
(r_intercept - term_1201) / decode((r_slope),0,1) as ONE,
(r_intercept - term_1202) / decode((r_slope),0,1) as TWO

[code]...

View 6 Replies View Related

Reports & Discoverer :: Develop A Simple Matrix Report Manually

Nov 3, 2010

I have developed simple matrix report using wizard. But now I want to do it manually.

View 3 Replies View Related

Forms :: Simple Data Block - Serial Number Generation

Jun 2, 2010

I have created simple data block by wizard and taken tabular layout 10 rows and 6 columns

i want to generate serial number on first column .....

what need to use..?

View 11 Replies View Related

Forms :: Wait Event During Execution Simple Select Statement

Jul 19, 2013

Yesterday i got wait event when executed simple select from table.This select was like:

SELECT emp_number from employer where subs_id = 111

I got one row, select is very fast.In our Core Bank System we have package with function which returns such information. I tested this select on test DB, and nothing wrong. But when I executed such select and package on Production DB, DB Admin saw that 88 sessions waits when my session release the resource. But what can happen, it was simple select? I used PL/SQL developer to get information from table:

1) SELECT emp_number from employer where subs_id = 111 then
2) Package with this function

Another users used Oracle Forms screen to execute package. How simple select statement could stop all DB?

BANNER
1Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
2PL/SQL Release 10.2.0.5.0 - Production
3CORE 10.2.0.5.0 Production
4TNS for 64-bit Windows: Version 10.2.0.5.0 - Production
5NLSRTL Version 10.2.0.5.0 - Production
[code]...

Forgot to say that after succeful execution on Prod DB I disconnected, and in EM my session was INACTIVE.

View 3 Replies View Related

SQL & PL/SQL :: Error Occurred During Execution Of Simple JDBC Program In Eclipse

Jun 27, 2013

java.sql.SQLException: ORA-04054: database link GMAIL.COM does not exist

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:207)
at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:946)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1169)
at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1615)
at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1580)
at com.jdbc.pack.Lab1.main(Lab1.java:31)

View 7 Replies View Related

Replication :: ORA-12013 / Updatable Materialized VIEWS Must Be Simple Enough TO DO Fast Refresh

Aug 20, 2009

CREATE MATERIALIZED VIEW LOG ON ABC;
CREATE MATERIALIZED VIEW MV_ABC
REFRESH FAST
START WITH SYSDATE
NEXT SYSDATE+20/(24*60)
FOR UPDATE
AS
SELECT * FROM ABC WHERE TMSTP> SYSDATE-1;

It is working fine. Appended the Query with WHERE clause in MV_ABC MVIEWS.

DROP MATERIALIZED VIEW LOG ON ABC;
DROP MATERIALIZED VIEW MV_ABC;
CREATE MATERIALIZED VIEW LOG ON ABC;
CREATE MATERIALIZED VIEW MV_ABC
REFRESH FAST
START WITH SYSDATE
NEXT SYSDATE+20/(24*60)
FOR UPDATE
AS
SELECT * FROM ABC WHERE TMSTP> SYSDATE-1;

ORA-12013: updatable materialized VIEWS must be simple enough TO DO fast refresh

View 6 Replies View Related

Creating Materialized View On Remote Simple View

Dec 11, 2012

I'm trying to create a Materialized View on a remote database from a simple view. The reason is, the data owners don't want to grant explicit tables privileges to external subscribers.

A new schema is created to publish data in the form of a view. I've created mlogs on the master tables, and granted them to the subscriber, but it's still complaining about a missing primary key on the view. A primary key does exist in the master table.

Is there another work around for this situation without having to work inside the data sources' environment?

View 5 Replies View Related

Spatial :: Convert Multi-line To Simple Line

Feb 13, 2013

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0

We are processing spatial data from another source to display in our GIS environment. The data is a set of multi lines. The gtype is 2006. A typical geometry looks like:

SDO_GTYPE     2006
SDO_SRID      31370
SDO_POINT.X   NULL     
SDO_POINT.Y   NULL
SDO_POINT.Z   NULL
SDO_ELEM_INFO (1,2,1, 7,2,1)
SDO_ORDINATES (105094.84, 195084.96,
105094.54, 195080.22,
105094.84, 195084.96,
105094.84, 195084.96,
105094.68, 195082.47 )

Now, this is not an actual multiline... it's just encoded as a multi line, but if you look at the coordinates you'll see that the end point of the first line is the same as the beginning of the second line (105094.84, 195084.96).

A better way to encode this geometry would be:

SDO_GTYPE     2002 <---
SDO_SRID      31370
SDO_POINT.X   NULL     
SDO_POINT.Y   NULL
SDO_POINT.Z   NULL
SDO_ELEM_INFO (1,2,1)
SDO_ORDINATES (105094.84, 195084.96,
105094.54, 195080.22,
105094.84, 195084.96, <---
105094.68, 195082.47 )

Is there a standard function in Oracle that does this conversion for me? Or do I have to write my own :)

View 4 Replies View Related







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