We help IT Professionals succeed at work.

Return out parameter from a custom query

347 Views
Last Modified: 2013-12-07
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?
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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.

Author

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
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
You use execute immediate to 'execute' the statement.  You 'return' the value by calling the procedure.

Which are you needing?
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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?

Commented:
@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
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.