We have a package that processes(Insert and Update) set of records and runs in loop.
Package is working fine when we set number of records to 300 for each loop and commit. when we increase the number of records to 1000, package is getting stuck(Not ending and not coming out of loop).
Is there any DB parameter to be changed in my environment to process more number of records at at time?
I have a requirement to extract data from an XML formatted string that is passed as an IN parameter to a database stored procedure. I do not have a table to store the XML, nor an XML schema, nor a namespace, just the XML string. While new to XML, I do understand the basics. Using XMLTable I am able to parse out the simplest of XML data. My problem is this; attempting to extract data from nested XML nodes results in the error
'ORA-19279: XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence'.
Testing a solution example I found on this site by Barbara Boehmer, I still get the ORA-19279 error. I suspect it may be because I have version 10g while she has version 11g. Her solutions is from #msg_514154. Here is that code that gives me the error:
SCOTT@orcl_11gR2> SELECT x."deptno"
2 FROM (SELECT '<departments><deptno>1</deptno><deptno>2</deptno></departments>' col FROM dual) xml_data,
3 XMLTABLE
4 ('/departments/deptno'
5 PASSING XMLTYPE (xml_data.col)
6 COLUMNS
7 "deptno" number path '/deptno') x
8 /
deptno
----------
1
2
2 rows selected.
As I said, this example generates the ORA-19279 error. Need processing XML data from an IN parameter?
I have written the following PL/SQL procedure to delete the records and count the number of records has been deleted.
CREATE OR REPLACE PROCEDURE Del_emp IS
del_records NUMBER:=0;
BEGIN
DELETE
FROM candidate c
WHERE empid in
(select c.empid
from employee e,
candidate c
where e.empid = c.empid
and e.emp_stat = 'TERMINATED'
);
[code]....
is there any view in oracle for getting the total number of processes and currently used process in oracle? i am using oracle 11.2.0.1.0.
View 3 Replies View RelatedOracle 11g R2 on WIndows 20o8 R2 Standard edition
I have a test server that keeps running into ORA-00020: maximum number of processes error each night. Our current setting is '600' processes to be allowed. There are no users to this database except myself. No front end app connects to it either.
I found that ORACLE.EXE (SHAD) process coming from SYSMAN user is the one eating up all the processes and eventually causing that error.
From v$session, the program is OMS.
From v$process, the hostname is the server itself.
I narrowed it down to restarting the ConsoleService, if i do that, then the number of processes drops down. So now im not sure why something within the ConsoleService is logging in, taking a process and not releasing it...What i can check?
I have a procedure. Here is the spec
Procedure countyname(i_lat IN Number, i_lon IN NUMBER, o_countyname OUT VARCHAR2);
The procedure works fine when the input parameter values are small precision like 30.653, -618.765 etc.it fails if the Input parameter values have more precise like 35.694872140886...I think the IN Number can only take upto certain precission.
Is there any way I can specify the precision for a NUMBER input parameter in a procedure?
ex: Procedure countyname(i_lat IN Number(30,10), i_lon IN NUMBER(30,10), o_countyname OUT VARCHAR2);
when I tried the above statement it doesn't compile it gives PLS-00103 error.
I am getting above error when i ma starting my rac data guard on linux. i specify instance number but still its asking
this is my pfile
timsdr1.__db_cache_size=4345298944
timsdr2.__db_cache_size=4395630592
timsdr2.__java_pool_size=33554432
timsdr1.__java_pool_size=33554432
[code]...
I am trying to create a report parameter using an lov. You be able to pick one item or return All
The source for my lov is
SELECT l.extkey||' - '||l.longdescr a, l.clocid b
from udm_cloc l, udm_cust c, udm_lde lde
where l.custid = c.custid
and c.ldeid = lde.ldeid
[code].......
But I keep getting an error
'LOV query is invalid, a display and a return value are needed, the column names need to be different. If your query contains an in-line query, the first FROM clause in the SQL statement must not belong to the in-line query.
In my application (forms6i) initially data will be displayed in a non-database block after a lot of calculations and validations which is time consuming.
Now, if the user want to print it as a report, we store the displayed data in a dummy table and run the report using that dummy table and then delete records from it.
Is it possible to pass the records displayed to the report as a parameter so that I can avoid Unnecessary add/delete records?
I need an example of oracle stored procedure, which should xmltype input parameter. xml type parameter contains multiple records and I need to update them in the database.
View 2 Replies View RelatedI have one stored proc with three in parameters as
number, varchar and table of number
what statement I need to write in pl/sql to execute it ...
execute getdetails(1,'xyz', ????????????)
In Report it is possible to set the maximum rows to fetch to be a number e.g. 1+.Is it possible to use a number that is selected from another query? When I try to do this i.e. I put in the field num_lines or :num_lines I just get invalid number
View 10 Replies View RelatedI would like to know if using varchar parameter in sql queries with number column can result in performance degrade.
Ex: Procedure testa ( myparam varchar) is
begin
select col1 into var1 from table where colno = myparam;
end;
Here col no is a number column and myparam is varchar. I feel its better to change the parameter to number.
I have two sql queries. They run the one after another.
Query 1:
select * from capital
where member_status = 'MEMBER' AND rownum <= 25
order by price desc
Query 2:
select * from capital
where member_status = 'MEMBER' AND rownum > 26
order by price desc
Question is, in the query 2 I want records greater than row number 25. In query 2, I don't want the records that were fetched in Query 1. Is there any way to do this without using rownum?
I have the following requirement.
Data from table 1 is printed first and after every X number of rows another set of data either from the same table or a different table needs to be printed.
I know how to use cursors to read all the records in a table.
But how can records that should be processed together be read in a loop, using cursors?
If there were, for instance persons from different nationalities, French, German, English, ..., I woluld like to read the first unknown number of persons from France and process that data, and then continue with the unknown number of germans and process that, and so on.
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.
is there any way other than this way to retrieve the number of record displayed in the block ?
last record ;
l := :system.cursor_record
In form i call a record from database table through execute_query. i have lov on ITEM_NO
i want to display on that form, no. of records of ITEM_NO which i select from the list. like if i have 10 records with item_no which i select from list is must show 1/10.
if i press the down arrow it should show next record of the same item_no.
If I run the following query I got 997 records by using GROUP BY.
SELECT c.ins_no, b.pd_date,a.project_id,
a.tech_no
FROM mis.tranche_balance a,
FMSRPT.fund_reporting_period b,
ods.proj_info_lookup c,
ods.institution d
WHERE a.su_date = b.pd_date
AND a.project_id = c.project_id
AND c.ins_no = d.ins_no
AND d.sif_code LIKE 'P%'
AND d.sif_code <> 'P-DA'
AND a.date_stamp >='01-JAN-2011'
AND pd_date='31-MAR-2011'
GROUP BY c.ins_no,
b.pd_date,
a.project_id,
a.tech_no;
I want to show the extra columns a.date_stamp and a.su_date in the out put so that I have used PARTITION BY in the second query but I got 1079 records.
SELECT c.ins_no, b.pd_date,a.date_stamp,a.su_date, a.project_id,
a.tech_no,
COUNT(*) OVER(PARTITION BY c.ins_no,
b.pd_date,
a.project_id,
a.tech_no)c
[code]....
why I got 1079 records.how to show the two extra columns in the out put whcich are not used in GROUP BY clause.
MY problem is in my reports i wish to have filter number of records on my requirement , when i call reports from form then ask me that how many number of records you want..
View 3 Replies View RelatedI got Scenario that, i need to insert the number of records loaded in target table into the log table.
But the resultset is handled in the in the cursor. how to get the number of records the cursor handles.
/* Formatted on 08/03/2013 15:00:44 (QP5 v5.149.1003.31008) */
CREATE OR REPLACE PROCEDURE DASHBOARD75.SP_STG_MLY_GL_HKP_V1_00
AS
CURSOR GL_HKP
IS
SELECT CAL.MTH_NM,
CAL.YEAR,
GLM.BU_CD,
[code].......
Table : Customer
Cust_idNameid
227ABC1
227ABC2
227ABC3
228XYZ1
228XYZ2
228XYZ3
228XYZ4
In one oracle table (Customer) we have multiple cust_id based on that, we want to populate id column as cust_id record count.
I have id column right now null and want result same as above.
How to create procedure for getting number of records of a table
View 24 Replies View RelatedI need to verify huge number of records in two different databases. Basically i wanted to check if same record exist in other database's table or not? but as the number of records are more than billions who would i verify?
checking record one by one would be so hectic and time consuming. any other option is there?
Updated to Add: In a last ditch search, I found my answer with ROWNUM <= 1 in the Where clause. It works and I can go from there with what I want to do.
I have a website that pulls similar information from multiple queries using a Union-based query. I want to only pull 1 record from one section, two from another, and 5 from the third. I've so far found LIMIT but haven't been able to get it to work in that way. Is it possible to limit each query in the union as I am looking to do?
The query is:
SELECTc.priority, c.startDate, p.headline, p.newsID, p.kicker, p.webPath, p.makePopup, p.thumbnail, p.shortDesc, p.storyType, d.filePath
FROM (so_news p LEFT OUTER JOIN so_news_deptLevel c ON p.newsID = c.newsID) LEFT OUTER JOIN so_departments d ON d.deptID = c.deptID
WHERE p.storyType = 'alert' AND c.display = 'yes' AND c.startDate <= sysDate AND c.endDate >= sysDate
UNION All
SELECTc.priority, c.startDate, p.headline, p.newsID, p.kicker, p.webPath, p.makePopup, p.thumbnail, p.shortDesc, p.storyType, d.filePath
[code]....
I have created a function that is used for splitting a comma separated string & give the output in tabular form.here is the function
Here I have used CLOB as my input string will be huge(greater than max limit of varchar2)
CREATE OR REPLACE TYPE SPLIT_TBL_CLOB AS TABLE OF CLOB;
CREATE OR REPLACE FUNCTION CSVTOSTRING_CLOB
(
P_LIST CLOB,
P_DEL VARCHAR2 := ','
) RETURN SPLIT_TBL_CLOB PIPELINED
[code]....
But here I am facing 2 problems.
1. The function is not accepting a large string & I am getting the error
ORA-01704: string literal too long
2. The function is going for an infinite loop.
We are building a custom form extension with our EBS R12 where against certain parameters we are inserting and updating records in a table through a PL/SQL procedure.This PL/SQL doesn't use a commit exclusively, instead the user must click the Save button after verifying the data (new records are inserted, existing ones are updated and after the insert and update routines, execute_query is fired to fetch the latest data)
Everything works fine, however, when the user clicks on the Save button form status bar says "FRM-40401: No changes to save"
We would like to alter this message with our own custom message stating how many records were inserted and hows many were updated (We have counters counting this information)
Under which particular trigger we should write the code to enhance this requirement?
Actually m working on oracle 10g forms. I have a list item in which no. of records displayed is 10. I am populating this list item through a record group. I want when user select a value from first record then he should not be able to select the same value in further record of that list item.
View 12 Replies View RelatedI am currently on a project which has a requirement of changing the number of records dynamically. Oracle Forms version 10g.
View 2 Replies View Related