johnportaldev
asked on
Return out parameter from a custom query
I have a procedure constructed like below:
PROCEDURE IIP_GET_TOTAL_ROW_COUNT(
login_id_in IN VARCHAR2,
from_tm_in IN VARCHAR2,
to_tm_in IN VARCHAR2,
app_name_in IN VARCHAR2,
row_count out NUMBER)
AS
v_sql VARCHAR2(20000);
BEGIN
v_sql :='SELECT (select COUNT(*) FROM USER_TABLE WHERE ';
v_sql := v_sql || 'USER_TM BETWEEN TO_DATE('''|| from_tm_in ||''', ''MM/DD/YYYY'') AND ';
v_sql := v_sql || 'TO_DATE('''|| to_tm_in ||''', ''MM/DD/YYYY'')'
IF (app_name_in <> 'A')
THEN
v_sql := v_sql || 'AND APP_NAME = ''' || app_name_in || ''' ';
END IF;
//TO-DO
END IIP_GET_TOTAL_ROW_COUNT;
v_sql is having the complete query. How do i execute this procedure and return the row count?
PROCEDURE IIP_GET_TOTAL_ROW_COUNT(
login_id_in IN VARCHAR2,
from_tm_in IN VARCHAR2,
to_tm_in IN VARCHAR2,
app_name_in IN VARCHAR2,
row_count out NUMBER)
AS
v_sql VARCHAR2(20000);
BEGIN
v_sql :='SELECT (select COUNT(*) FROM USER_TABLE WHERE ';
v_sql := v_sql || 'USER_TM BETWEEN TO_DATE('''|| from_tm_in ||''', ''MM/DD/YYYY'') AND ';
v_sql := v_sql || 'TO_DATE('''|| to_tm_in ||''', ''MM/DD/YYYY'')'
IF (app_name_in <> 'A')
THEN
v_sql := v_sql || 'AND APP_NAME = ''' || app_name_in || ''' ';
END IF;
//TO-DO
END IIP_GET_TOTAL_ROW_COUNT;
v_sql is having the complete query. How do i execute this procedure and return the row count?
What is the rest of the procedure? I assume you eventuaLly do something with v_sql.
I think you'll have issues. I don't see spacing when you concatenate the strings together. You might introduce syntax errors.
I think you'll have issues. I don't see spacing when you concatenate the strings together. You might introduce syntax errors.
ASKER
I think i am looking for EXECUTE IMMEDIATE statement. How do i use EXECUTE IMMEDIATE statement to execute the query in v_sql string and return the count(*) as row_count
You use execute immediate to 'execute' the statement. You 'return' the value by calling the procedure.
Which are you needing?
Which are you needing?
Check the quick test below. YOu should change your code to use the bind variables and 'USING' syntax.
drop table tab1 purge;
create table tab1(col1 char(1));
insert into tab1 values('a');
insert into tab1 values('a');
insert into tab1 values('b');
commit;
create or replace procedure myproc(inCol1 in varchar2, outCount out number)
is
v_sql varchar2(20000);
begin
v_sql := 'Select count(*) from tab1 where ';
v_sql := v_sql || 'col1 = :myCol1';
execute immediate v_sql into outCount using inCol1;
end;
/
show errors
--test using sqlplus
var myCount number
exec myProc('a',:myCount);
print myCount
exec myProc('b',:myCount);
print myCount
I should add that the bind variables are positional not 'named'.
If you reuse a variable you need to repeat the name in the proper position.
In other words: say you use var1 twice and var2 once:
You need to do:
' Where col1=:a and col2=:a and col3=:a ' using :var1, var1, var2
If you reuse a variable you need to repeat the name in the proper position.
In other words: say you use var1 twice and var2 once:
You need to do:
' Where col1=:a and col2=:a and col3=:a ' using :var1, var1, var2
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Aj,
Why would you not use bind variables? It allows for sql reuse and helps protect from sql-injection.
When you add bind variables, then don't you have the code I posted?
Why would you not use bind variables? It allows for sql reuse and helps protect from sql-injection.
When you add bind variables, then don't you have the code I posted?
@slightwv:
Using bind variables will greately enhance performance, agree with your comment.
Though if we want to keep it simple (when performance is not really an issue) author can opt for my comment
Using bind variables will greately enhance performance, agree with your comment.
Though if we want to keep it simple (when performance is not really an issue) author can opt for my comment
I might rewrite it to use execute immediate and bind variables but that is just me.
I can help rewrite it later.
Using sqlplus:
Var mycount number
Exec IIP_GET_TOTAL_ROW_COUNT('a
Print mycount