PL/SQL :: Procedure To Merge / SELECT From Table Of Record

May 28, 2013

I want to write a simple stored procedure and I want to keep it as simple as possible (no loop, the least amount of parameters ...etc.)

Basically, the procedure receives a Table Of Record as input parameters and needs to merge it with existing table, the table of record is of the rowtype of the existing table.

I have difficulties to merge these data. Below is what I tried

CREATE or REPLACE PACKAGE BIZ_xxx_MERGE
IS
TYPE xxx_ACTIVITE_Type IS TABLE OF MyTbl%RowType INDEX BY BINARY_INTEGER;
PROCEDURE MERGE_xxx_ACTIVITE_SP (
MyLP IN xxx_ACTIVITE_Type
);
END BIZ_xxx_MERGE;

CREATE OR REPLACE PACKAGE BODY BIZ_xxx_MERGE AS
PROCEDURE MERGE_xxx_ACTIVITE_SP (
MyLP IN xxx_ACTIVITE_Type
) AS
BEGIN
MERGE INTO MyTbl
[code].........

View 10 Replies


ADVERTISEMENT

SQL & PL/SQL :: Select First Unlock Record In Table For Update

Jun 6, 2012

I'm looking for a solution to select the first row that is not currently locked in a table and insert a record to another table that reference that first row. this is my scenario:

create table ticket
(
id number(10) not null,,
ticket_type number(1) not null,,
is_sold number(1) not null,
CONSTRAINT ticket_pk PRIMARY KEY (id)
);

[Code]...

id ticket_type is_sold
------------ -------------------- -----
10000004 1 1
10000005 2 1
10000006 1 0
10000007 1 0
10000008 2 0
10000009 2 0

SQL> select * from customer_ticket;

cust_id cust_name ticket_id
------------ -------------------- ----------
1 John 10000004
2 Sara 10000005

my goal is finding the first free ticket ( not sold ) in the ticket table and insert buyer information of that ticket in customer_ticket table. at last I will mark that ticket as a sold one in ticket table with update.

Problem is that the first transaction locks the the first row in ticket table and the second transaction running the same query goes to wait untill the first transaction commit or rollback. However when first transaction finish successfully, second transaction select duplicate id from ticket table that was selected by the first transaction!

I tried to solve problem with "skip locked" and "nowait" options with select for update, but they didn't work.

View 13 Replies View Related

SQL & PL/SQL :: How To Select Latest Updated Record From Table

Sep 21, 2011

I want to know like How we can select the latest updated record from xyz table. that record has STATUS column. I also want to check if the status is RED or GREEN query should return if the status is red then 1 and if the status is GREEN then it should return 0

View 8 Replies View Related

Forms :: How To Select 1st Record From The Duplicate Values In Table Without Using Rownum And Rowid

Apr 23, 2010

how to select 1st record from duplicate vales in a table.

If we created one table with out primary key column In form in search block have uwi value and top_depth value when i enter uwi and top_depth value then when i click search button then it will display all values in master block.

but here duplicate values r there.

SQL> select rownum,uwi,top_depth,base_depth,test_start_date from well_pre_header;

ROWNUM UWI TOP_DEPTH BASE_DEPTH TEST_STAR
---------- ---------------- ---------- ---------- ---------
1 100 453.05 458.08 09-SEP-10
2 100 200 288 23-AUG-00
3 1001 200 289 25-AUG-01
4 1001 200 201 24-MAY-87

if uwi = 1001 and top_depth=200 and i will click search button it should be display 3 record & when i click next button then it will show 4th record.

View 3 Replies View Related

SQL & PL/SQL :: Merge Two Statements (Select ID) Into Single Output

Aug 11, 2013

I want to merge the following two sql statements into single output.

select id,count(*) from derailed where changed_on between to_date('26-july-13 18:30:00','DD-Mon-YY hh24:MI:SS') and to_date('01-August-13 18:29:00','DD-Mon-YY HH24:MI:SS') group by id;

