Link to home
Start Free TrialLog in
Avatar of johnportaldev
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?
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Execute it with what language/tool?

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','a','a','a',:mycount);

Print mycount
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.
Avatar of johnportaldev

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?
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

Open in new window

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
ASKER CERTIFIED SOLUTION
Avatar of ajexpert
ajexpert
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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?
@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