PL/SQL :: Get Multiple Records In FUNCTIONS?
Aug 16, 2012
I have created the following function in oracle database 10g then I used it in Oracle reports 6 . It is working fine for single row(one value). But when there is multiple rows then it show error .
Note:ot.po_code is number datatype and clr_name is varchar2 data type.
this is the script
create or replace FUNCTION get_colour_name (POC number)
RETURN VARCHAR2 IS
CLR_NAME VARCHAR2(100);
BEGIN
SELECT DISTINCT BC.COLOUR_NAME INTO CLR_NAME
[Code].....
View 4 Replies
ADVERTISEMENT
Apr 16, 2013
I'm trying to create multiple functions, add them then minus 1 in a package. I'm at my wits end as to what I'm not doing correctly as the package gets created fine but the package body is giving me compilation errors.
create or replace package addfunction as
function q1 (num1 IN NUMBER, num2 IN NUMBER) return number;
function q1 (num1 IN NUMBER, num2 IN NUMBER, num3 IN NUMBER) return number;
END;
/
[code].....
Warning: Package body created with compilation errors
View 4 Replies
View Related
Jun 15, 2013
I have 2 tables order_items and items.
Order_items Items
Item_id Item_id
Quantity Price
In normal sql statement: select sum(order_items.quantity*items.price) sales_price
from order_items,items
where order.item_id=items.item_id;
I have to put this logic in either a stored procedure or Function just to calculate sum(order_items.quantity*items.price) and store the aggregated value as Sales_price in DB. Then we have to call this from Informatica Stored procedure Transformation where we will have only one output port as Sales_price,this is to load into summary table. All the aggregate calculations and joining of 2 tables should be done on DB side and only one output should be populated when we execute this procedure.
View 2 Replies
View Related
Sep 18, 2013
I have a table as col_1 number,col_2 varchar2(10)
col_1 col_2
1 abcdefghijkl
what i want is i've to split
col_1 col_2
1 abcdefghij
1 kl
View 4 Replies
View Related
May 6, 2010
I have a table formatted like this
ID|First Name|Second Name
1|X|X
2|X|X
3|X|X
4|X|X
and I want to insert First Name and Second Name records using the ID as the reference. The new data is currently in a .csv file, but I could put them in another table if that's easier.
ID|First Name|Second Name
1|Adam|Adamson
2|Ben|Benson
3|Chris|Christophers
4|Dave|Davidson
I understand how to do individual updates, I want to know how it is possible for me to do this as one query, as I have several thousand records to update.
View 14 Replies
View Related
Jun 18, 2013
I have a small requirement...
Create table temp_a (source_code varchar2(100), target_code varchar2(1000));
Insert into temp_a values ('1','002.0 AND 002.9');
Insert into temp_a values ('2','729.90 AND 079.99 AND 002.9');
Output :
1 002.0
1 002.9
2 729.90
2 079.99
2 002.9
So, once we get the output, it needs to be joined to another table. I did Google search, but most of them are retuning collections / arrays as output. Not sure how I join the collection with the table.
create or replace function splits
(
p_list varchar2,
p_del varchar2
) return split_tbl pipelined
is
l_idx pls_integer;
[code].......
View 3 Replies
View Related
Jul 30, 2010
I'm looking to create a STORED PROCEDURE to do the following per v1...but I seem to misunderstanding some ORACLE concept(s) as when called in a PLSQL window I'm getting an ORA-01422 message when using the V2 syntax.
Ideally I want this code called from VB, but for the moment I'm trying to get it to work when called from the PLSQL Developer toolkit. Am I trying the wrong concept? per previous posts my background is MSSQL, but I've used Oracle SQL from approx 2 years (at a SQL level) only.
v1
create procedure myabc
as
begin
SELECT custNAME
FROM MYCUSTOMER
[code]....
View 7 Replies
View Related
Jul 8, 2013
I want to insert multiple records in a single row. Example: I have a below query
select '"'||c1||'","'||c2||'","'||c3||'"'from (select 'ABC' as C1,'ZYX' as C2,'TEST' AS c3from dual unionselect 'A1' as C1,'a2' as C2,'A3' AS c3from dual)
And I want to insert the above 2 records in table T1 as a single row as below:
"A1","a2","A3""ABC","ZYX","TEST" Column1"A1","a2","A3""ABC","ZYX","TEST"
View 6 Replies
View Related
May 17, 2011
My query is: I have created a master-child forms on one canvas. In a child form i have to enter multiple records, but after entering every record its prompting me to save it. I want to avoid it and save the entire thing once all my records are entered.
View 4 Replies
View Related
Jul 18, 2013
Insert multiple record in table. I have a table of customers . It has column cus_name, cus_fruit, cus_date, cus_qty.
Select * from customers;
cus_name cus_fruit cus_date cus_qty
Maria Anders Apple 18-July-2013 10
Maria Anders Apricot 18-July-2013 20
Maria Anders Asparagus 18-July-2013 100
Maria Anders Avocado 18-July-2013 5
Ana Trujillo Apple 18-July-2013 10
Ana Trujillo Apricot 18-July-2013 20
Ana Trujillo Asparagus 18-July-2013 100
Ana Trujillo Avocado 18-July-2013 5
how I can insert record in one time in table. All table data same only change the cus_name.
Thomas Hardy Apple 18-July-2013 10
Thomas Hardy Apricot 18-July-2013 20
Thomas Hardy Asparagus 18-July-2013 100
Thomas Hardy Avocado 18-July-2013 5
After Insert record result.
Select * from customers;
cus_name cus_fruit cus_date cus_qty
Maria Anders Apple 18-July-2013 10
Maria Anders Apricot 18-July-2013 20
Maria Anders Asparagus 18-July-2013 100
Maria Anders Avocado 18-July-2013 5
Ana Trujillo Apple 18-July-2013 10
Ana Trujillo Apricot 18-July-2013 20
Ana Trujillo Asparagus 18-July-2013 100
Ana Trujillo Avocado 18-July-2013 5
Thomas Hardy Apple 18-July-2013 10
Thomas Hardy Apricot 18-July-2013 20
Thomas Hardy Asparagus 18-July-2013 100
Thomas Hardy Avocado 18-July-2013 5
View 9 Replies
View Related
Feb 23, 2012
I'd want to select multiple records in my multi-record block. I'd also want to do that with checkboxes. When the user clicks on a particular checkbox, that should be selected and whatever record the user wants to check it should be added to selected records.
View 39 Replies
View Related
Jun 15, 2012
I have Leave Balances In such Format
EMP_COD CategoryJON_DTE LEV_BAL
3900000385101/02/200760
3900002678107/01/200840
Now I want to Separate it into Three and Two Partitions Year Wise.
Like this
3900000385101/02/200720 2012
3900000385101/02/200720 2011
3900000385101/02/200720 2010
3900002678107/01/200820 2012
3900002678107/01/200820 2011
View 7 Replies
View Related
Jun 3, 2013
how to save multiple records at once i did coding like this:
declare
cnt number;
l_rec number;
f_rec number;
begin
cnt := 0;
go_block('aan');
last_record;
l_rec := :system.cursor_record;
first_record;
[code]....
but this works as saving the last record only, not saving the all records.
View 4 Replies
View Related
Mar 30, 2010
I have a form with two data blocks, one parent, one child block.
The parent is holds mineral lease info while the child holds the mineral owner info, such as addresses and phone numbers. One owner can be in the owner block multiple times (different owner types). The form only displays one owner at a time.
We have a separate master owner table which holds owner address. (We set it up this way because we get electronic info from mineral companies that we have to load each year).
As you tab through the owner block, it checks the FEIN against the master table and pulls updated address info from the master table.
I have a problem in which if an owner is on the lease multiple times, when you tab through the first instance, it pulls in the new address info, but when you go to the next instance, it won't update. If you requery, it seems that the first update actually updated all the owner records on that lease. How can I turn this off?
View 13 Replies
View Related
Oct 29, 2012
OS:Solaris
DB:10G
I have a situation where there are multiple records for a join criteria. I am trying to find a way to update a particular column for all the records returned by the join criteria. Example :
Table A
id number
1 1000
2 2000
3 3000
4 4000
Table B
id number
1 9999
1 9999
1 9999
1 9999
1 9999
1 9999
2 8888
2 8888
3 6666
3 6666
Result after update:
Table B
id number
1 1000
1 1000
1 1000
1 1000
1 1000
1 1000
2 2000
2 2000
3 3000
3 3000
update query ? When I use a update statement with where exists it errors out because the query returns more than one row for the join condition.
View 2 Replies
View Related
Jun 27, 2011
I have a service that executes a pl/sql function (legacy app) to delete records from multiple tables. This function works fine in development, and has worked fine in production until about a week ago. I'm not a DB guy but the DB guys are trying to say this is an application issue. That may be, be the "insuff privileges" really leads me to believe otherwise.
What is causing this type of Oracle error? Permissions between dev and prod are the same, yet it works in dev but not prod.
ORA-29876: failed in the execution of the ODCIINDEXDELETE routine
ORA-20000: Oracle Text error:
DRG-50857: oracle error in textindexmethods.ODCIIndexDelete
ORA-20000: Oracle Text error:
DRG-10602: failed to queue DML change to column ABSTRACT for primary key AAAfBoAAEAABa62AAA
DRG-50857: oracle error in drexrdml
ORA-01031: insufficient privileges
[code]....
View 2 Replies
View Related
Jul 31, 2012
I have two blocks, both are multi record block. 1st block is control block and second is database block. Both the blocks have same fields(Example: Location,Location_name,Location_Type). In the first block(Control Block) I have check box. My goal is when I check the checkbox and click on add button all the records which are selected in first block should go to second block.
View 3 Replies
View Related
Nov 21, 2011
I'm trying to select id's in a table that have 2 certain values for another column. Example below explains:
idCoupon Type
123Amount
123Percent
456Amount
789Percent
I would like to write a sql statement that would select all rows where id=123, because id 123 has both coupon types "Amount" and "Percent". So the result set of the sql statement would look like:
idCoupon Type
123Amount
123Percent
View 6 Replies
View Related
Nov 4, 2013
This is my first post in the form and also i'm new to oracle forms technology.
I have the following issue:
In my form i have two Text-Fields to store some information for a claim:
A --> Profit and
B --> Loss
(only one of the filed can have value)
Also i have a data block where i store the reason of the claim (status). Reason can take three values 1, 2, 3. For each claim i can have more than one reasons stored in db table.
if the user input more than one reasons, then if one of them is 1 Profit should be set unprotected and Loss should be set to protected.
I have the following issue.
user creates a records with reason 1 : Profit text field is set to unprotected and Loss text filed is set protected.
user creates another record with reason 2: Profit text field is set to Protected and Loss text filed is set to unprotected. --> wrong
How can i validate the values of the two records and create the desired functionality?
I have used the POST-TEXT-ITEM trigger but it works correct only for one record. i insert two records with the above order i have the issue i describe.
View 3 Replies
View Related
Jun 28, 2010
I have a requirement where i need to retain latest 3 records based on creation date for each customer_id and delete the older records. The customer_ id or contract_number data in the test table are not unique.
Sample Table Script:
CREATE TABLE TEST
(
CUSTOMER_ID VARCHAR2(120 BYTE) NOT NULL,
CONTRACT_NUMBER VARCHAR2(120 BYTE) NOT NULL,
CREATION_DATE DATE NOT NULL
);
[code]...
View 8 Replies
View Related
Oct 11, 2013
I'm updating a large piece of legacy code that does the following type of insert:
INSERT INTO foo_temp
(id, varchar2_column)
SELECT id, varchar2_column
FROM foo;
We're changing varchar2_column to clob_column to accommodate text entries > 4000 characters. So I want to change the insert statement to something like:
INSERT INTO foo_temp
(id, clob_column)
SELECT id, clob_column
FROM foo;
This doesn't work, since clob_column stores the location of each text entry, rather than the actual content. But is there some way that I can achieve the insert with one call to a select statement, or do I need to select each individual record in foo, open the clob_column value, read it into a local variable and then write the content to the matching record in foo_temp?
View 2 Replies
View Related
Oct 10, 2012
CREATE TABLE test1
(strt_num NUMBER ,
end_num NUMBER ,
des VARCHAR2(5),
CONSTRAINT pk_strt_num PRIMARY KEY (strt_num)
);
INSERT INTO test1 VALUES (5, 8, 'GC');
INSERT INTO test1 VALUES (10, 25, 'AB');
INSERT INTO test1 VALUES (12, 35, 'PC');
INSERT INTO test1 VALUES (22, 65, 'LJ');
SELECT * FROM test1
STRT_NUM END_NUM DES
-------------- ------------ -------------
5 8 GC
10 25 AB
12 35 PC
22 65 LJ
The requirement is the records should be split based on below conditions
1. Split only those records WHERE (end_num - strt_num) > 10
2. If TRUNC((end_num - strt_num)/10) = n, then n + 1 number of rows should be created for that record
3. While splitting the records,
-> For first record , START_NUM = Original STRT_NUM and END_NUM = START_NUM + 10
-> Second record , STRT_NUM = previous END_NUM + 1 and END_NUM = previous END_NUM + 10
And this should continue for all records except the last record
-> For last record, STRT_NUM = previous END_NUM + 1 AND END_NUM = Original END_NUM
This table has 5 million records. Only for 2000 records (end_num - strt_num) > 10.
Expected Output.
STRT_NUM END_NUM DES
------------- -------- ---------
5 8 GC -- No chage, END_NUM - STRT_NUM < 10
10 20 AB
21 25 AB
12 22 PC
23 32 PC
33 35 PC
22 32 LJ -- STRT_NUM = Original STRT_NUm, END_NUM = STRT_NUM + 10
33 42 LJ -- STRT_NUM = Previous END_NUM + 1, END_NUM = previous END_NUM + 10
43 52 LJ -- STRT_NUM = Previous END_NUM + 1, END_NUM = previous END_NUM + 10
53 62 LJ -- STRT_NUM = Previous END_NUM + 1, END_NUM = previous END_NUM + 10
63 65 LJ -- STRT_NUM = Previous END_NUM + 1, END_NUM = Original END_NUM
View 7 Replies
View Related
Feb 27, 2013
I want to update records which returns more than 1 row using store procedure. i tried with ref_cursor but failed to update,
View 1 Replies
View Related
Feb 27, 2013
i am trying to update multiple records using store procedure but failed to achieve
for example my source is
emp_name sal
abhi 2000
arti 1500
priya 1700
i want to increase salary of emp whose salary is less than 2000 it means rest two salary should get update..using stored procedure only
i have tried following code
create or replace procedure upt_sal(p_sal out emp.sal%type, p_cursor out sys_refcursor)
is
begin
open p_cursor for
select sal into p_sal from emp;
if sal<2000 then
update emp set sal= sal+200;
end i;f
end;
and i have called the procedure using following codes
set serveroutput on
declare
p_sal emp.sal%type;
v_cursor sys_refcursor;
begin
upt_sal(p_sal,v_cursor);
fetch v_cursor into p_sal;
dbms_output.put_line(p_sal);
end;
the program is executing but i should get o/p like this after updating
1700
1900
but i am getting first row only
2000
and record is not updating...
View 15 Replies
View Related
Jun 17, 2012
I have Multi record Block and for that block i have created one button, if we press that buttion it will open new block and it will post the records, Unfourtunately that block table dont have Primary key or any constraints .. so when we press that buttoon multiple times .. its posting multiple times..
Now i need to restrict to that which is should not post the records multiple timies i have tried by controling the paraemter..I have created one Non data base item initially value i assigned to 'N"
if the value is "N" then am doing process and showing the records and after processing am assigning the value to 'Y', if there are multiple records , at block level in pre-record trigger am assigning as 'N'.
View 2 Replies
View Related
Mar 25, 2013
I want to insert multiple records on a database using a stored procedure.
View 11 Replies
View Related
Jun 22, 2012
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 Related
May 15, 2003
i'm working on a project right now using Oracle Forms 6.0 and Oracle 9i. after i create a record and save the data in the table, how can i generate/create a text file of that particular record? i need this text file in order to run it in another computer and somehow upload the data in the text file to another database.
i will also need to create the text file for multiple records.
View 7 Replies
View Related
Apr 1, 2012
i have a master-detail form.
detail block is tabular.
when-button-pressed trigger iam inserting records in another table.i write
insert into ONHAND_QTY_LOCATION(sno,matid,matcode,description,partno,onhand_qty,location)
values (:ship_dtl.slno,
:ship_dtl.mat_id,
:ship_dtl.mat_code,
:ship_dtl.description,
:ship_dtl.part_no,
:ship_dtl.rec_qty,
:ship_mstr.place_from)
it is inserting only one record in onhand_qty_location table that too the last record.
i want all the records which iam entering in detail block should get entered in onhand_qty_location table.
View 8 Replies
View Related
Aug 13, 2013
I am new to the Oracle Forms.
I need a Master Detail oracle form for Oracle EBS.
The form should use only one table.
I need the form should look like User Form in EBS.
I created 2 data blocks and designed. When I run the form and key in some data, I notice multiple records stored.
like
Empno Ename Sal Comm
101 null null null
null John 2300 500
Empno is in Master Block.
And remaining are in Detail block. How to remove the duplicate records so that my required output should be like
Empno Ename Sal Comm
101 John 2300 500
View 5 Replies
View Related
