PL/SQL :: How To Use Multiple Concurrent Contexts

Oct 24, 2013

I have a context package that uses a session_id.

I want to use multiple sessions as I will have multiple users creating contexts at the same time.

Once the context is created with a session_id I want to reference that session_id in a view.

Can this be done? Using Oracle 10g R2 Context:

 -- create ctxCREATE OR REPLACE CONTEXT MY_CTX USING CTX_PKG ACCESSED GLOBALLY;  -- ctx package  CREATE OR REPLACE PACKAGE CTX_PKG IS    PROCEDURE SET_SESSION_ID (IN_SESSION_ID VARCHAR2);    PROCEDURE CLEAR_SESSION (IN_SESSION_ID VARCHAR2);    PROCEDURE SET_CONTEXT(IN_NAME VARCHAR2, IN_VALUE VARCHAR2); END CTX_PKG;  -- context packatge  CREATE OR REPLACE PACKAGE BODY CTX_PKG IS    GC$SESSION_ID  VARCHAR2 (100);    PROCEDURE SET_SESSION_ID (IN_SESSION_ID VARCHAR2)    IS    BEGIN     GC$SESSION_ID := IN_SESSION_ID;   

[Code]...

The first query returns valoc but the second query doesn't return the session_id.

View 5 Replies


ADVERTISEMENT

SSO Preventing Multiple Concurrent Oracle Sessions From Loading

Nov 16, 2012

We are experiencing a problem with SSO causing 2nd or 3rd concurrent Oracle sessions to hang. The Oracle application hangs during loading and the task manager has to be used to close the application.

I have tested logging onto our application servers using SSO and I cannot load more than 3 concurrent Oracle sessions. When I bypass the SSO and logon to the same server I can load more than 20.

View 1 Replies View Related

Concurrent Updates On A Value

Apr 18, 2013

You have a stock_amt value in one table and there is a procedure that updates and substracts from this stock_amt.

lets say in a store a have a stock amount of 50 items and this procedure, for each sale is subtracting from this value and it is not allowed to go below zero. The process, beside the update, on this column (set stock_amt = stock_amt - x) is doing a lot of other updates on other tables and it total it takes like 0.5 seconds. Everything is fine till I want to execute this procedure by 50 users in parallel.

The initial implementation, to avoid some dead locks and we put a lock on that column (stock_amt) but there where to much waits. we cannot hold that lock for 0.5 seconds.

What will be the best approach for this? For this stock amt problem, maybe the solution can be a trade like: do not update that column every time but once in a while, by another process or by a materialize view logic.

but what if my column is a critical value like a Prepay balance or bank balance and it needs to be updated in near real time. What will you do?

View 0 Replies View Related

Running Concurrent Request Permanently

Oct 8, 2010

having a concurrent request that is always up?

As part of an interface I've implemented, I have a 'listener' concurrent request that registers for a dbms_alert and uses wait_one to detect appropriate alerts. When an alert has been detected, a loader concurrent request is kicked-off, before the listener loops back to the wait_one call. I've set a nominal wait_one timeout period of ten minutes. It is not envisaged that many alerts will be raised - maybe several a day at the moment, but I am always reluctant to use polling unless I can avoid it.

Our EBS system has gradually ground to a halt after the release of this program, and the DBAs suspect this as a possible cause. CPU usage was very high overall, with various java-related usage figure also being high.

The overall idea is to have a 'poor-mans' equivalent of OAI, so I'd hope to add more listeners, with each being the equivalent of an OAI adapter.

Surely a big powerful thing like Oracle EBS is not going to keel over just because of a few processes doing little more than waiting for alerts?

View 7 Replies View Related

Running Parallel / Concurrent Scripts?

Feb 24, 2011

I am trying to execute two scripts at the same time (concurrent) in Oracle SQL Developer. I know we can schedule a job using DBMS_job package and define the job. But is there any other way of doing it using Threads ?

View 3 Replies View Related

