I am getting the below output when I tried to take the "Payables" RDA from command-line (Application RDA is failing to collect) I am getting the below output.
appldev@devpi:/home/appldev>cd $IZU_TOP/bin
appldev@devpi:/d02/appldev/apps/apps_st/appl/izu/12.0.0/bin>./rda.sh -vdCRP -e APPL_SHORT='SQLAP' ACT
Collecting diagnostic data ...
No module requiring data collection
Generating the reports ...
No pending report
[code]....
why it is saying that "No module requiring data collection"?
REQUEST ---------------------- request_id item_name unit_of_measure UOM_CODES_TL ("tl" means translation - this table has the same record 3 times, once for English, once for Spanish, and one for France) ---------------------- unit_of_measure description language [code]....
So what I'm trying to do with the outer join ((+)) is to get the request record, regardless of whether the r.unit_of_measure is null. That works as long as I don't include the underlined portion above. As soon as I do that it only returns results where r.unit_of_measure is not null. That all makes sense to me, but I'm wondering how I can restrict the values to English without requiring that a value exist in the request table.
1) Split values from "INST" Column : suppose 23 2) Find all values from "NUM" column for above splitted value i.e 23 ,
Eg:
For Inst : 23 , It's corresponding "NUM" values are : 1234,1298
3) Save these values into
A table Y : INST, NUM are column names.
INST NUM 23 1234,1298
1) I have a thousand records in Table X , and for all of those records i need to split and save data into Table Y.Hence, I need to do this task with best possible performance.
2) After this whenever a new data comes in Table X, above 'split & save' operation should automatically be called and append corresponding data wherever possible..
While reading data from collection variable using ref cursor . I am getting the below two errors.
PLS-00382:Expression is of wrong type ORA-22905 Cannot access rows from a non-nested table item.
CREATE OR REPLACE PACKAGE APPS_GLOBAL.GIIOMEGAORDERLIST AS TYPE BU_LIST_TYPE IS TABLE OF VARCHAR(50); TYPE OFFER_DETAIL IS RECORD ( GII_BU VARCHAR(50), GII_OFFER NUMBER, [code]........
Elapsed Time (s) CPU Time (s) Executions Elap per Exec (s) % Total DB Time SQL Id SQL Module SQL Text 2,423 1 3,919 0.62 1.83 gt49gg0fnc5x8 srv_dr@ahs (TNS V1-V3) UPDATE /*+ CCL<OENDB_FILE... 2,227 14 1 2227.16 1.68 bggfx8a04prj9 SQL*Plus select * from (select n.source... .........
On [SQL ordered by Elapsed Time], [SQL Module] shows an indication that a SQL was executed by which process (i.e. srv_dr@ahs)outside of SQL*PLUS.If [SQL Modeule] shows as [SQL*Plus], does it mean the query was run in SQL*PLUS manually or directly?I have the SQL ID. How do I find out who, how, and exactly what time it was run?
I created one collection report and in the report when i click on the next >> then the report will show the next columns to enter data in some columns but the data which i entered before going to next will not get retain (data is getting lost) when i come back by clicking << previous but i want the data to get retained even when i click on next >> or previous << in report so that i can enter large amount of data at a time in the report columns by clicking >> & << and click on submit button to save all the data.
My customer is using latest SES11g with WCC11g, the WCC11g has ACL security module, in SES document, it mentioned only security group and account module in WCC is supported when using SES, but somebody tested SES did work with WCC with ACL security module, is there any document in SES mentioned this is a supported feature?
Recently, i had to restore /usr/lib folder (because some files crashes) to a previous point. This restore point is previous to Oracle client installation. Well, now, when i try to compile my Pro*C samples:
There is a nested table with in a nested table type and i want to print the value and again assign a new value to the next subscript and i have tried a lot but couldn't find any solution.
declare type type_name is table of varchar2(10); type type_name1 is table of type_name; names type_name1:=type_name1(type_name('hello')); begin -----HOW TO PRINT A VALUE-------- -----HOW TO ASSIGN A NEW VALUE TO NEW SUBSCRIPT null; end;
1) need to print the values of names(1) 2)Assign a value to names(2)
I have been doing some code in collection for testing. I have been doing the below but getting the error.
SQL> desc t_mine; Name Null? Type ----------------------------------------------------- -------- OWNER NOT NULL VARCHAR2(30) OBJECT_NAME NOT NULL VARCHAR2(30)
SQL> ed Wrote file afiedt.buf
1 declare 2 v_start_time PLS_INTEGER := DBMS_UTILITY.GET_TIME; 3 v_elapsed PLS_INTEGER; 4 type allobjects_record is record 5 (owner varchar2(1000) [code].......
ERROR at line 15:
ORA-06550: line 15, column 15: PL/SQL: ORA-00947: not enough values ORA-06550: line 15, column 3: PL/SQL: SQL Statement ignored
Is there any table except (global temp table and permanent table) which can be used to store data and be used in inner, left and right join for a session.
remove duplicates from my collection(table type).Or an alternate solution for my issue is get clean data into my collection which i am not able to get to either.
Object creation
create table testingtype_table(ordernumber number,org_id number , company_name varchar2(10)) insert into testingtype_table values (1124,2424,'cbaaa'); insert into testingtype_table values (1124,2424,'cbaaa');
create or replace type testingtype_obj as object (ordernumber number,org_id number , company_name varchar2(10)); create or replace type testingtype_tab as table of testingtype_obj;
Code Block declare l_testingtype_tab testingtype_tab := testingtype_tab(); begin select distinct testingtype_obj(ordernumber ,org_id ,company_name) bulk collect into l_testingtype_tab from testingtype_table; end;
If only i can get a way to bulk collect only distinct values into the table type that will just do great but when i try the above (with distinct highlighted in red) it throws an error
ORA-22950: cannot ORDER objects without MAP or ORDER method
The following procedure stores the retrieved data in o_ref_primary_dept collection variable, suppose I want to display the data from that variable while executing what code I have to write.
CREATE OR REPLACE PROCEDURE sp_ost( o_ref_primary_dept OUT PRIMARY_DEPT) IS l_primary_dept LONG; [code].....
how to use collection variables as OUT parameters in procedure.
CREATE OR REPLACE PACKAGE BODY pkg_das_stag_to_master AS PROCEDURE sp_load_dasstage_security AS cursor cur_stag is select Asset_id, ID_ISIN, ID_SEDOL1, ID_CUSIP, [code].........
error:
66/35 PLS-00302: component 'ASSET_ID' must be declared 66/35 PLS-00302: component 'ASSET_ID' must be declared 66/18 PL/SQL: ORA-00904: "TYP_SECURITY_VAR"."ASSET_ID": invalid identifier 63/1 PL/SQL: SQL Statement ignored
I have an existing Procedure that uses a ref cursor as a parameter. The procedure currently simply selects data into the ref cursor using an OPEN FOR and then exits.
I have a need to extend the processing of the procedure. This will mean retrieving extra columns in the select and also removing some of the retrieved records.
I have the constraint that the definition of the REF CURSOR (which is based on a record type as follows) cannot be modified.
TYPE t_charge IS RECORD ( id number, date_from invoice_detail.from_date%TYPE, date_to invoice_detail.to_date%TYPE, description invoice_detail.desc_text%TYPE, amount invoice_detail.amount%TYPE ); TYPE t_charge_cursor IS REF CURSOR RETURN t_charge;
The procedure does this
PROCEDURE get_bill_lines ( p_bill_id IN NUMBER, bill_lines_list OUT t_charge_cursor ) IS OPEN bill_lines_list FOR SELECT id, from_date, to_date, desc_text, amount from invoice_table where id = p_bill_id;
I am thinking instead of selecting directly into the bill_lines_list parameter I will use a local REF CURSOR to select into a COLLECTION. I can then do some work on the collection, similar to below:
PROCEDURE get_bill_lines ( p_bill_id IN NUMBER, bill_lines_list OUT t_charge_cursor ) TYPE t_local_charge IS RECORD (
[code].....
This all works fine, but the question is, how do I then put the collection into the bill_lines_list REF CURSOR where the '*** HERE ***' comment is so I can send the existing REF CURSOR structure back? (Remember I cannot change any definition that is referenced externally)
Declare Cursor c1...; Cursor c2...; begin open c1 ; fecth c1 bulk collect into v1; close c1;
[Code]...
Is there any way by which if condition gets true then v1 gets appended rather than being overwritten?
declare type lst_deptno is table of dept.deptno%type index by binary_integer; type lst_deptno_emp is table of emp.deptno%type index by binary_integer; v_deptno lst_deptno; v_deptno_emp lst_deptno_emp; cursor c1 is select deptno from dept;
I need to check if a small collection contains a specific value. I know I can use the .exist method to let me know if the nth element exists. But is there a similar syntax for checking if an actual value exists?
So the below collection has 6 values (300,301,310,320,400,410) after the cursor values are fetched. I want to use something similar to the below exists syntax in order to search the collection for a value (rather than position) within an IF statement.
The below code shows the kind of thing I want to do. Currently, unless my test value (310) is in the range 1 to 6 the test will always return FALSE. But is there a similar syntax for testing against the value?
The example I have given is a simplification of what I need. In reality there will be more than one test value... returned by another cursor.
DECLARE CURSOR c_type_id_usg IS
[Code]....
-- get the list of sms type id usg values OPEN c_type_id_usg; FETCH c_type_id_usg bulk collect into l_type_ids; CLOSE c_type_id_usg;
-- the above returns value 300,301,310,320,400,410
IF l_type_ids.exists(310) then dbms_output.put_line('I have found an entry '); else dbms_output.put_line('I have NOT found an entry ' ); end if; END;
I'm working on a plsql program and i'm using collections. I loop the collection and delete rows of it depending on the edits of my program. Here is the question.
if my collection holds rows [1]value [2]value [3]value
i can simply do something like FOR indx in invoice.first..invoice.lasthowever if i delete row 2 of my collection i get an error. no data found. ive been researching this site
[URL].......
rows [1]value [3]value [4]value
is there a way to tell plsql i just want it to loop the collection from top to bottom regardless of the index values?
The following procedure stores the retrieved data in o_ref_primary_dept collection variable,suppose I want to display the data from that variable while executing what code I have to write.
CREATE OR REPLACE PROCEDURE sp_ost( o_ref_primary_dept OUT PRIMARY_DEPT) IS l_primary_dept LONG; BEGIN l_primary_dept := 'SELECT emp_obj(empno,ename)' || ' FROM emp'; EXECUTE IMMEDIATE l_primary_dept BULK COLLECT INTO o_ref_primary_dept; END;
how to use collection variables as OUT parameters in procedure.
In PL/Sql, I create a new collection using TYPE. Then populate it with BULK COLLECT.The next step I need is to load data from the collection to pass as WHERE condition to a ref cursor.How I can do this?Every test I did fais. It seems to me that I cannot use some kind of SELECT to retrieve all data from the collection, but I might use a cursor to read row by row. (I'm on Oracle 11gR2 DB )
while doing stats collection weather system takes the backup of current statistics. i think we can specify stattab. but weather it takes stats backup before over writing? I got this requirement as a part of upgrade, i have already gone through export_schema_stats and import_schema stats already. Just trying all other possible options only.
I have the following questions you can create a bulk collect in which he has 3 fields and one of them is a type collect failing index or other bulk collect?.If so, how would the procedure for when to insert the first record sub fill the bulk collect. being for example something like this:
----------------------------------------------------------------- index | codigo | nombre | telefono | | |----------------------- | index | telefono ----------------------------------------------------------------- | | | |
I have following query: type EMP_REC is record ( id number, name varchar2(20), dept number );
type EMP_TBL IS TABLE OF EMP_REC INDEX BY BINARY_INTIGER; EMP_TABL1 EMP_TBL;
select * BULK COLLECT into EMP_TBL1 FROM emp;
How to remove duplicate records from EMP_TBL1 collection if exists.I don't want to remove duplicate records from main table. But actually want to remove duplicate records from EMP_TBL1 collection if exists.
we had an issue with our 10.2.0.4.0 two node RAC Database where when running stats collection caused the database performance to go down and in AWR I noticed the following:
Top 5 Timed Events Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class gc buffer busy 95,077 73,786 776 76.0 Cluster cursor: pin S wait on X 3,524,808 54,467 15 56.1 Concurrency library cache lock 30,223 13,660 452 14.1 Concurrency gc cr request 2,876 3,594 1,250 3.7 Cluster library cache pin 1,740 1,800 1,035 1.9 Concurrency
when looked under Library Cache Activity section in the AWR report, I see the following:
Namespace Get Requests Pct Miss Pin Requests Pct Miss Reloads Invali- dations BODY 59 3.39 3,056 0.13 1 0 CLUSTER 70 0.00 73 0.00 0 0 INDEX 178 0.00 925 0.00 0 0 SQL AREA 423,064 50.25 2,465,209 2.37 182 119,671 TABLE/PROCEDURE 1,848 21.27 22,638 0.62 70 0 TRIGGER 14 0.00 1,801 0.06 1 0
does this mean, we are having memory crunch for this instance on node 1? we have sga_target=26gb and sga_max_size=32gb set for this instance.
