Home » SQL & PL/SQL » SQL & PL/SQL » problem with insert in procedure !!!!!
problem with insert in procedure !!!!! [message #232048] Thu, 19 April 2007 03:25 Go to next message
cefb
Messages: 56
Registered: March 2007
Member

hi there!!!!!!
i have a procedure with an insert
insert into teste (select ....)


statement and then a select from the table i made the insert returning the values to a cursor. the problem is the insert doesn't work. If i make only the insert in sql plus it works fine but in procedure the cursor returns no values.
Another thing is if i login in with sys user it works fine.

im logging in with a user with DBA grant. I think it can be a privilege problem, but it's strange because i'm DBA user.


thanks

Re: problem with insert in procedure !!!!! [message #232055 is a reply to message #232048] Thu, 19 April 2007 04:05 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If it were a privilege problem, you'd get an error message.

Can you show us some code?
Re: problem with insert in procedure !!!!! [message #232061 is a reply to message #232048] Thu, 19 April 2007 04:21 Go to previous messageGo to next message
cefb
Messages: 56
Registered: March 2007
Member
PROCEDURE TESTE
as
my_cursor sys_refcursor;
a varchar(100);
b varchar(100);
c varchar(100);
begin
  open my_cursor for select ac.owner,ac.constraint_name,ac1.table_name 
    from all_constraints ac inner join all_constraints ac1 
    on ac.r_constraint_name=ac1.constraint_name;
    fetch my_cursor into a,b,c;
    while my_cursor%FOUND
    loop
    dbms_output.put_line(a || ' - ' || b || ' - '||c);
    fetch my_cursor into a,b,c;
    end loop;
end;


the problem is all_constraints is only returning user_constraints. if i make this select statement outside the procedure i get all constraints

[Updated on: Thu, 19 April 2007 04:26]

Report message to a moderator

Re: problem with insert in procedure !!!!! [message #232063 is a reply to message #232061] Thu, 19 April 2007 04:26 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
If you try it in SQL*Plus after you issue the following command:
SET ROLE NONE


What happens?

MHE

PS: VARCHAR should be VARCHAR2 and make sure all cursors are closed in the end.
Re: problem with insert in procedure !!!!! [message #232064 is a reply to message #232061] Thu, 19 April 2007 04:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Roles are disabled in procedure/package unless you defined it with current user privilege: "authid current_user" after procedure/package name.

Regards
Michel


Re: problem with insert in procedure !!!!! [message #232066 is a reply to message #232048] Thu, 19 April 2007 04:36 Go to previous messageGo to next message
cefb
Messages: 56
Registered: March 2007
Member
the same problem with both solutions.
Re: problem with insert in procedure !!!!! [message #232067 is a reply to message #232048] Thu, 19 April 2007 04:39 Go to previous messageGo to next message
cefb
Messages: 56
Registered: March 2007
Member
another thing i think it may cause the problem, i create my user using system account.

create user xxx;
grant DBA to xxx;



Re: problem with insert in procedure !!!!! [message #232071 is a reply to message #232066] Thu, 19 April 2007 04:45 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
cefb wrote on Thu, 19 April 2007 11:36
the same problem with both solutions.


I did not offer you a solution (yet). I wanted you to run the select from the SQL*Plus prompt after you disabled all roles (through the SET ROLE NONE command). You granted DBA to your user. "DBA" in this case is a role. Reread Michel's reply.

MHE
Re: problem with insert in procedure !!!!! [message #232072 is a reply to message #232067] Thu, 19 April 2007 04:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, the problem is not who creatd the user but that you only granted a role (DBA) and not direct privileges (if you don't use the option I previously posted).

Regards
Michel
Re: problem with insert in procedure !!!!! [message #232074 is a reply to message #232048] Thu, 19 April 2007 04:53 Go to previous message
cefb
Messages: 56
Registered: March 2007
Member

it worked. Sorry for all my confusion.



Thanks you all;
Previous Topic: sql text
Next Topic: can anyone help me in solving this query
Goto Forum:
  


Current Time: Sat Dec 10 07:21:47 CST 2016

Total time taken to generate the page: 0.04073 seconds