PL/SQL :: Forming Number Type List In A Cursor?
Jul 12, 2013
we use oracle 11g. 11.2. I have a situation where in a pl-sql package, there is a modification needs to be done . Every User is associated with destination codes. For eg, user1==>0101,0104 There is a destination_number and country field in the table1.Each of the destination number is associated with a country code and this combination is unique. for eg:
dest_code country_code 0101 CHINA 0202 UK 0101 Mexico 0104 Mexico
For example, if user1 logs in to the system, then pl sql logic will look if that dest_code is in a table1,If the dest_code is present in the table1, then that dest_code number wont be added to the dest_code list formed for that user. For example,if user1 got dest_codes 0101,0104 associated with him.IF 0101 is in exception table then 0101 and 0104wont be used to form dest_code list. If NOT, a dest_code list will formed like 0101||0104. I am able to form the logic like I have a cursor that loops for each dest_code.For each dest_code , it will get that dest_code into v_dest_code_no number type variable. open cursor fetch dest_code no into v_dest_code v_dummy number:=0;v_dest_code_list number:=0; LOOP v_dest_code_list:=v_dest_code ||v_dest_code ; v_dest_code :=0; END LOOP: How to make sure that for each dest_code set, if one of them is in table1, then the next number also will not be added to the list.
View 1 Replies
ADVERTISEMENT
Dec 14, 2011
Im trying to list the products list of a client grouped by type of the product. Ex:
product type
prod.A acid
prod.B flavour
prod.C acid
prod.D cleaner
prod.E flavour
I want to list something as:
Acid
Prod.A
Prod.C
Cleaner
prod.D
Flavour
prod.B
prod.E
View 1 Replies
View Related
May 21, 2010
how do I count a list of number value eg 1,1,1,1,3,3,6,4 and find the one with maximum number which is 1
View 5 Replies
View Related
May 15, 2013
I've got a requirement for building a 5000 X 5000 matrix using PL/SQL. My original data tables have 5000 rows each and I need to do a correlation analysis using this data and need to store in a physical table and not in-memory. Is this feat achievable using mere PL/SQL? I understand that Oracle DB has a limitation of 1000 columns(but not sure) and hence I'd like to know whether there is any work-around for such scenarios. If not, what are the other alternative method(s) to achieve this feat? Do I need to use any 3rd party tools to get this done?
View 3 Replies
View Related
Aug 21, 2012
I'm currently using Oracle 11.2G and I was wondering if you can declare a dynamic cursor as a strong ref cursor type or can it only be declared as weak type ref cursor? Now, if it can only be declared as a weak type ref cursor, is there any way to avoid having to explicitly declare the type and field names being returned by the cursor? Meaning, is there anyway to avoid have to do something like this...
TYPE example_rec IS RECORD
(
col1 VARCHAR2,
col2 NUMBER,
col3 NUMBER,
etc...
)
Instead, I was wondering if I could just set the fields and their types by setting them as a TABLE OF the ref cursor? The reason I'm wondering if this is possible is because I have a lot of fields being returned in my cursor, and I was hoping on avoiding having to type each one out and its type.
View 12 Replies
View Related
Feb 11, 2013
what is the default return type for weak ref cursor?
View 6 Replies
View Related
Sep 24, 2010
I am select list of ids;
cursor c1 is
select c_id from ctab1 where login = l_id
union
select c_id from ctab2 where login = i_id;
now I need to return err_code and err_message if this combined list is empty ! Can not use exit%notfound.
View 5 Replies
View Related
May 28, 2013
In my environment found maximum open cursor exceeds error. So how can I found the open cursor list and how can I close that cursor without restarting. Any SQL commands to close the open cursor.
View 4 Replies
View Related
Oct 25, 2013
I am getting [Error] PLS-00402 (182: 1): PLS-00402: alias required in SELECT list of cursor to avoid duplicate column names error in my SP.I have created alias for each column and still i am getting the error.
for my_rec_lot in
(SELECT LLP.BOOK_VALUE LLP_BOOK_VALUE,LLP.COMMISSION LLP_COMMISSION,LLP.CURRENCY LLP_CURRENCY,LLP.EXCHANGE_RATE LLP_EXCHANGE_RATE,LLP.EXPENSES LLP_EXPENSES,
[Code].....
View 6 Replies
View Related
Dec 5, 2012
exactly what permissions I need to execute the following SQL statement.
SQL> CREATE OR REPLACE TYPE NUM_ARRAY as table of number;
2 /
CREATE OR REPLACE TYPE NUM_ARRAY as table of number;
*
ERROR at line 1:
ORA-01031: insufficient privileges
When I grant DBA to the ID which runs the SQL it works fine.
View 2 Replies
View Related
Apr 21, 2010
in retrieve column data in the cursor.My requirement is I created a table dynamically as I don't know how many fields will be there.And the table structure would be like this
Filed1 varchar2(10)
Filed2 varchar2(10)
-----------
-----------
Filedn-1 varchar2(10)
Filedn varchar2(10)
[code]...
As in the cur.filed value.
View 1 Replies
View Related
Apr 9, 2012
I need a query to get the below.
Source :
select * from test;
LVL
1
2
3
1
2
3
4
Output:
LVLSEQ
11
21
31
12
22
32
42
I need above to uniquely identify the set of data.
View 25 Replies
View Related
Dec 15, 2011
I have to procedure that computes number of project, and average working hours of employees where employee id is passed as a parameter to the procedure. If the average working hours is less than 10 then employee's salary remain the same, otherwise check if number of project is less than 4 then 5% of salary, else 10% of salary is added to the salary.
my tables are:
CREATE TABLE employee(
empid number(5),
empname varchar(20),
address varchar(20),
no_of_dependents number(5),
deptno number(5),
CONSTRAINT EMPLOYEE_PKEY PRIMARY KEY(empid),
CONSTRAINT EMPLOYEE_FKEY FOREIGN KEY(deptno) REFERENCES department(deptno));
[code]...
the problem is i have to use only 2 cursors - 1 for select and 1 for update.
View 27 Replies
View Related
Aug 18, 2011
I am working on an application that pulls data from an Oracle view into Microsoft Excel (Oracle 11g, MS Excel 2003). It is an automated pivot table created through vba. The problem I'm having is that the decimal points from number columns are being truncated - they don't make it to the pivot table.
When I use the pivot table wizard and set the external data source using a SQL string (select * from view), the precision is not lost. When I create the pivot table automatically in vba, the precision is lost. The pivot table settings, regardless of how the data is brought in, shows the format of the number column as general. This tells me that Excel is trying to figure out what the data type is, and can't (not smart enough).
When looking at the description of the view, the data type is NUMBER (no precision). The table that it is pulling from has the precision set (NUMBER(11,3)). I have tried using the following, but it doesn't work:
select to_number(field_name, '99999999.999') field_name from view
View 7 Replies
View Related
Mar 7, 2010
SQL> select * from year;
YEAR YEARGOAL
1992 5000
1995 5000
1993 5000
1994 5500
[code]...
what would the query be toList the donor number and last name of donors who did not contribute in 1994, in order by donor number.
View 2 Replies
View Related
Apr 1, 2013
I want the query to generate the sequenctial number from 1 for the new financial year in some query.
View 6 Replies
View Related
Sep 7, 2012
I'm trying to use MULTISET UNION to append several collections into a single recordset.The select queries shown for the CURSOR are simple beta versions of the real queries which have about 350 fields to each of them. I will be appending data from 15 of these queries which each will with no more than 20 records per query into a new single recordset (not sure if 'recordset' is the correct phrase or term for what I mean, it could be ARRAY, OBJECT, COLLECTION or something else all together).
Here is text of the ERROR message:
ORA-06550: line 63, column 1:
PLS-00306: wrong number or type of argument in call to 'MULTISET_UNION_ALL'
ORA-06550: line 62, column 1:
PL/SQL: Statement ignored
ORA-06550: line 71, column 36:
PLS-00487: Invalid reference to variable 'VARCHAR2'
ORA-06550: line 71, column 5:
PL/SQL: Statement ignored
I think the error has to do with some defect in my declare statements.
DECLARE
MyTID varchar2(10);
CURSOR Cursor_rst1 IS
SELECT d_owner_internal_id,
d_internal_id,
d_tid,
d_entity_name,
d_form_seq
FROM rtns.itas_rtn_ct_1120_cor tblRecords
WHERE d_form_seq = '2710' --Tax Year =2003
AND D_TID = MyTID;
[code]....
View 3 Replies
View Related
Dec 28, 2010
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 Related
Sep 13, 2012
I am using regexp_substr to break the pipe delimited string. Want to use the output as the NUMBER input to a cursor.Its not working and not getting any error also.
Here is an example.
initial input : '5545|4124|12456'
using the sql below to break the string into columns.
select regexp_substr('5545|4124|12456','[^|]+', 1, level)) from dual
connect by regexp_substr(, '[^|]+', 1, level) is not null;
But when i try to pass the output of the above query to a cursor with input defined as number, its not accepting. I tried using to_number for the output of above query.
View 12 Replies
View Related
Jan 11, 2013
Does the column V$SQL.CHILD_NUMBER related on number of current cursor only ? V$SQL lists statistics on shared SQL areas without the GROUP BY clause and contains one row for each child of the original SQL text enteredI see in V$SQL big numbers in V$SQL.CHILD_NUMBER ( HASH_VALUE and PLAN_ HASH_VALUE is in my quewry fixed ) , am I wrong that this column displayed not summary count of "objects"(in terminology of Steeve. URL....
View 0 Replies
View Related
Jun 28, 2010
Now i have a problem which i am facing for the first time. The problem is:
I have created a table test1 with two column of number data type and the column witdth is 25. Then enter the data but when i select the recrod it does not display the column data as it was entered.
SQL> create table test1
2 (
3 startno number(25),
4 endno number(25)
5 );
Table created.
SQL> insert into test1
[code]...
View 5 Replies
View Related
Apr 19, 2009
I have been trying to construct a query in Oracle that allows me to do the following:
For example if I have the data below:
EmpNo DOB SickDays
Alex 445 15/06/1985 7
Tom 778 22/08/1981 4
James 992 07/10/1978 5
I need to write and a query to lists the employee number and the amount of days sick that they have had and also add a column that compares the number of sick days to the average number of suck days by ALL employees.
I can calculate the average sick days etc, but It wont see to allow me to find the difference between that and the amount of sick days that each person has had. I have tried this many ways and have not been able to come up with a solution.
View 3 Replies
View Related
Jan 30, 2013
we are using database 11g.
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
i am getting one error while selecting the data from table.
SELECT interco_type, entity, chapter_entity, tran_currency, source_id_entity,
tran_id_entity, mirror_id, gaap_type, counterpart
FROM t_gri_reconid_dtl_agg_gcr;
or
select * from t_gri_reconid_dtl_agg_gcr where rownum = 1 ;
Error:- ORA-01795: maximum number of expression in a list is 1000 ( error is same in both the case )
View 13 Replies
View Related
Oct 4, 2008
Create a table with 100 records.Then write a BULK COLLECT Cursor that can reduce the number of context switches by using a bulk fetch to query records in sets or all at once.
View 1 Replies
View Related
Sep 18, 2009
When i'm sorting on a calculated field i'm getting the following error:
ORDER BY item must be the number of a SELECT-list expression
View 3 Replies
View Related
Dec 27, 2012
Ive a requirement which ive overlooked for a while now as below,
On my tabular form i have fields: Empid as Select List and when the value is picked from this Select List EmpID i want the associated/corresponding phone# displayed in its TEXT FIELD in that same row,
apex 4.1.1/Oracle 11gR2,
also i tried the below as a workaround but it doesnt fulfill my requirement, [URL]....
View 1 Replies
View Related
Jan 2, 2013
I have 3 user defined collection types. I am trying to access the type3's attribute in type1 body (like backward accessing).
Ex. My Collection Types Structure (something like master detail structure)
create type type1 as object
(
attr1 varchar2(10),
attr2 varchar2(10),
member procedure function1
[code]...
so, in the type1 body i have to get or assign the value either to type2's attribute or type3's attribute. I have search all the in internet but i haven't find anything such. how to find the reverse method of accessing the super type's attribute(s) in sub type's body.
View 3 Replies
View Related
Oct 17, 2011
Is there some functions to convert the long type field data to varchar2 type?
View 2 Replies
View Related
Mar 8, 2013
I have created the below types and oracle objects.
create or replace type T_EMA_NP_SETDEL_RESP_REC as object
(
respCode number,
respDesc varchar2(255)
)
create or replace type T_EMA_NP_RANGE_LNPTICKET_TAB AS TABLE OF T_EMA_NP_RANGE_LNPTICKET_REC
create or replace type T_RANGE_TICKET_TAB AS TABLE OF T_RANGE_TICKET_REC
The following types are created in the Package specification
type t_resp_rec IS RECORD
(
resp_code number,
resp_desc varchar2(255)
);
--
subtype t_ema_lnpticket is T186_IN_REQ_PARAMETER.T186_EMA_LNPTICKET%TYPE; -- Number
type t_ema_lnpticket_tab is table of t_ema_lnpticket index by binary_integer;
I have the following two procedures
PROCEDURE getEMAReturnResponse(
p_in_call_request_id IN number,
p_ema_resp_rec IN t_ema_np_setdel_resp_rec,
p_ema_range_lnpticket_tab IN t_ema_np_range_lnpticket_tab,
p_endof_event IN varchar)
PROCEDURE Return_Response(p_in_call_request_id IN number,
p_ema_resp_rec IN t_ema_resp_rec,
p_ema_lnpticket_tab IN t_ema_lnpticket_tab,
p_endof_event IN varchar2)
getEMAReturnResponse Procedure:
Accessed by Java application to pass the values. Should call the Return_Response procedure and pass the values received from Java.
Return_Response Procedure
The p_ema_lnpticket_tab is a sort of array that can have multiple values. Please see the example of values. Has all the business rules and validation that should be adhered.
Example of Vaules
p_in_call_request_id = 1
p_ema_resp_rec = 12345, 'Operation Failed'
p_ema_lnpticket_tab = (1,2,4,5)
p_endof_event = Y
View 2 Replies
View Related
Feb 1, 2012
i am trying to run a script in which a command tries to create or replace a type.
i get this error:
ORA-02303: cannot drop or replace a type with type or table dependents
SQL>
SQL> --create a test user:
SQL>
SQL> create user tuser identified by tuser
[Code]....
Table created.
SQL>
SQL> --then change the type:
SQL>
SQL> create or replace type t1 as object (obj_type number(15))
2 /
create or replace type t1 as object (obj_type number(15))
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or table dependents
SQL>
SQL> --if i'll do FORCE action on the type - it'll corrupt my depandant table:
SQL>
SQL> drop type t1 FORCE
2 /
Type dropped.
SQL>
SQL>
SQL>
SQL>
SQL> desc dpntnt_table
Name Null? Type
----------------------------------------- -------- ----------------------------
ID1 NUMBER(7)
SQL>
SQL>
SQL>
SQL> --if i re-create it - my table is still corrupted:
SQL>
SQL>
SQL> create or replace type t1 as object (obj_type number(15))
2 /
Type created.
SQL>
SQL>
SQL>
SQL> desc dpntnt_table
Name Null? Type
----------------------------------------- -------- ----------------------------
ID1 NUMBER(7)
SQL>
--if i re-create it - my table is still corrupted:
create or replace type t1 as object (obj_type number(15))
/
desc dpntnt_table
[/code]
1. If i'll do drop type FORCE what will happen to the dependent object(might be a table for example) ?
2. I understand that this type is already assigned to some object, but i can't seem to find out which one.
how do i find out which object is depending on the type i want to create or replace?
View 4 Replies
View Related