SQL & PL/SQL :: Preventing Concurrent Running Of A Stored Procedure

Oct 9, 2012

I have a stored procedure that is run from a command within our Clarity application.

The procedure involves some SQL Reads and SQL Inserts.

We have experienced users running the SP at the same time (slim chance to do this) and it creating duplicate entries.

if there is a clever way of preventing the same SP to be run concurrently?

Initially I was thinking of having the first step of the SP to interrogate a flag into a custom table - which the SP then sets to 1 if it is running, and 0 at the end.

Are there better more efficient/effective ways of doing this?

View 7 Replies View Related

Forms :: Running Concurrent Report From FORM

Oct 26, 2010

My requirement is:

I want to run multiple Reports Concurrently, being called from my Form.

Suppose I have 10 Reports Say Report1, Report2, ....Report10.

I am using "RUN_PRODUCT" to call these Reports from my Form. But it's taking too long time to run all the Reports one after another. Can I run all these Reports Concurrently at the Same time.

View 4 Replies View Related

Restrict Concurrent User And / Or Session From A Client?

Nov 5, 2012

We have an application with database Oracle 10g.

I want to add a new validation to restrict concurrent user and/or session from a client. (we have almost 60 client firms using the software to enter daily trasnactions). All users from all clients are connecting to the database using a common functional ID.

What I did was:
1) Add a column 'user_logged_in' in the master table for client and update it as Y when user from that client logged on to the system,
2) Insert the application logon details (we can figure out the client details from this) into a global temp table,
3) Create a logoff trigger to update the 'user_logged_in'flag in client master table by using values from global temp table when session logged off and
4) Restrict the users from same client if the flag is 'Y'

But the problem in this case is logoff trigger will not be executed in case if the session got killed or terminated abnormally.

View 3 Replies View Related

Deadlock In Database Because Of Concurrent Data Deletion

Feb 4, 2013

I am facing Deadlock issue in my transaction when record is been deleted in the same table in parallel from a PLSQL package. The package is been called from the Java code.The example and the table structure is given below.

Col1 of tab1 is foreign key to col1 of tab2.
Commit will not be done until all the below dml scripts are executed in both environment.

1st session:

Delete from tab1 where col1=1001;
Delete from tab2 where col1=2001;

Result: Deletion successful

2nd session:

Delete from tab1 where col1=1002;
Delete from tab2 where col1=2002;

Result: Deletion successful

1st session:

Delete from tab1 where col1=1003;
Delete from tab2 where col1=2003;

Result: Deletion successful

2nd session:

Delete from tab1 where col1=1004;
Delete from tab2 where col1=2004;

Result: Query is executing for a longer time.

1st session:

Delete from tab1 where col1=1003;
Delete from tab2 where col1=2003;

Result: Query is not executed and throws Deadlock in the back end.

View 3 Replies View Related

Automate Multi User Concurrent Data Loads

Nov 15, 2011

We are designing a three tiered system (client, application/web server, database server) that will allow clients through a web interface to select a text file from the operating system and load that file into a intermediate table (import database table). Many users will do this concurrently and data will load into a single table. The text files come in monthly for about 100 firms. No user is able to insert or update the data of another users data (there is a check out system). Their are about 30 to 40 users that will be using the system doing various functions but it is possible for 10 to 20 users to import data at one time. The files can have anywhere from 2000 to 25000 records at a record length of 398.I am concerned about having a good design strategy as well as decent performance.

Problems with each of the Oracle loaders.

1) External tables - Can not read data text files on the application server(which is where they want the text files to go) secondly you cannot create a instance of a external table. Multiple users will be using the external table to point to different text files and loading at the same time.

2) Sqlloader - is mainly a OS level tool and I am not sure how I could programatically point it to a different text file each time a user wants to load. The client will have to have the ability through code to point sqlloader to the correct file name.

