SQL & PL/SQL :: Retrieving Data From Multiple Tables Using Procedure
Nov 7, 2011how to write this query using procedure and how it can display.
SELECT iusr_id,
so_curr_assign_to,
comp_id,
[Code]....
ADVERTISEMENT
SQL & PL/SQL :: Retrieving Multiple Values In Stored Procedure Using Variables And Cursors
Oct 6, 2011The goal is to create a stored procedure that will retrieve multiple values from a table.
GUI is in Java and they will trigger our procedure to show list of all employees and their roles , doj etc.
So I wrote the following procedure.
---------------------------------
create or replace
PROCEDURE emp_test(
c_cursor OUT SYS_REFCURSOR)
AS
BEGIN
OPEN c_cursor
FOR
SELECT emp_name, emp_doj, emp_role FROM emp_table ;
END;
---------------------------------
I'm using sql developer, stored procedure is compiled and I can manually run it by right clicking on the procedure and click 'Run'.
When I intend to run it by executing the script "Execute Procedure name ", I get errors.
In SQL Developer, I open new SQL file and key in
EXECUTE emp_test;
Highlight it and run the script, here is the list of errors that I get.
-------------------------------------------
Error starting at line 18 in command:
execute frm_lst
Error report:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'emp_test'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:
%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
--------------------------------------------
Issue 2:
Instead of using cursor, is there a way to use multiple parameters and insert the data retrieved from select column_name from table into these parameters and build it in a stored procedure. I tried something like below which did not work.
____________________________________________________
CREATE OR REPLACE PROCEDURE emp_test1
(e_name OUT emp_name%TYPE,
e_dob OUT Edob%TYPE)
IS
BEGIN
SELECT emp_nam, Edob
INTO e_name, e_dob
FROM emp_table
END emp_test1;
End;
______________________________________________________
Just so you know, there is no input parameter or input feed, when called the procedure should return all the values.
SQL & PL/SQL :: Procedure To Update Or Insert Data In Multiple Tables
Apr 9, 2011I'm writing a Procedure which Updates or Inserts data in Multiple tables. Selected fields of 10 tables need to be updated or Inserted. For this I created a table which comprises of fields related to all 10 tables. Then I write Procedure. Under this I create a Cursor which uploads the data from the newly created table which contains different fields of 10 tables. Then I write Update and Insert statements one by one for all 10 tables.
Sample Procedure below.
-------------------------------------------
Create or replace procedure p_proc as
spidm spriden.spriden_pidm%type;
cursor mycur is select * from mytable;
begin
for rec in mycur
[code]......
----------
Note: I created table on my server because data is coming from different server. They will upload the data in the table from there I pick and update the tables. Is updating or Inserting data in different tables one by one is correct?
SQL & PL/SQL :: Stored Procedure / Extract Data From Excel To Multiple Tables
Oct 14, 2010This is my first time running a stored procedure. The procedure is already written.
We have various related table. I need to use this stored procedure and extract information from an excel sheet into the multiple tables.
Retrieving Data From 3 Or More Tables
Apr 12, 2008I have a problem with my Oracle 9i SQL Query and I'm struggling to get it done.
I have three tables namely Student, Lease and Room and want to retrieve data from these three tables.
I want the Student name, the Lease details and the Room No from these tables.
The problem with my SQL query is that I get all the information from the tables except from the Room table, where in the column it show Room_No but the values are not displayed, the query is given below.
SELECT STUDENT.STU_FNAME, STUDENT.STU_LNAME, LEASE.LSE_NO, LEASE.LSE_DURATION, LEASE.LSE_STARTS, LEASE.LSE_ENDS, ROOM.ROOM_NO
FROM STUDENT
LEFT OUTER JOIN LEASE ON LEASE.STU_ID = STUDENT.STU_ID
LEFT OUTER JOIN ROOM ON ROOM.PLACE_NO = LEASE.PLACE_NO;
SQL & PL/SQL :: Retrieving Records From Tables
Mar 27, 2010I want to retrieve the data from ex.tables. How to get this.
table1:
Account_No, Account_sub_No
1234 1
1234 2
1234 3
2345 4
2345 5
2345 6
2345 7
2345 8
................
Account_no is the primary key
table2:
Account_sub_No, Description
1 Hello
2 Hi
3 No.1
4 great
5 people
.................
8 world
..........................
Account_sub_No is primary key.
Out put:
I want the data like Account_no who is having more than 3 Account_sub_no values.
But in my case need to join these two tables with other tables. join field is Account_no from table1. there are no other fields to join.
SQL & PL/SQL :: Getting Data From Multiple Tables
Feb 11, 2011What are some performance issues you might run into when having to query 3 or 4 tables at a time.
View 1 Replies View RelatedBlock Can Contain Rows / Data From Multiple Tables?
Jul 1, 2013A block shouldn't have rows from multiple tables... Is that true? I read in one of the OTN thread (i don't exactly remember the thread name) that a block can have data from multiple tables. If it doesn't have, what's the table directory in block signifies?
View 9 Replies View RelatedImport Data To Multiple Tables In Oracle DB From SQL Server?
Jun 30, 2012What is the Best way to import data to multiple tables in Oracle Data base from sql server?
1)linked server?
2)ssis ?
If possible share me the query to done this task using Linked server?
Best Way To Import Data To Multiple Tables In Oracle DB From SQL Server
Mar 7, 2013What is the Best way to import data to multiple tables in Oracle Data base from sql server?
1)linked server?
2)ssis ?
If possible share me the query to done this task using Linked server?
SQL & PL/SQL :: Retrieving Names Of Tables That Contain Similar Information In Oracle Database?
Nov 16, 2010it's possible to retrieve the names of tables that contain similar information in an oracle database.
View 7 Replies View RelatedServer Utilities :: Loading Multiple Input Files Into Multiple Tables
Jul 9, 2012NGFID;RECTYPE;RECNAME
25;7;POLES
PARENT
CHILD;1401;9845075;2020
817;8;SUPPORT
PARENT
CHILD
Required output:-
AREA_SRNO = 1
AREA_NAME = '3rivieres.export.ngf'
File :-mauri.export.ngf
NGFID;RECTYPE;RECNAME
257;7;POLES
PARENT
CHILD;1401;9845075;2020
8174;8;SUPPORT
PARENT
CHILD
Required output:-
AREA_SRNO = 2
AREA_NAME = 'mauri.export.ngf'....etc
CREATE TABLE NGF_REC_LINK
(
AREA_SRNO NUMBER(2),
AREA_NAME VARCHAR2(40),
NGFID NUMBER(20),
TABLENAME VARCHAR2(40),
PARENT VARCHAR2(200),
[code].......
find the ctl file (ngf_test.ctl) and modify the ctl file as per my requirement.
SQL & PL/SQL :: Retrieving Data From Table
Mar 21, 2013i have requirement, that is to retrieve the data from pl/sql table.
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
[code]....
PL/SQL procedure successfully completed.now i want receive the data from particular x record to y record
SQL & PL/SQL :: Retrieving Data Between Two Dates
Nov 3, 2011I have one table when I am querying like below
select * from timeoffreqitem
where timeoffreqitemid=134
getting data like below
134 144 07-OCT-11 13-OCT-11 134
I need to see this result as below.
134 144 07-OCT-11 13-OCT-11 134
134 144 08-OCT-11 13-OCT-11 134
134 144 09-OCT-11 13-OCT-11 134
134 144 10-OCT-11 13-OCT-11 134
134 144 11-OCT-11 13-OCT-11 134
134 144 12-OCT-11 13-OCT-11 134
134 144 13-OCT-11 13-OCT-11 134
I am looking at multilple optons.
PL/SQL :: Retrieving Periodical Data
Mar 7, 2013I have a requirement like to retrieve past 6 months data and i have used below query.
select count(1) from event where trunc(start_datae) between trunc(sydate)-180 and trunc(sysdate);it is giving the results but am not sure whether it is giving correct data or not.If ran the above query it is taking more time to execute.
Is the above approach is correct? Is there any difference between trunc(sysdate)-180 and trunc(sysdate-180) ?
SQL & PL/SQL :: Retrieving The Data Based On Count?
Apr 2, 2010I want to retrieve the data based on the count.
Sample data
AccountSubAccountDate
11.12-Mar-10
11.23-Mar-10
11.34-Mar-10
11.42-Mar-10
11.56-Mar-10
21.67-Mar-10
21.78-Mar-10
21.82-Mar-10
3210-Mar-10
32.111-Mar-10
32.22-Mar-10
32.313-Mar-10
32.414-Mar-10
32.52-Mar-10
42.616-Mar-10
42.717-Mar-10
What i want is I want the data which account is having more than 5 subaccounts within the last one month. Also need data with other date criteria like last 100 days if more than10 sub accounts. need single query.
Output is :
3210-Mar-10
32.111-Mar-10
32.22-Mar-10
32.313-Mar-10
32.414-Mar-10
32.52-Mar-10
Having more than 8lacks in my database. i wrote the query but it is taking much time and didnt give the data even after 14 hours.
My query is :
select * from table a where account in
(select account from table b where b.subaccount=a.subaccount
and b.date>=sysdate-35
group by b.account having count(b.subaccount)>5)
union
select * from table a where account in
(select account from table b where b.subaccount=a.subaccount
and b.date>=sysdate-100
group by b.account having count(b.subaccount)>10)
how to retrieve the Subaccounts which satisfies my requirements.
Forms :: Retrieving The Data According To Where Clause?
Jul 30, 2010I want if the user write for example in text box 'AM TK' the query display the resualt which has am alone tk alone, and that has both. I know that i should use the Like with % but i do not know how to write it in the set property. I have wrote
set_block_property('Employee_Other',default_where,'Upper(name) like '''||UPPER(:key_search.person_name)||'''') ;
This will bring the resualt just if the user write am tk. How i can modify it to return value as i explained above.
Windows :: Retrieving Data From Corrupt XP?
Oct 9, 2011i am using oracle 9i database.i want to retrieve data from corrupt windows xp.but i do not know what i do for that.
View 2 Replies View RelatedPL/SQL :: Retrieving Data Between two Dates?
Apr 3, 2013I am using Oracle 11g version
create table ORG(Name char(20),Datetime char(45),val1 number);
insert into ORGvalues('abc','10/29/2012 13:00','1.5')
insert into ORGvalues('abc','10/29/2012 13:05','1.5')
insert into ORGvalues('abc','10/29/2012 13:10','1.5')
insert into ORGvalues('abc','10/29/2012 13:15','1.5')
insert into ORGvalues('abc','10/29/2012 13:20','0.00')
insert into ORGvalues('abc','10/29/2012 13:25','0.00')
[code]....
while I am retrieving data between two dates. from table ORG. select from ORG where datetime between '29/03/2013' and '30/03/2013' order by datetime asc*
the output is coming like this,
O/P:
abc 29/03/2012 13:00 1.5
abc 29/03/2012 13:05 1.5
'abc 29/03/2012 13:10 1.5
'abc 30/03/2012 13:15 1.5'
'abc 30/03/2012 13:20 0.00
'abc 30/03/2012 13:25 0.00
[code]....
My problem in above out put is Here I am getting previous year data also at same day and month I want data only between the dates which are specified in query
But here needed output is
*'abc 29/03/2013 13:35 0.00*
*'abc 29/03/2013 13:40 2.1*
*'abc 30/03/2013 13:45 2.3*
*'abc 30/03/2013 13:50 2.1*
*'abc 31/03/2013 13:55 2.1*
*'abc 31/03/2013 14:00 2.2*
Note:my datetime datatype is char
My dear friends actually I am getting data like this date as a string(CHAR datatype) from third party tool.Due to this reason only I treat datetime attribute as string.
Performance Tuning :: Retrieving From Data Is Slow
Jan 12, 2011I'm extracting/retrieving the data from the oracle database using Java application it's bit slow. However, when I retrieve from the SQL server it's faster than oracle.
View 6 Replies View RelatedSQL & PL/SQL :: How To Insert Data In Multiple Bases Using Multiple Database Links
Jan 2, 2013how to insert the data in multiple bases( Same table structure in different bases) using the multiple database links?
View 4 Replies View RelatedSQL & PL/SQL :: How To Sum Over Multiple Tables
Feb 21, 2010I'm trying to do a sum over 2 different tables but can't get it to work...This is the idea:I have a table A with client ID, time-id (per day), purchase amount and segment code.
In another table (let call it B) I have a lot of client ID's and also their purchase amount, time-id and segment code. I want to sum the purchase amount for every client from table A and B for clients with certain segment code from table B.
This is what I have now:
select client_id, purchase_amountA+ purchase_amountB from tableA, tableB where
A.client_id = B.client_id
and time_id between 20090101 and 20091001
and B.segment_code = 'A'
This does the job, but it selects only client_id's which are in both tables. I want to select all client_id from table B with segment_code 'A' and add the purchase_amount from table A to their purchase amount from table B, at least, if they have any purchase amount in table A.
SQL & PL/SQL :: Multiple Tables
Sep 30, 2010Video Rental Shop
Each customer has a video card , When Customer rent a CD , Shopkeeper register an issue date and a Return Date . If customer return CD after Return Date Then There will be a fine of 2 Dollor .
After every 6 Months The shop Keeper review each customer Account , and Send Gifts to those customer whose Total Amount is More than 50 Dollar .and also send letters to those whose Fines Are More than 20 Dollor .
Now I am unable to understand that how many table i need to create for this .
What i have created so far is given below ,
When Customer Rent a CD then Shopkeeper will submit Following Information .
Customer_id 101
Issue DateDATE
Expected_return_dateDATE
Original_return_date-
Fine -
Total_Amount -
And at the time of return , he will Put these information .
Customer_id 101
Issue DateDATE
Expected_return_dateDATE
Original_return_date DATE
Fine 2
Total_Amount5
But Do i need to create another table for each customer also ? That will store customer total amount , total Fines ,and shopkeeper will view it after every six months. Which type oo table i need to create ?
Selecting Via Multiple Tables
Jan 19, 2011I need to calculate a list of people, who got some services more that 2 times with the same service koda (pas_kodas) to the same person (zmo_kodas). It should not depend on report number.
[URL]...
What I get is in green (services are calculated more than 2 times BUT in the same report).
What I need is in red: calculate servises more that 2 times ACCROSS all reports to the same person (zmo_kodas).
[URL]...
One person (zmo_kodas) can have a lot of reports (ats_nr).
Every report can have one or more services (pas_kodas).
SQL & PL/SQL :: Get Sum Amounts From Multiple Tables?
Mar 2, 2011I have a primary table and some secondary tables. I need to use the key from Primary table and get the sum/totals from the other tables with a single query.
For example, I have 3 tables employee, employeesal and employeerewards in which employee is the primary table. I need to get the total salary for an employee from employeesal table and total rewards from the employeerewards table using one query.
I am pasting the test data below:
create table employee (eno number(9), ename varchar2(40));
insert into employee values(1,'Jack');
insert into employee values(2,'Jim');
[Code]....
I am looking for a better way of doing this in terms of performance. In my projecct, those 3 tables are very big tables and I doubt if I do two inner queries and then use a outer query may not be the best way to do it.
Is there any better way to write this query in terms of performance?
SQL & PL/SQL :: JOIN For Multiple Tables
Mar 29, 2011Here is what I'm trying to do:
I have three tables:
TABLE1 TABLE2 TABLE3
NCI 1AB OPEN
NCI (NULL) (NULL)
INV (NULL) (NULL)
NCI 2AB REOPEN
What I want to do is to return all of the rows from TABLE1 that are NCI regardless, and if they are NCI, I want to return the corresponding records from TABLE2 and TABLE3.
If TABLE1 has a record of NCI but there are no corresponding records in TABLE2 or TABLE3, then of course the columns for TABLE2 and 3 would be blank.
I can get all of the NCI records from TABLE1 when I LEFT JOIN with TABLE2, but when I try to specify TABLE3 in the FROM statement, only the records that are NCI in TABLE1 AND have data in TABLE2 are returned, not just all records with NCI in TABLE1.
Let me know if I can further clarify.
I know that you do not have access to my tables, but here is an example of my code so that you may understand my quandary further:
SELECT l.sku AS "SKU",
l.loc AS "LOC",
l.qty AS "QTY",
o.ncikey AS "NCI",
r.description AS "NCI DESC",
o.qtyexpected AS "NCI QTY EXP",
o.qtyreceived AS "NCI QTY REC",
o.loc AS "NCI LOC",
o.status
[Code]...
SQL & PL/SQL :: Merging Into Multiple Tables
Feb 12, 2012Can I use MERGE statement to insert the same thing into multiple tables?
Like below--
Merge into tbl1
using tbl2
when matched then update...
when not matched then insert all.
SQL & PL/SQL :: Selecting Via Multiple Tables?
Jan 19, 2011I need to calculate a list of people, who got some services more that 2 times with the same service koda (pas_kodas) to the same person (zmo_kodas). It should not depend on report number.
[URL]
What I get is in green (services are calculated more than 2 times BUT in the same report).
What I need is in red: calculate servises more that 2 times ACCROSS all reports to the same person (zmo_kodas).
[URL]
One person (zmo_kodas) can have a lot of reports (ats_nr). Every report can have one or more services (pas_kodas).
SQL & PL/SQL :: Trigger From Multiple Tables
May 1, 2013I need to set up a trigger to check and enforce that the age of an employee at hire date is older than 18 when a new record is inserted. The age is on the PERSON table, and the hire date is on the EMPLOYEE table.
My attempt:
CREATE TRIGGER AGE_HIRE
BEFORE INSERT OR UPDATE ON EMPLOYEE
FOR EACH ROW
BEGIN
IF FLOOR EXTRACT (YEAR FROM (PERSON.BIRTHDATE - EMPLOYEE.HIREDATE)) > 18
THEN DROP NEW
ELSE INSERT NEW
FROM PERSON, EMPLOYEE
END;
SQL & PL/SQL :: Joining Multiple Tables
Apr 4, 2010I tried to split the query into 2 parts , the outcome is fine but when ever i merge them ( include other table fields ) . The result is 3X3 rows .. it should be just 3 rows
The First Query ( Is running fine , 3 Rows returned )
SELECT
WORKORDER.WONUM,
WORKORDER.ESTSERVCOST,
WPM.ITEMNUM,
WPM.ITEMQTY,
[code]......
The Second Query ( Is running fine also, 3 Rows returned )
SELECT
WORKORDER.WONUM,
WORKORDER.ESTSERVCOST,
WP.DESCRIPTION,
WP.LINECOST
FROM WORKORDER , WPSERVICE WP
WHERE PARENT IS NULL
AND WORKORDER.WONUM = WP.WONUM
AND WORKORDER.WONUM = :WO
The problem occurs when i try to merge both these queries , as the result shows 9 rows ..
SELECT
WORKORDER.WONUM,
WORKORDER.ESTSERVCOST,
WPM.ITEMNUM,
WPM.ITEMQTY,
[code]......