Home » SQL & PL/SQL » SQL & PL/SQL » procedure accept insert into query as parameter
procedure accept insert into query as parameter [message #224439] Wed, 14 March 2007 05:25 Go to next message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
Hi Everybody,
can i write a procedure that accept an Insert Into query as parameter and also another parameters that work as by pass parameters to same query??

for example:
-----------
create or replace procedure Pr_foo(p_sql varchar2,p_parm1 varchar2,p_parm2 Number)
as
Begin
Execute Immediate p_sql using p_parm1,p_parm2;
End;
/
and then i declare the query like this:
Declare
l_parm1 varchar2(20);
l_parm2 Number;
sql varchar2(4000);
begin
sql:='Insert into foo1
select l_parm1,l_parm2 from foo;

Update foo1 set nr=l_parm1;';

Pr_foo('sql','A',1);
Pr_foo('sql','B',2);
Pr_foo('sql','C',3);
Pr_foo('sql','D',4);
Pr_foo('sql','E',5);
end;
/

this Model does not work ,Do you have a better solution??

Thanks in Advance,
bahaa

Re: procedure accept insert into query as parameter [message #224445 is a reply to message #224439] Wed, 14 March 2007 05:34 Go to previous messageGo to next message
martijn
Messages: 283
Registered: December 2006
Location: Netherlands
Senior Member
I think you have to look at the package dbms_sql.
This package provides the abality to perform dynamic sql.

personal note:
Take care with this type of solution. If to many users or roles have execute privilege on the procedure a lot of people can do a lot of harm, since you can't control what they are actually executing.
(anyway.... maybe I'm way to paranoid. The exercise to get it to work is nice though Smile)
Re: procedure accept insert into query as parameter [message #224449 is a reply to message #224445] Wed, 14 March 2007 05:40 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
So, you want to execute dynamic sql by means of a dynamic sql call...
Could you tell us what your actual problem is? I get the idea you are stuck searching in a direction you do NOT want to go...
Re: procedure accept insert into query as parameter [message #224454 is a reply to message #224449] Wed, 14 March 2007 06:04 Go to previous messageGo to next message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
Hi Everybody,
The main problem is the following:
i have an insert into query and update query that must frequentlly executed by using variant values as parameters..
To solve this problem we thought it is beter way to create a procedure that can hold that multiple execution each time the parameters change,The whole Code is as follows :
------------------------------------------------
CREATE OR REPLACE PROCEDURE Pr_executesqlOra
(p_sqlcmd VARCHAR2,p_wgroot VARCHAR2,p_catelog varchar2,p_usergroup Number,p_accesslevel Number,p_accesstype Number)
IS
BEGIN
EXECUTE IMMEDIATE p_sqlcmd Using p_wgroot,p_catelog ,p_usergroup ,p_accesslevel ,p_accesstype ;
END Pr_executesqlOra;
/
DECLARE
l_wgroot VARCHAR2(50);
l_usergroup PLS_INTEGER;
sqlcmd nvarchar2(4000);
BEGIN
l_wgroot:='123456789';
sqlcmd:='
insert into csUserGroupAccessLevel
select l_usergroup, c.Nr, l_accesstype, l_accesslevel
from Contents c
join contenttypes ct on c.ContentType= ct.Nr
join csWorkGroupItemCategories wic on c.CTSpecificInt4= wic.Nr
where wic.Code=l_category and ct.Code=''CSM''
and c.Nr in (select nr from Table(fn_recursechildren(l_wgroot,0)))
and not exists (select 1 from csUserGroupAccessLevel nugal where nugal.nrUserGroup=l_usergroup and nugal.nrContents=c.Nr and nugal.AccessType=l_accesstype);

update csUserGroupAccessLevel
set AccessLevel=l_accesslevel
from Contents c
join contenttypes ct on c.ContentType= ct.Nr
join csWorkGroupItemCategories wic on c.CTSpecificInt4= wic.Nr
where wic.Code=l_category and ct.Code=''CSM''
and c.Nr in (select nr from Table(fn_recursechildren(l_wgroot,0)))
and nrUserGroup=l-usergroup and nrContents=c.Nr and AccessType=l_accesstype;';
DBMS_OUTPUT.PUT_LINE('AccessLevel set for ''1'' record(s)');


--Set accesslevels for Workgroup Administrators
select nr INTO l_usergroup from Usergroups where name='Workgroup Administrators';
IF LENGTH(l_usergroup)=0 THEN
DBMS_OUTPUT.PUT_LINE('Usergroup Workgroup Administrators not found, skipping insert for this group!');
ELSE
DBMS_OUTPUT.PUT_LINE('Setting accesslevels for Workgroup Administrators');
END if;


Pr_executesqlOra('sqlcmd', l_wgroot , 'ACCESSRIGHT',l_usergroup,1,100);
Pr_executesqlOra('sqlcmd', l_wgroot , 'ACCESSRIGHT',l_usergroup,2,100);
Pr_executesqlOra('sqlcmd', l_wgroot , 'ACCESSRIGHT',l_usergroup,3,100);
Pr_executesqlOra('sqlcmd', l_wgroot , 'ACCESSRIGHT',l_usergroup,4,100);
Pr_executesqlOra('sqlcmd', l_wgroot , 'ACCESSRIGHT',l_usergroup,5,100);
Pr_executesqlOra('sqlcmd', l_wgroot , 'ACCESSRIGHT',l_usergroup,6,100);
Pr_executesqlOra('sqlcmd', l_wgroot , 'ACCESSRIGHT',l_usergroup,7,100);
Pr_executesqlOra('sqlcmd', l_wgroot , 'AVATAR',l_usergroup,1,100);
.
.
.
.
.
.
.
.......This may reach 100 line code.

END;
/

This is the problem ..Do you have something in mind?

Thanks in Advance,
bahaa

Re: procedure accept insert into query as parameter [message #224465 is a reply to message #224454] Wed, 14 March 2007 06:58 Go to previous messageGo to next message
dmitry.nikiforov
Messages: 723
Registered: March 2005
Senior Member
The first thing is what EXECUTE IMMEDIATE doesn't execute "composite" statements like you did. If you want to execute several statements at a time you have to put them
into PL/SQL block like 'BEGIN <<insert statement>>; <<update
statement>>; END;'. Consider too what EXECUTE IMMEDIATE treats
bind variables in SQL statement by positions and in PL/SQL - by names.

The next problem is your particular solution now is the nice rabbit hole in the Oracle security because of SQL injection. Why can't you put
static SQL in your procedure and use parameters ?

Rgds.
Re: procedure accept insert into query as parameter [message #224480 is a reply to message #224465] Wed, 14 March 2007 07:21 Go to previous messageGo to next message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
Thank you for your replay,
You wrote:
-------------
The first thing is what EXECUTE IMMEDIATE doesn't execute "composite" statements like you did. If you want to execute several statements at a time you have to put them
into PL/SQL block like 'BEGIN <<insert statement>>; <<update
statement>>; END;'.

Ok..

"Consider too what EXECUTE IMMEDIATE treats
bind variables in SQL statement by positions and in PL/SQL - by names."

Sorry what do you mean about that??



"The next problem is your particular solution now is the nice rabbit hole in the Oracle security because of SQL injection. Why can't you put
static SQL in your procedure and use parameters ?"

i could not understand the relation with 'SQL injection'..Could you be more exact.

Thanks in Advance,
bahaa

Re: procedure accept insert into query as parameter [message #224488 is a reply to message #224480] Wed, 14 March 2007 07:33 Go to previous messageGo to next message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
I think i understand now what you mean with your replay..

Thanks in Advance,
bahaa
Re: procedure accept insert into query as parameter [message #224491 is a reply to message #224480] Wed, 14 March 2007 07:46 Go to previous messageGo to next message
dmitry.nikiforov
Messages: 723
Registered: March 2005
Senior Member
>>Sorry what do you mean about that??

Look. For SQL statements no matter what name bind variables have. Their ordered (left to right) positions and number are important only:

SQL> declare
  2   v_into integer;
  3  begin
  4   execute immediate 'select 1 from dual where 1=:1 and 1=:1' into v_into using 1;
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-01008: not all variables bound
ORA-06512: at line 4


SQL> declare
  2   v_into integer;
  3  begin
  4   execute immediate 'select 1 from dual where 1=:1 and 1=:1' into v_into using 1,1;
  5  end;
  6  /

PL/SQL procedure successfully completed.


In PL/SQL bind variable name and it's first occurance position is important:

SQL> begin
  2   execute immediate 'declare v_into integer; begin select 1 into v_into 
  3   from dual where 1=:1 and 2=:2 and 1=:1; end;' using 1,2,1;
  4  end;
  5  /
begin
*
ERROR at line 1:
ORA-01006: bind variable does not exist
ORA-06512: at line 2


SQL> begin
  2   execute immediate 'declare v_into integer; begin select 1 into v_into 
  3   from dual where 1=:1 and 2=:2 and 1=:1; end;' using 1,2;
  4  end;
  5  /

PL/SQL procedure successfully completed.


>>i could not understand the relation with 'SQL >>injection'..Could you be more exact.

Suppose you grant the user to execute the procedure but not
grant him/her to delete from T table.
Your approach makes the security miserable.

SQL> conn / as sysdba
Connected.
SQL> create user user1 identified by user1;

User created.

SQL> grant create session to user1;

Grant succeeded.

SQL> conn scott/tiger
Connected.

SQL> create table t (id number);

Table created.

SQL> create or replace procedure Pr_foo(p_sql varchar2,p_parm1 varchar2,p_parm2 Number)
  2  as
  3  Begin
  4  Execute Immediate p_sql using p_parm1,p_parm2;
  5  End;
  6  /

Procedure created.

SQL> grant execute on Pr_foo to user1;

Grant succeeded.

SQL> insert into t values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t;

        ID
----------
         1

SQL>  conn user1/user1
Connected.
SQL> delete from scott.t;
delete from scott.t
                  *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> exec scott.pr_foo('delete from t where 1=:1 and 2=:2',1,2)

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> conn scott/tiger
Connected.
SQL> select * from t;

no rows selected


Hope I'm exact.

You shoud study Oracle concepts and read about
execution rights of SQL inside procedures.

Rgds.

Re: procedure accept insert into query as parameter [message #224507 is a reply to message #224491] Wed, 14 March 2007 08:25 Go to previous message
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
Member
Thanks to you ..that was clear.

Thanks in Advance,
bahaa
Previous Topic: Invalid Identifier - Oracle Error
Next Topic: sql help
Goto Forum:
  


Current Time: Sun Dec 11 08:34:15 CST 2016

Total time taken to generate the page: 0.20063 seconds