I had a creative approach and was wondering if this would work. I would like to use external tables just like a connection pool. I would propose first a scheduled OS job to move files to the database server. I would create about 20 external tables with 20 different directory objects. Using a stored procedure for the user to call and pass in file name and audit info as needed. I would use a Load lock pool table (my invention) to load the name or a code for the external table in use. The procedure loads this code into my load lock pool table when a external table is in use and deletes the name when the load is completed. The procedure would check through a series of if statements whether a particular external table was in use. If in use (exist in load lock pool table) I would check the next available external table until a external table not in use is encountered. Now potentially 20 users at one time but not likely would be laoding into the same table at one time.My questions

1) Could Oracle handle this strategy? What do I need to consider performance wise with the possibility of so many users loading into a single table at one time?

2) Do any of you maybe have another strategy to do this?

View 8 Replies View Related

Server Administration :: Updating Table Through Concurrent Sessions

Feb 5, 2011

I have a table with counter value which will be incremented or decremented by several application servers.

SQL> select * from test;

COUNTER
----------
10

Application servers(multiple servers) will be running update against this row for increasing the counter value or decreasing the counter value.

update test set counter=counter+1;
update test set counter=counter-1;
update test set counter=counter+1;
update test set counter=counter+1;

So when update happens concurrently to this table will the counter value gets messed up?

I did a small test by opening multiple sessions for running update and the result I got for above update statement was 11,10,11,12.

But our developer is bit skeptical about this approach and he is using select for update and then updating the row.

Which approach will be better?

View 9 Replies View Related

SQL & PL/SQL :: Using Analytic Function To Determine Maximum Concurrent Calls?

Apr 27, 2010

I have a requirement to calculate the maximum number of concurrent calls from the following data:

Create_date connect_date_time disconnect_date_time duration ...
12/01/10 13:20:26 1263253551 1263254153 602
...

I have attempted to use the analytic function to keep a running total of the count of active calls based on the connect and disconnect times given for each record row.

e.g.

SELECT
count(*) calls,
avg(duration)/60 average_duration_mins,
max(duration)/60 max_duration_mins,
sum(duration)/60 total_mins,
(SUM(DURATION)/60)*0.04 total_cost_4c_per_min

[code]....

View 7 Replies View Related

Forms :: Run Concurrent Program On Save (Push Button)

Jun 16, 2011

How can we run concurrent program on SAVE (push button) in oracle forms?

View 3 Replies View Related

Performance Tuning :: How To Control Concurrent Inserts Of Record By Parallel Sessions

Aug 7, 2012

There is a detail table

There is a summary table

During batch process record is entered in detail table as well as summary table.

The process first checks if record exists in summary table for same group_no and if 'yes' then "updates" the record with the newly added amount (sums it) else inserts a new record
Whereas in the detail table it inserts the record directly

detail table :
group_no doc_no amount
101 doc1 100
101 doc2 200
102 doc3 300
102 doc4 400

summary table :
group_no amount
101 300
102 700

Now if the batch process runs in parallel, (out of many) two different sessions insert same group_no; This is because while sesond session inserts a record, first session inserting the same record (group_no) has not yet committed ; So second session Not knowing that already there is same Group_no (101) inserted, again inserts another record with same group_no rather than summing it.

Can it be solved without using temp table, select for update?

View 4 Replies View Related

Reports & Discoverer :: Output Not Appearing In Order When Running In Concurrent Program?

Jan 23, 2013

I have a problem. I have created a report that will list out details of checks using Report Builder. I use XML Publisher to register a template for the report. When running the program through concurrent request, the output will come in pdf format but are not in order.I have add 'Order By' statement in my sql scripts in report builder. Its working fine if test in report builder but not in concurrent program.

View 4 Replies View Related

Server Administration :: Find Maximum Number Of Concurrent Logins Over A Period Of 1 Month

Nov 18, 2011

Need to find out what the maximum number of concurrent logins are over a period of 1 month. We need to do this as we may need to buy more licences.

View 4 Replies View Related

Server Utilities :: Loading Multiple Input Files Into Multiple Tables

