| Select / update in a procedure [message #52434] |
Mon, 22 July 2002 01:01  |
Janna
Messages: 3
Registered: March 2002
|
Junior Member |
|
|
Hi,
I want to do the following in a procedure
From user1
1. Select some columns from table1,table2 table 3 with some where condition
2. Select some other columns from table4,table5 table6 with some where condition and join it with resultset of 1
3. Update resultset got in 2 set column5 = some select statement
Now I have to tranfer the result set in 3 to user2 some_table.
How can i achieve this?
Thanks,
Janna
|
|
|
|
| Re: Select / update in a procedure [message #52439 is a reply to message #52434] |
Mon, 22 July 2002 06:53  |
Sanjay Bajracharya
Messages: 279
Registered: October 2001
Location: Florida
|
Senior Member |
|
|
Your post needs a little more details ... still just by looking at it, your possible solution may be
1. Use UNION/UNION ALL if applicable
2. Use temp table to store the result sets, combine and then extract the outcome.
Good luck.
|
|
|
|
| Re: Select / update in a procedure [message #52441 is a reply to message #52439] |
Mon, 22 July 2002 07:05 |
Janna
Messages: 3
Registered: March 2002
|
Junior Member |
|
|
Sanjay,
Union all cannot be used. Let us say I have
1.emp table with eid,ename,salary,dept_id
2. dept table with dept_id,dept_name,dept_head
in user user1
Also in user user2 I have a table called emp_dept with eid,ename,salary,dept_id,dept_name
Now let us write a stored procedure as detailed below
1. select eid,ename,salary,dept_id, null from user1.emp
2. update recordset got in Step-1 set dept_name = select dept_name from user1.dept where
recordset got in step-1.dept_id = user1.dept_id
3. insert into user2.emp_dept set values as in recordset got in step-2
I do not want to create original tables b'cos each of procedures will have many such tables. Therefore I tried using globl temporary table. But it is also not working
create or replace PROCEDURE temp_test
AS
BEGIN
EXECUTE IMMEDIATE 'create global temporary table temp_test1 as (select * from dual)';
END;
procedure created
exec temp_test;
BEGIN temp_test; END;
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "USER!.TEMP_TEST", line 4
ORA-06512: at line 1
Referred the doc:
http://osi.oracle.com/~tkyte/Misc/RolesAndProcedures.html
I am not sure how to give priviledge granted dierctly to me?
Thanks & Regards,
Janna
|
|
|
|
| Re: Select / update in a procedure [message #52450 is a reply to message #52439] |
Mon, 22 July 2002 23:44 |
santosh
Messages: 85
Registered: October 2000
|
Member |
|
|
Hi Janna,
Can you be more specific with your problem as i can not get where exactly you are stuck up .Whether you want the syntax for the query or u got some problem while running your own query and plz check your second reply step 2 it is not very clear .
Thank you
Santosh
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