select id,code from dbo;

View 11 Replies View Related

SQL & PL/SQL :: Merge Statement In Procedure - Internal Error Code

Feb 9, 2011

I have a merge statement in one procedure , this job runs on daily . It is running successfully since Jan-19-2010 and giving the below error now.

ERROR at line 1:
ORA-00600: internal error code, arguments: [kcbgcur_9], [4224596], [1],
[4294967250], [2], [], [], []

It is giving error in the beginning of the MERGE statement.

View 11 Replies View Related

SQL & PL/SQL :: Select Statement From Schemas In MERGE Statement In USING Clause

Sep 13, 2013

In the following merge statement in the USINg clause...I am using a select stament of one schema WEDB.But that same select statement should take data from 30 schemeas and then check the condition below condition

ON(source.DNO = target.DNO
AND source.BNO=target.BNO);

I thought that using UNIONALL for select statement of the schemas as below.

SELECT
DNO,
BNO,
c2,
c3,
c4,
c5,
c6,
c7
[code]....

View 5 Replies View Related

SQL & PL/SQL :: Merge Two Table Into One

Sep 17, 2011

i HAVE TWO TABLES

EMP_NAME_DETAILS

EMP_ID EMPNAME

EMP_ADD_DETAILS

EMP_ID EMPCITY

NOW I WANT TO MERGE/COMBINE THIS TWO TABLES INTO ONE FLAT TABLE SO MY REPORTS WILL RUN ONLY FROM ONE TABLE INSTEAD OF TWO.

MY DESIRED OUTPUT TABLE WOULD BE

EMP_ID EMPNAME EMPCITY

View 2 Replies View Related

SQL & PL/SQL :: Merge Into Table Name Using Cursor

Jun 28, 2010

I am trying to merge into test table using a cursor. If the contract number in the test table matches with the contract number from the cursor then i need to update the creation_date else i need to insert values from the cursor into the test table.

MERGE INTO test
USING cursortest
ON (i.contract_number = test.contract_number)
WHEN MATCHED THEN
[code]......

View 9 Replies View Related

SQL & PL/SQL :: Merge Two Table With Distinct Value

Sep 4, 2012

select * from a (6 column)
where wallet='01710000273'
select * from b (2 column)
where wallet='01710000273'

when I union these two tables it find two row for similar wallet, but i want to show these in one row. How will possible this?

View 1 Replies View Related

SQL & PL/SQL :: Using Table Type With Merge?

May 31, 2007

Can i use table type in the using clause of merge statement ?

Attached is the code of the procedure

create or replace procedure fnd_sample as
cursor fnd_c is select * from fnd_columns;
type test_t is table of fnd_columns%rowtype;

[Code]....

(APPLICATION_ID,TABLE_ID,COLUMN_ID,COLUMN_NAME,USER_COLUMN_NAME,COLUMN_SEQUENCE,
LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,
COLUMN_TYPE,WIDTH,NULL_ALLOWED_FLAG,TRANSLATE_FLAG,FLEXFIELD_USAGE_CODE) values
(f.APPLICATION_ID,f.TABLE_ID,f.COLUMN_ID,f.COLUMN_NAME,f.USER_COLUMN_NAME,
f.COLUMN_SEQUENCE,f.LAST_UPDATE_DATE,f.LAST_UPDATED_BY,f.CREATION_DATE,f.CREATED_BY,
f.LAST_UPDATE_LOGIN,f.COLUMN_TYPE,f.WIDTH,f.NULL_ALLOWED_FLAG,f.TRANSLATE_FLAG,f.FLEXFIELD_USAGE_CODE);
end loop;
exception
when others then
dbms_output.put_line(SQLERRM);
end;
/

I am getting an error saying that

/19 PL/SQL: SQL Statement ignored
8/60 PL/SQL: ORA-00942: table or view does not exist

View 18 Replies View Related

PL/SQL :: Merge Statement On Single Table

Oct 29, 2012

