how to create and execute a procedure dynamically having schema name and table name as in parameter in oracle

Question

Update statement using dyanmic sql by passing schema name and table name as in parameter to a stored procedure. Now it is working.

create or replace procedure p11(v_sal in number,v_schema varchar2,v_tab varchar2)
v_sql varchar2(100);
is
begin
v_sql:='update '|| v_schema||'.'||v_tab||' set sal= '||v_sal||' where empno= :1 ';
dbms_output.put_line(v_sql);
Execute immediate v_sql using 7839;
end;

Include the error! The update syntax is wrong. Try this for troubleshooting: create a varchar2 variable, and set it equal to the update statement you are building, then display it via dbms_putput.put_line before trying to run it. Verify the syntax is correct. You have some errors in how you are building the string. — Gary_W, Aug 19 '15 at 15:39
you should avoid this: ' set sal= '||v_sal||' where empno= :1 '. v_sal, if contains decimal digits will be converted to a string using TO_CHAR. this is ambiguos: it strongly depends on the national settings! that code in a lot of countries of europe would not work: it would generate commas for decimal separators, not points. — Carlo Sirna, Aug 27 '15 at 19:29

score 1 · Answer 1 · answered Aug 19 '15 at 15:36

1

You should consider spaces at least.

create or replace procedure p11(v_sal in number,v_schema varchar2,v_tab varchar2)
is
begin
Execute immediate 'update '|| v_schema.v_tab||' set sal='||v_sal||' where empno= :1' using 7839;
end;

answered Aug 19 '15 at 15:36

Simimmo

658
1
6
15

score 0 · Answer 2 · answered Aug 19 '15 at 17:12

0

This procedure errors not withstanding is a potential source of SQL Injection attacks. It should be rewritten to avoid building the dynamic query directly from the input values. Instead there should be some form of validation of the input.

Here's one potential way to rewrite the procedure:

create or replace procedure p11(v_sal in number,v_schema varchar2,v_tab varchar2)
is
 type tab_t is record( owner ALL_TABLES.OWNER%type
                     , table_name ALL_TABLES.TABLE_NAME%type);
 tab tab_t;
begin
  -- Ensure table and schema are valid by selecting from all_tables
  select owner, table_name
    into tab
    from all_tables
   where owner = v_schema and table_name = v_tab;

  Execute immediate 
  -- Use the returned values from above in building the query
  'update '|| tab.owner||'.'||tab.table_name
  -- use a bind for the new salary instead of concatenation
  ||' set sal=:new_sal where empno= :1' using v_sal, 7839;
end;

answered Aug 19 '15 at 17:12

Sentinel

6,379
1
18
23

Thanks it worked.But why we should create a record type rather then directly passing the schema name as in parameter. Can we do that without touching the ALL_TABLES. I want to do it just by passing a name as schema and table which is known. – Debasish Aug 19 '15 at 17:59
Consider the following call to your procedure `p11(0,'hr.employees set salary=5000000 where 1=1 or :1 is not null or :2 is not null --','anytable');` If the SQL were constructed directly from the parameters in this call v_sal and your hard coded empno would be effectively ignored and it would set every employee's salary to 5000000 in the hr.employees table. As far as the record type is concerned, you don't need it. I could have declared two variables to select into instead of 1 record type with two attributes. – Sentinel Aug 19 '15 at 18:20
1

You could also use `dbms_assert` rather than coding your own query against `all_tables`. Under the covers, that's likely doing basically the same thing. But `dbms_assert` is probably a bit more efficient (it doesn't need to hit all the `x$` objects that `all_tables` does) and will hopefully be a bit more consistent across sites http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_assert.htm – Justin Cave Aug 19 '15 at 18:46
@JustinCave true, but if I try to use `dbms_assert` to ensure that a specific object exists in a different schema than mine, with `dbms_assert.sql_object_name('NOTME.SOME_TABLE')` it fails even if I have full access to the object whereas a query against all_tables works. – Sentinel Aug 19 '15 at 22:19

score 0 · Answer 3 · answered Aug 19 '15 at 18:09

0

Check the package DBMS_SQL to dynamically execute the query.Steps are as follow. open the cursor parse bind the variable
execute close the cursor

answered Aug 19 '15 at 18:09

Aki

1

Try this way. CREATE OR REPLACE PROCEDURE test1(v_sal in number,v_schema varchar2,v_tab varchar2) AS cursor1 INTEGER; rows1 INTEGER; BEGIN cursor1 := dbms_sql.open_cursor; DBMS_SQL.PARSE(cursor1, 'update '|| v_schema||'.'||v_tab||' set salary= '||v_sal||' where employee_id= :1 ',DBMS_SQL.NATIVE); DBMS_SQL.BIND_VARIABLE(cursor1, ':1', 7839); rows1 := DBMS_SQL.EXECUTE(cursor1); DBMS_SQL.CLOSE_CURSOR(cursor1); END; – Aki Aug 19 '15 at 18:47

how to create and execute a procedure dynamically having schema name and table name as in parameter in oracle

3 Answers3