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?
johnportaldevAsked:
Who is Participating?
 
ajexpertCommented:
I have modifed your code to return the row count, also corrected the syntax errors...
Please find my comments inline
/* Formatted on 4/24/2011 12:25:36 AM (QP5 v5.115.810.9015) */
CREATE OR REPLACE 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'')';

   -- there has to be space before AND APP_NAME-- added space
   IF (app_name_in <> 'A')
   THEN
      v_sql := v_sql || ' AND APP_NAME = ''' || app_name_in || ''' ';  
   END IF;
   
   -- this is how rowcount should be returned
   EXECUTE IMMEDIATE v_sql INTO row_count  ;
   
   
END IIP_GET_TOTAL_ROW_COUNT;

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
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
0
 
slightwv (䄆 Netminder) Commented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
johnportaldevAuthor Commented:
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
0
 
slightwv (䄆 Netminder) Commented:
You use execute immediate to 'execute' the statement.  You 'return' the value by calling the procedure.

Which are you needing?
0
 
slightwv (䄆 Netminder) Commented:
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

0
 
slightwv (䄆 Netminder) Commented:
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
0
 
slightwv (䄆 Netminder) Commented:
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?
0
 
ajexpertCommented:
@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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.