{code{

i want to apply merge stmt on single table.

CREATE TABLE TEST11(TNO NUMBER(5), TVAL VARCHAR2(100), TID VARCHAR2(10));
INSERT INTO TEST11 VALUES(1,'VIJAYA','TEST');
INSERT INTO TEST11 VALUES(2,'VIJAYA','TEST');
INSERT INTO TEST11 VALUES(3,'VIJAYA','TEST');
INSERT INTO TEST11 VALUES(4,'VIJAYA','');
INSERT INTO TEST11 VALUES(5,'VIJAYA','');

[Code]....

My requiremen is if record is exists then i wan to update some value, if record not existes the i wan to insert new record

View 3 Replies View Related

SQL & PL/SQL :: Can Have All Rows In A Table Updated At Once In Merge Statement

Aug 10, 2010

Can I have all the rows in a table updated at once in the merge statement?

MERGE INTO providermaster a
using
(
SELECT * FROM PROVIDERMASTER@INGEST) b
ON (b.MASTERPROVIDERID=a.MASTERPROVIDERID)
WHEN MATCHED THEN

UPDATE ....... I want to update all the other rows at once..

View 24 Replies View Related

SQL & PL/SQL :: Select Record As Specified

May 30, 2012

Based on ACCOUNT_NUMBER column we have to check the GUID data in test table.

for example ACCOUNT_NUMBER =11 as duplicate it has 2 values then only, we need to check the corresponding GUID if any occurence ("9f680174-cb87-4f71-887a-92" and "9f680174-cb87-4f71-887a-91"),
we should select,if not leave it.

CREATE TABLE test
(
GUID VARCHAR2(32 BYTE),
ACCOUNT_NUMBER NUMBER(30),
INDIVIDUAL_ID NUMBER(13)
)

[Code]...

it tried as like as below it's working.

select t2.* from (select distinct t1.guid
from test t1,( select ACCOUNT_NUMBER from test
group by ACCOUNT_NUMBER having count(ACCOUNT_NUMBER

[Code]...

View 15 Replies View Related

PL/SQL :: Update Vs Merge - Updating One Table Based On Values In Another

Jun 26, 2013

I have two tables lets say TAB_A and TAB_B. I altered table B to include a new column from table A I wrote a merge statement as follows to merge the data 

MERGE INTO TAB_AUSING TAB_BON (TAB_A.SECURITYPERSONKEY=TAB_B.SECURITYPERSONKEY)WHEN MATCHED THEN  UPDATE SET TAB_A.PPYACCOUNT=TAB_B.PPYACCOUNT; 

I know INSERT is for inserting new records UPDATE to my knowledge is to modify currently existing records (loosely) MERGE is one I rarely used, until this particular scenario. The code works perfectly fine, but I was wondering how could I write an update statement? Or in this scenario should I even be using an update statement?

View 4 Replies View Related

Unable To Select Only Record For 07 Nov 06

Nov 13, 2006

I am unable to select only record for 07Nov06. if i use between i get records e.g. :07 November ,2006-0941

Select
TO_CHAR(session_START_time,'dd month,yyyy-hh24mi') LOG_IN_TIME,
TO_CHAR(session_END_time,'dd month,yyyy-hh24mi') LOG_OUT_TIME
from SESSION_LOG
where SESSION_LOG.SESSION_START_TIME
between '06Nov06' AND '07Nov06';

View 2 Replies View Related

Getting Different Record Counts When Select Overall?

Nov 7, 2011

I have a UNION query having 3 parts, 1st gets date, 2nd data and 3rd displays the formatted data count :WHERE clause of 2nd and 3rd queries are same.

Problem is that I an getting different record counts when I select the overall count of records given by the whole UNION query and when I run to count the records given by each query individually.First count. Here I am selecting the overall count of records given by the query :

select count(1)
from (
SELECT SUBSTR ( '0'
|| TO_CHAR (SYSDATE, 'MM/DD/YYYY')
|| TO_CHAR (SYSDATE, 'HH:MI:SS')
|| LPAD (' ', 180)

[code]...

This count is : 1751525 Second count. Now when I run to count the records given by each query individually, here is the result

select count(1) a
from (
SELECT SUBSTR ( '0'|| TO_CHAR (SYSDATE, 'MM/DD/YYYY') || TO_CHAR (SYSDATE, 'HH:MI:SS')
|| LPAD (' ', 180)|| chr(13), 1, 200 ) dtl_record from dual
select count(1) b from (
SELECT '1'

[code]...

why there is difference of 1 (1751526 - 1751526) in the count results.

View 1 Replies View Related

SQL & PL/SQL :: How To Select Correct Record

Sep 17, 2013

I have a table of below structure:

CREATE TABLE Emp_addrs
(
EMP_ID NUMBER(15) NOT NULL,
ADDRESS_ID NUMBER(15) NOT NULL,
SITE_USE_ID NUMBER(15) NOT NULL,
SITE_USE_STATUS VARCHAR2(1 BYTE) NOT NULL,
SITE_USE_CODE VARCHAR2(30 BYTE) NOT NULL)

Insert Script code :

insert into Emp_addrs values ( '1207' , '1846', '2342','A');
insert into Emp_addrs values ( '1207' , '1846', '2343','I');
insert into Emp_addrs values ( '61618' , '165200', '261449','A');

[Code]...

A combination of emp_id & address_id can have multiple site_use_id's. I want to select the max(site_use_id) where site_use_status ='A'.
Now Site_use_status can have either = 'I' or 'A' value.

For a combination of emp_id and address_id , there could be cases when there is no record with site_use_status ='A'. In such cases I need to select the max(site_use_id) (and obviously site_use_status ='I').

Just to clear my requirements, out of the above records I want the following records:

'1207' , '1846', '2342','A'
'61618' , '165200', '261449','A'
'1003' , '1004', '1007','A'
'1005' , '1010', '2002','I'
'2005' , '2010', '3002','A'

View 9 Replies View Related

PL/SQL :: Select Record Between Date Gap

Aug 2, 2013

I have a sql query where I need to select only records with an 18 month gap between max(date) and previous date( no dates between max(date)and 18 month gap date), when I run the below query it should only select supid 130, not 120 (even though 120 does contain an 18 month gap date it also has a date that is less then the 18 month gap( '25-NOV-2012','DD-MON-YYYY'). how would get the query to look back 18 months for the next date and evaluate the month_between.

. example: 

create table supply(supID number(8), supply varchar2(20), supdate Date,supamount number(13,2));
 insert into supply values(100,'Tapes',to_date('01-AUG-2013','DD-MON-YYYY'),50.00);
insert into supply values(100,'TV',to_date('01-APR-2013','DD-MON-YYYY'),250.00);
insert into supply values(100,'Discs',to_date('25-DEC-2012','DD-MON-YYYY'),25.00);
insert into supply values(120,'Tablets',to_date('25-AUG-2013','DD-MON-YYYY'),15.00);

[Code]....

and p.supid in(select s.supid from supply s where months_between

(s.supdate,p.supdate)<-18)      SUPID SUPPLY               SUPDATE    SUPAMOUNT---------- -------------------- --------- ----------       120 Tablets              25-AUG-13         15       130 Discs                25-JUL-13         75

View 9 Replies View Related

Select From Main If There Is No Record In History

Aug 9, 2010

I have 3 main tables as projects, tasks, clients. Then I have a history table for each. I created a trigger on update/delete that takes the old records and inserts into the history.

Now, I need to retrieve the history but I have no clue how to do so. I need to join these 3 history tables with:

1. select from projects_history with projectID = 1
2. if no records, then go back to projects and get projectID = 1

3. select from task_history with taskID = 1
4. if no records, then go back to tasks with taskID = 1

5. select from client_history with clientID = 1
6. if no records, then go back to client with clientID = 1

where client.taskID = task.taskId and task.projectID = project.projectID.

View 1 Replies View Related

SQL & PL/SQL :: Select All The Elements Of A Record Type

May 24, 2011

Is there any way that I can check what are the elements present in a pl sql record type by querying in table?

For example if I want to check what are elements present in oe_order_pub.header_rec_type and I don't want to open the package, then in which table I should query? Is it possible?

View 2 Replies View Related

Record Count Mismatch Between Select And Insert

Mar 20, 2013

We are trying insert records from a select query in to temporary table, some of the records is missing in the temporary table. The select statement is having multiple joins and union all which it little complex query. In simple terms the script contains 2 part 1st Part Insert in to temporary table 2nd part Select query with multiple joins, inline sub queries, unions and group by classes and conditions Eg. If we execute select statement alone it returns some count for example => 60000 After inserting into the temp table, in temp table the count is around 42000 why is the difference?

It is simple bulk inserts... insert in to temp table select * from xxx. also, there is no commit in between. The problem is all the records populated by the select statement are not inserted in to temp table. some records are not inserted.

Also, we had some other observation. It only happens in its 2nd execution and not its first run. Hope there might be some cache problem
Even, we also did not believe that. We are wondering. In TOAD, we tested however at times it happens. In application jar file, after "insert in to temp select * from xxx" we take the i. record count of temp table and ii. record count of "select * from xxx" separately but both doesn't match. Match only at 1st time.

View 3 Replies View Related

SQL & PL/SQL :: How To Get Latest Record In Embedded Select Statement

Mar 12, 2013

I am trying to run an Oracle report with a query that has an embeded sql. this sql is returning more than 1 row, and the report is failing.

I need to pick the latest record entered that this sql return.

I tried rownnum and it works but only i can get the rown num I specify, not the latest record. I try to order, but I am getting an error back.

select w.emp_no, (select t.timestamp
from tob.work_unit t
where t.work_date = to_date('20130312', 'YYYYMMDD')
and rownum = 1
order by t.timestamp desc)
,w. spare_type
from work.work_unit w
where w.work_date = to_date('20130312', 'YYYYMMDD')

I am getting missing right parenthesis at the order by keyword My report is much complex than this, but I am tring to see if I can get the row that I want.

View 10 Replies View Related

SQL & PL/SQL :: Dynamic Procedure Name With Record Type I/p Parameter?

Jun 22, 2012

I have a problem with passing procedure name dynamically with record type i/p parameter..I'm not attaching any insert/create table statements, as I'm unsure of forming the sql statement dynamically..

CREATE OR REPLACE PACKAGE med_order_pkg AS
TYPE deid_med_order_typ IS RECORD(....)
L_deid_med_order_typ deid_med_order_typ;
PROCEDURE RULE_MASTER_PRC (P_IN_RULE IN deid_med_order_typ);
END;

[code]....

From the above, I need to execute a procedure with record type as i/p parameter..V_SQL should form the statement & execute another procedure which comes into the variable V_MSG_PROC .I'm having difficuly in forming the statement...(I did it by hard-coding the procedure with parameter in the next line which is commented out & it works...So how can I modify V_SQL in the above statement?

View 3 Replies View Related

SQL & PL/SQL :: Executing Dynamic Procedure With Record Type

Jul 26, 2012

I'm not attaching any tables / data..etc...I just want to know how to pass the record type to a procedure (which are actually obtained from a table) -- see ** below where I'm getting an error..Need to pass the whole record type "l_shl_order_msg"

CREATE OR REPLACE PROCEDURE CM_BUILD_MSG_PRC (P_IN_BLD_MSG_CURSOR IN SYS_REFCURSOR,
P_OUT_BLD_MSG_CURSOR OUT SYS_REFCURSOR)
IS
l_shl_order_msg CRAE_INTERFACE.GLB_VAR_PKG.deid_SHELL_order_typ;
V_MSG_SHELL_NAME VARCHAR2(1000);
V_MESG_TEXT_SEGMENT VARCHAR2(1000);
V_TEXT VARCHAR2(1000);
V_MSG_TEXT VARCHAR2(4000);
V_MSG_FINAL_TEXT VARCHAR2(4000);
V_MSG_PROC VARCHAR2(1000);
V_SQL VARCHAR2(4000);
V_CNT NUMBER;
L_STATUS VARCHAR2(100);
L_REASON VARCHAR2(1000);
[code]...

I get an error saying that "wrong number or types of arguments in call to ||"..Not sure how to pass record type dynamically...

View 10 Replies View Related

Overload PL / SQL Procedure Based On Different Record Type With Same Structure

Apr 8, 2004

I have two cursors like

cursor_A IS select * from table_a where condition_A;
cursor_B IS select * from table_a where condition_B;
record_A is a recorded of cursor_A%ROWTYPE
record_B is a recorded of cursor_B%ROWTYPE

I define a procedure like pro_A(record_in cursor_A%ROWTYPE) can I overload this procedure by defining pro_B(record_in cursor_B%ROWTYPE)?

If I can't, Can I call pro_A by passing record_B as the parameter to it?

View 3 Replies View Related

Precompilers, OCI & OCCI :: EXEC SELECT Query Not Fetching Record Even Though It Is Present In Database

Jun 26, 2008

I am using Pro*C/C++ Release 10.2.0.2 in HP-UX. But Pro*C/C++ application was written long back during oracle release 8. Now we are facing a problem like EXEC SELECT query is not fetching the records even though record is present in DATABASE.

This is not happening every time. This problem starts happening only after heavy use of the unix process.

For every request, unix process will fetch the record and updates the same at the end and process goes to wait mode to get the request again. Let say after 50 request, process is returned with no rows found error.

It started working fine only after restarting the process and problem starts again after 50th or 60th request. This problem we are facing only after upgrade to 10g.

View 1 Replies View Related

PL/SQL :: How To Write Procedure To Load Data Into Table Using XML File As Input To Procedure

Sep 20, 2013

how to write procedure to load the data into a table using xml as input parameter to a procedure and xml file is as shown below which is input to me. 

xml version="1.0"?><DiseaseCodes><Entity><dcode>0</dcode><ddesc>(I87)Other disorders of veins - postphlebitic syndrome</ddesc><claimid>34543></claimid><reauthflag>0</reauthflag></Entity><Entity><dcode>0</dcode><ddesc>(J04)Acute laryngitis and tracheitis</ddesc><claimid>34543></claimid><reauthflag>0</reauthflag></Entity><Entity><dcode>0</dcode><ddesc>(J17*)Pneumonia in other diseases - whooping cough</ddesc><claimid>34543></claimid><reauthflag>0</reauthflag></Entity></DiseaseCodes>.

View 3 Replies View Related

SQL & PL/SQL :: Exception In Procedure / Created One Procedure Based On One Table Item Master

Mar 6, 2010

I have created one procedure based on one table item master which has a field called item stock or non stock based on this i will fetch data from one of two tables .If its a stock item data will be retrieved from wip_main_acnt table and if its non stock it will pick from ns_main_acnt.my procedure is working fine but all i need is i just want to put an exception that if data is not found in one of the table based on the item selected.I am confused which one to be used whether no_data_found or notfound%.

CREATE OR REPLACE PROCEDURE dflt_pr_acnt (
l_item_code IN VARCHAR2,
l_main_acnt_code OUT VARCHAR2
)
[code]....

View 8 Replies View Related

SQL & PL/SQL :: Single Select Statement Instead Of Procedure?

Oct 2, 2010

presenting the case as follows:There are two dates with time components like

26-sep-2010 13:00 and 29-sep-2010 19:00

In between these dates hours between 20.00 to 05.00 (night hours)need to be considered and In these night hours less than three hours can be ignored and between 3 to 9 hours should be treated as 1. Need the number of one's in between the dates.

Procedure, cursor solution is not needed and need single select statement.

View 5 Replies View Related







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