can we pass bind variable as in and out parameter in oracle stored procedure?
i have a query like below
insert into table_na(col1,col2,col3) select parameter1,col_name,parameter2) from table2 where condition?
How can i pass the in and out parameters(parameter1 and parameter2) in a table along with that tables column ?
it was possible in sybase like below
insert into table_na(col1,col2,col3) select @parameter1,col_name,@parametr2) from table2 where condition.
please help me with this query.Thanks a lot un adv!!!
I don't really understand what you're trying to do.
But yes, you can pass in out parameters to a procedure. And use them in a SQL statement. Provided you're using static SQL, these are bind variables.
create table t (
c1 int not null, c2 int not null
);
create or replace procedure p (
p1 in out int, p2 in out int
) as
begin
insert into t ( c1, c2 )
values ( p1, p2 ); -- p1 & p2 are bind variables
p1 := 2;
p2 := 2;
end p;
/
declare
v1 int := 1;
v2 int := 1;
begin
p ( v1, v2 );
dbms_output.put_line ( 'v1 = ' || v1 || '; v2 = ' || v2 );
end;
/
v1 = 2; v2 = 2
select * from t;
C1 C2
1 1