I am working on a application. It is in its initial stage so the number of records in table is not large, but later on it will have around 1 million records in the same table. I want to know what points I should consider while writing select query which will fetch a huge amount of data from table so it does not slow down performance. ...
I am going through some pl/sql code with no comments. Trying to make sense of it and optimizing it. Here is the sample: INSERT INTO gtt1 --75711 rows (USER_ID, role_id, participant_code, status_id ) SELECT r.user_id, r.role_id, r.participant_code, MAX(status_id) FROM user_role r, cmp_role c WHERE r.role_id = c.role_id AND r.pa...
I have an Oracle 9i DB installed at remote IBM AIX server. I want to connect to it using C# app (.Net) Presently, I am able to connect to it using SQL Developer and SQLPlus from my machine. But when I try to connect from Visual Studio App, using System.Data.OracleClient. private static string GetConnectionString() { ret...
Application works fine and connects every single time from any machine except the server, where it's supposed to be deployed :/ When run on the server it manages to connect once in like 20 or something attempts. Judging on the funky symptoms, I suspect it to be some kind of a network configuration related issue (as in some randomly lost ...
Hello, How can I get all the Days of the previous month in PL/SQL ? Thanks. ...
I'm using stunnel 4.26 in windows environment to encrypt database traffic between web server and Oracle database. In general it works but I've got couple weird issues and I was wondering if somebody would know answer for it. So I have a web server machine where I run stunnel in client mode. With connection parameters like [SOME NAME] ...
Sometimes I see Oracle databases with names like SOMEDB and other times I see SOMEDB.WORLD. I can use them interchangeably with no apparent differences. What does the .WORLD part mean? Does WORLD have any specific meaning, or is it just an arbitrary string that happens to be used? I've seen other extensions also. ...
I have a package that will be inserting a single parent record in one table, and dependent child records in another table with a FK on the parent_id. Each of these functions will be called by an external program, and all calls to insert children may not be contained within the same transaction. I'm wondering if there is any way for me ...
Something like: create table Employee( ID int primary key, Name nvarchar(200) IDArea int foreign key references Area(ID) ); go create table Area( ID int primary key, Name nvarchar(200) ); Does something like this exist in Oracle? ...
How can I make this get a specific users data, Example user enters his 3 digit code in the badge text box so that only his information is shown. Can I add a where clause to the select command? or is there a better way to do this? Also is there a good book out there with information about asp.net, c# and oracle. // string Badge = ...
Hi, I'm Dinesh from Bhiwani, and I want to know about error handling in PL/SQL. Can anyone help me to find brief description on this topic? ...
is it possible to select colums and do complex operations on them for example select factorial(column1) from table1 or select integral_of(something) from table2 perhaps there are libraries that support such operations? ...
Using SQLDeveloper 2.1.1.64, if you try typing the following code: DECLARE v_status_code NUMBER; v_status_text VARCHAR2(30); v_to_delete NUMBER := 5; BEGIN PACKAGE_NAME.Delete(v_to_delete, v_status_code, v_status_text); END; Pressing Enter after the PACKAGE_NAME.Delete(...) line will make Delete go into all caps (DELETE). I ...
* EDIT6: * This is what ended up working for me (from accepted answer): var ret1 number var tran_cnt number var msg_cnt number var rc refcursor exec :tran_cnt := 0 exec :msg_cnt := 123 exec get_account(Vret_val => :ret1, Vtran_count => :tran_cnt, Vmessage_count => :msg_cnt, Vaccount_id => 1, rc1 => :rc) print :tran_cnt print :msg_cnt pr...
Hello everybody! I am new to Oracle and I've developed an Oracle Portal using OracleAS Portal 10g release 2 (10.1.4). I need to move the application on another computer, and I was thinking of copying it on a CD but no matter I've tried I wasn't able to find the portal pages, nor the images or documents that I've included. Can anybody h...
Lets say i have two tables EMPLOYEE and INCHARGE_ROOM , EMPLOYEE.INCHARGE_ROOMS has | delimted data of INCHARGE_ROOM.ID EMPLOYEE ID NAME INCHARGE_ROOMS 1 K 1|2|3|4 2 S 2|4|5 INCHARGE_ROOM INCHARGE_ROOM_ID INCHARGE_ROOM_NAME 1 ...
June 29, 2010 - I had an un-committed action from a previous delete statement. I committed the action and I got another error about conflicting primary id's. I can fix that. So morale of the story, commit your actions. Original Question - I'm trying to run this query: with spd_data as ( select * from openquery(IRPROD,'select * from ...
Using linq (.net 3.5 +) and predicate builder, I did it like so: var startsWith3Chars = new System.Text.RegularExpressions.Regex(@"^[a-zA-Z]{3}\-", System.Text.RegularExpressions.RegexOptions.Compiled); wherePredicate = wherePredicate.And(x => startsWith3Chars.Matches(x.MATERIALUID).Count > 0); But now I have the need to do this filt...
I have an Oracle source, and I'm getting the entire table, and it is being copied to a SQL Server 2008 table that looks the same. Just for testing, I would like to only get a subset of the table. In the old DTS packages, under Options on the data transform, I could set a first and last record number, and it would only get that many r...
Although Oracle is one of the earliest to create stored procedures (PL/SQL) then Informix with (SPL) which RDBMS products besides DB2 have implemented SQL/PSM or a subset of it after 1998?.. Which RDBMS' can support procs like in the following example?: CREATE OR REPLACE FUNCTION foo1(a integer) RETURNS void AS $$ CASE a WHEN 1, ...