I have a column that is only 6 characters long in a table that references a column that is 20 characters using a foreign key constraint. How do I fix this? Note: The issue was due to the limitation of the Oracle SQL Developer Edit table. When I performed the specific alter column, it worked fine. ...
Hello, how I can create a query in Oracle for "angel" no matter if it is Angel, ANGEL, angel AngEl ...
I am a SQL Server developer, with a task in Oracle. DBA set up a DBLink in Oracle that points at a SQL Server database. I am writing a view on the SQL Server data and then a view on the Oracle side to join it with additional Oracle data. Problem: if I change the definition of the view on SQL Server, even "Select * From myview@dblink" er...
anyone have any idea? ...
I have a database with three tables: userid_tbl, need_tbl, have_tbl create table userid_tbl (user_id varchar2(15) not null primary key); create table need_tbl (user_id varchar2(15) not null, have_item varchar2(100) not null, foreign key (user_id) references userid_tbl (user_id) ); create table have_tbl (user_id varchar2(15) not null,...
Hi, I'm trying to answer the following question... "Explain the issues that arise when NULLs are present in columns that make up foreign keys. Discuss how ANSI have attempted to resolve this issue with the three 'matching rules' that can be adopted when using concatenated foreign keys." Can anyone point me in the right direction as to...
We have an application that needs certain HR (Human Resource) data like the employee table etc. to initiate the manual and automate workflow process it is used for. Well I am looking into the way to integrate or synchronized with Oracle Data,lets say I get a new customer who has something called "Oracle HR" and they say we will buy your...
Using an Oracle 11g database. I have a service that is tagged with: [ServiceBehavior(InstanceContextMode = InstanceContextMode.PerCall, TransactionTimeout="00:00:10")] The method itself looks like this: [OperationBehavior(TransactionScopeRequired = true)] [TransactionFlow(TransactionFlowOption.NotAllowed)] public OrderMess...
I'm looking for the best way to select numbers directly from an in clause. Basically like: SELECT * FROM (2,6,1,8); That doesn't work. I can do it this way: SELECT Lv FROM ( SELECT Level LV FROM DUAL CONNECT BY Level < 20) WHERE Lv IN (2,6,1,8); But that seems to be a bit clunky. Is there a m...
Is there any difference in performance when you break down a stored procedure into multiple procedures instead of having a big procedure?! wich one is faster?! for example: mainSP callSP1 callSP2 callSP3 end; rather than SP .... ..... .... Thanks guys. ...
What is the difference between a User and a Schema in Oracle? ...
I'm having a problem getting a simple ASP.NET webpage to display. The page contains a GridView with a SqlDataSource connected to an Oracle database. When I run the page in the VS debugger (cassini webserver) everything works fine, but when I publish the application to my local IIS server (same machine), I get the following error: ORA-1...
Hello I have a legacy database set with NLS_LANG set to IW8ISO8859P8. This I cannot change. I have another application, that is not working with unicode, that works on the same data my application works on. In some of the fields, and some of the times, the user inserts as part of a string, the character 161 which represents NIS curren...
Suppose I have a PL/SQL stored procedure as follows: PROCEDURE do_something(foo VARCHAR2 DEFAULT NULL) IS BEGIN /* Do something */ END; Now, suppose do_something is invoked two different ways: /* Scenario 1: The 'foo' parameter defaults to NULL */ do_something(); /* Scenario 2: The 'foo' parameter is explicitly set to NULL */ do...
Oracle 10g has Profiles that allow various resources to be limited. Here are some references for clarity - orafaq.com, Oracle Documentation. I am particularly interested in limiting CPU_PER_CALL, LOGICAL_READS_PER_CALL, and COMPOSITE_LIMIT with the goal of preventing a poorly formed statement from destroying performance for every other...
I just had an oracle9 database crash and it left me with a couple of .trc files. Some of them specified indexes that were out of kilter and i dropped and readded those indexes. However, when I run: ANALYZE TABLE TABLESPACE.TABLE VALIDATE STRUCTURE CASCADE; I still get an error: ora_00900, sqlstate: 4200 This creates a .trc file wit...
In the discussion about multiple row insert into the Oracle two approaches were demonstrated: First: insert into pager (PAG_ID,PAG_PARENT,PAG_NAME,PAG_ACTIVE) select 8000,0,'Multi 8000',1 from dual union all select 8001,0,'Multi 8001',1 from dual Second: INSERT ALL INTO t (col1, col2, col3) VALUES ('val1_1', 'val1_2', ...
I have .dmp and .log files. I need to recover the database schema and data using SQLPlus or some feature of EX. How do I do that? I've tried the RECOVER command and impdp. No luck, or I'm doing something wrong. ...
What is the "best" (correct, standard, etc) way to maintain a log of data acquired at a set rate (every minute, every 5 seconds, every 10ms, etc) in an Oracle database? It seems inefficient to store the 7 byte DATE value for every datapoint (especially as the frequency increases). However, packing the data into some type of raw format ...
I am writing a PL/SQL procedure that needs to to dynamically generate some queries, one of which involves creating a temporary table using results from a query taken as a parameter. CREATE OR REPLACE PROCEDURE sqlout(query IN VARCHAR2) IS BEGIN EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE tmp_tab AS (' || query || ');'; END; It c...