Jul 9, 2012

NGFID;RECTYPE;RECNAME
25;7;POLES
PARENT
CHILD;1401;9845075;2020
817;8;SUPPORT
PARENT
CHILD

Required output:-

AREA_SRNO = 1
AREA_NAME = '3rivieres.export.ngf'

File :-mauri.export.ngf

NGFID;RECTYPE;RECNAME
257;7;POLES
PARENT
CHILD;1401;9845075;2020
8174;8;SUPPORT
PARENT
CHILD

Required output:-

AREA_SRNO = 2
AREA_NAME = 'mauri.export.ngf'....etc

CREATE TABLE NGF_REC_LINK
(
AREA_SRNO NUMBER(2),
AREA_NAME VARCHAR2(40),
NGFID NUMBER(20),
TABLENAME VARCHAR2(40),
PARENT VARCHAR2(200),
[code].......

find the ctl file (ngf_test.ctl) and modify the ctl file as per my requirement.

View 6 Replies View Related

Server Administration :: Multiple Database With Multiple Instance On Same Machine?

Mar 16, 2011

can we have multiple database version running in a single machine with multiple instances provided there are enough resources.Can we do in RAC only?

View 15 Replies View Related

SQL & PL/SQL :: How To Insert Data In Multiple Bases Using Multiple Database Links

Jan 2, 2013

how to insert the data in multiple bases( Same table structure in different bases) using the multiple database links?

View 4 Replies View Related

PL/SQL :: Merge Multiple Rows Into Single Row (but Multiple Columns)

Oct 17, 2012

How to merge multiple rows into single row (but multiple columns) efficiently.

For example

IDVal IDDesc IdNum Id_Information_Type Attribute_1 Attribute_2 Attribute_3 Attribute_4 Attribute_5
23 asdc 1 Location USA NM ABQ Four Seasons 87106
23 asdc 1 Stats 2300 91.7 8.2 85432
23 asdc 1 Audit 1996 June 17 1200
65 affc 2 Location USA TX AUS Hilton 92305
65 affc 2 Stats 5510 42.7 46 9999
65 affc 2 Audit 1996 July 172 1100

where different attributes mean different thing for each Information_type. For example for Information_Type=Location

Attribute_1 means Country
Attribute_2 means State and so on.

For example for Information_Type=Stats

Attribute_1 means Population
Attribute_2 means American Ethnicity percentage and so on.

I want to create a view that shows like below:

IDVal IDDesc IDNum Country State City Hotel ZipCode Population American% Other% Area Audit Year AuditMonth Audit Type AuditTime
23 asdc 1 USA NM ABQ FourSeasons 87106 2300 91.7 46 85432 1996 June 17 1200
65 affc 2 USA TX AUS Hilton 92305 5510 42.7 46 9999 1996 July 172 1100

View 1 Replies View Related

SQL & PL/SQL :: Multiple Rows On A Table To Multiple Columns On One Row

Nov 26, 2010

I am attempting to select back multiple values for a specific key on one row. See the example below. I have been able to use the sys_connect_by_path to combine the fields into one field but I am unable to assign them to fields of their own. See the example below

TABLE DETAILS:
Policy id plan name
111 A Plan
111 B Plan
111 Z Plan
112 A Plan
112 Z Plan

My desired result is to be able to show the output as follows

Policy ID Plan_1 Plan_2 Plan_3
111 A Plan B Plan Z PLan
112 A Plan Z PLan

View 6 Replies View Related

PL/SQL :: Multiple Rows To Multiple Column

May 6, 2013

I have a table TableA containing 2 columns ( Name and Value). Here I know what are the values for column Name

TABLEA
=======
Name Parameter
-------------------------
Nexus 11
GPlay 21
Demo 31

I need a query which provides the below output

Desired Output:
======
First Second Third
11 21 31

I have tried the below query
SELECT
DECODE (name,'Nexus', parameter) First,
DECODE (name, 'GPlay', parameter) Second,
DECODE (name, 'Demo', parameter) Third
FROM (SELECT name, parameter FROM TableA where name in ('Nexus','GPlay','Demo'));

This gives me the output

First Second Third
11 <Empty> <empty>
<empty> 21 <empty?>
<empty?> <empty?> 31

Is there any way to get the output in single line.

View 3 Replies View Related

SQL & PL/SQL :: Displaying Multiple Row Values As Multiple Column And Row Values

May 4, 2010

find the Test Case below.

--Creation of Table

create table tb1
(ID number(4),
event varchar2(20),
vdate date);

--Inserting Values into the Table.

INSERT ALL INTO tb1 (ID, event, vdate) VALUES (01, 'V1', '01-JAN-2009')
INTO tb1 (ID, event, vdate) VALUES (01, 'V2', '02-FEB-2009')
INTO tb1 (ID, event, vdate) VALUES (01, 'V3', '04-MAR-2009')
INTO tb1 (ID, event, vdate) VALUES (01, 'V4', '03-APR-2009')
INTO tb1 (ID, event, vdate) VALUES (01, 'V5', '05-MAY-2009')

[Code]...

--Selecting data from Table.

SELECT * FROM TB1;

ID EVENT VDATE
---------- -------------------- ---------
1 V1 01-JAN-09
1 V2 02-FEB-09
1 V3 04-MAR-09
1 V4 03-APR-09
1 V5 05-MAY-09
2 V1 01-JAN-10
2 V2 02-FEB-10
2 V3 04-MAR-10
2 V4 03-APR-10
2 V5 05-MAY-10

10 rows selected.

how can i display the data as below format using Oracle 9i SQL.

IDV1 V2 V3 V4 V5
--- ---------------- ------------ --------------- -------------- ------------
11-Jan-092-Feb-094-Mar-093-Apr-095-May-09
21-Jan-102-Feb-104-Mar-103-Apr-105-May-10

View 4 Replies View Related

SQL & PL/SQL :: Last Value And Multiple Sum

Jun 5, 2010

I have a database which shows the amount of labor done on a part and the columns I am retrieving from the database are as follows:

PART....... WORK_ORDER..... STEP.... OPERATION...... GOOD_PARTS

There are multiple parts in the database, each with multiple work orders, with multiple steps but each step only has 1 operation but each operation has multiple good_part entries.

What I need from this is to sum the sum of all good_parts for the last step of each work order but only if it is a certain operation for each part.

So say i have this data:
PART..... WORK_ORDER..... STEP..... OPERATION.... GOOD_PARTS
XYZ... .... .... 1.. ... .. ... ... 1.. .. .. .. GRIND.. .. . .. . . 50
XYZ... .... .... 1.. ... .. ... ... 1.. .. .. .. POLISH.. .. . .. .. 45
XYZ.. . ... ... .. 2 . . . .. .... .. .1.. .. ... ...GRIND. .. ... ... .. 40
XYZ. . .. .... ... 2 . .. . .. . ... . 2. . .. . ... POLISH .... .... ....45
XYZ... .. .... ... 2. ..... .... ... . 2... .... .... POLISH .. ... ... ... 5
XYZ . ..... ... .. 3.. ... .. .... ... .1. .. .... ... GRIND. .... ... .. . 40
XYZ. ... ... .. .. 3. .. .. .. .. ... . 2. .. ... ... POLISH. .. ... ... .. 45
XYZ. ... .... .. .. 3. .. .. ..... ... 3 . .. .. .. . SHIP. . .. .. .. .. .. 45

(This is the result of a bunch of joins and filters but it is essentially a SELECT * with the clause "ORDER BY PART, WORK_ORDER, STEP" to sort it correctly.)

And I want only the parts for "POLISH" i want to have the final sum of 95 because work order 1 has the last entry of "POLISH" with 45 parts; work order 2 has 2 entries of "POLISH" (which is the last step done) with a sum of 50; but work order 3 has the last operation "SHIP" so I don't include it at all.

I have previously pulled all this data into excel and ran a macro to calculate it but now I find that I need it to be calculated by the server. This database is Oracle, so I can use any functions it supports. I saw the function LAST_VALUE that might be useful.

View 4 Replies View Related

Multiple Alert Log In 10g RAC

Jan 9, 2013

We have Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit RAC in Linux and syslog server also in Linux.

syslog server is a centralize server to monitor all the system and database log.

is there any way to create a multiple alert log file. so that we can keep one alert log file in default location and another alert log file in centralize syslog server for monitoring purpose.

View 3 Replies View Related

Multiple Databases With Same Name?

Nov 23, 2010

We are getting a consultant to upgrade an Oracle 9i installation to 11g R2. The current installation has 6 different databases installed on the same server. Each database is a different customer so for reasons of security we have requested that this be split into 6 virtual machines with one database per machine.

The consultant suggested that they could install the 11g database once and then just make copies (which would all have the same instance name. We are told that the TNS names can be configured so clients are directed to the right database.

View 3 Replies View Related

SQL & PL/SQL :: Next Multiple Of 10 Value For Given Integer

Sep 10, 2013

Is there any function which return the next multiples of 10 value directly ?

Ex : input - 11 then it should return 20
156 - 160
299 - 300
43 - 50

View 18 Replies View Related

SQL & PL/SQL :: How To Sum Over Multiple Tables

Feb 21, 2010

I'm trying to do a sum over 2 different tables but can't get it to work...This is the idea:I have a table A with client ID, time-id (per day), purchase amount and segment code.

In another table (let call it B) I have a lot of client ID's and also their purchase amount, time-id and segment code. I want to sum the purchase amount for every client from table A and B for clients with certain segment code from table B.

This is what I have now:

select client_id, purchase_amountA+ purchase_amountB from tableA, tableB where
A.client_id = B.client_id
and time_id between 20090101 and 20091001
and B.segment_code = 'A'

This does the job, but it selects only client_id's which are in both tables. I want to select all client_id from table B with segment_code 'A' and add the purchase_amount from table A to their purchase amount from table B, at least, if they have any purchase amount in table A.

View 4 Replies View Related

SQL & PL/SQL :: Multiple Rows Into One Row

Apr 10, 2011

tried searching google and this site too, found postings on WM_CONCAT, STRAGG, concat_all, LISTAGG functions by Michel and have experimented with these, but either the syntax is giving me a hard time or i just have not got the concept down.

Trying to get 2 rows into one. Have provided the create statements and insert of data. Also below will show what is returned with a Select i have and what is ideally required.

CREATE TABLE Person_Lang
(
Person_ID NUMBER NOT NULL,
Language_ID NUMBER NOT NULL,
Contact_Name VARCHAR2(255 CHAR),
Main_Phone VARCHAR2(255 CHAR),
Secondary_Phone VARCHAR2(255 CHAR),

[Code]...

Data Returned from Select is:

1 46 905-231-3319 22 Street11 Apt402 Brantford
1 46 905-231-3319 23 Street12 Apt315 MainTown

Ideally what is required back is:

1 46 905-231-3319 22 Street11 Apt402 Brantford 23 Street12 Apt315 MainTown

View 7 Replies View Related

SQL & PL/SQL :: Splitting One Row To Multiple?

Oct 9, 2012

I have a table Product as;
desc product
Name Null Type
--------------------------------------------------------------
PRODUCT_ID NOT NULL NUMBER
INGREDIENT VARCHAR2(20)

The data in Ingredient is separated by ','.
PRODUCT_ID INGREDIENT
---------------------- --------------------
1 A,B,C
2 A,D
3 E,F

I need to write a sql statement which will retrieve a pair of product and ingredient in each row as;

PRODUCT_ID INGREDIENT
---------------------- --------------------
1 A
1 B
1 B
2 A
2 D
3 E
3 F

write this sql ?

View 1 Replies View Related







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