Oracle dynamic run error

Question

I created a procedure with dynamic sql,but cannot run it successfully.

create or replace procedure getdata(string par1, results out cursor)
as
declare sqlBase varchar2(100);
begin
    sqlBase := 'open '||results|| ' for select * from studetns';
end;

After running, the following error message pops up:

PLS-00306, wrong number or types of arguments in call to '||'

I just need to filter data by some parameters ,but some parameters may be null or empty, so I need to filter dynamic. like if(par1 is not null) then ........ so here I need to use dynamic sql. in C# programe, use cursor to return result. like here ,I use cursor type to open select statements.

but in sql editor, I get right sql statement.

Can Somebody help me with this?

Alen Oblak · Accepted Answer · 2013-11-22 15:07:34Z

2

Your syntax is a little bit wrong. Try with this:

create or replace procedure getdata(par1 varchar2, par2 varchar2, results out sys_refcursor)
as
begin
   open results for
      select *
      from   students
      where  name = nvl(par1, name)
      and    surname = nvl(par2, surname);
end;

Why do you need parameter par1? Better to use PL/SQL type varchar2, not string. They work the same, but varchar2 is a base data type, while string is a subtype of it.

edited Nov 22, 2013 at 15:07

answered Nov 22, 2013 at 11:18

Alen Oblak

3,32515 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Alen Oblak Over a year ago

You are right, thanks for that. string is the same as varchar2.

chengdu.jack Over a year ago

I need to use par to filter data. there are more than one parameters. here ,I just list one parameter. use cursor to return select result.

Alen Oblak Over a year ago

Then just add where name = par1 to the select statement.

chengdu.jack Over a year ago

but the par1 maybe is null, then I don't need to use it to filter. if have par2 is not null ,then use par2 to filter, then have par3, par4.......

Alen Oblak Over a year ago

I edited the answer to include par2 and the logic if the parameter can be null. If you have more parameters, just add them in the procedure header and in the select statement.

|

Armunin · Accepted Answer · 2013-11-22 11:58:26Z

0

Depending on what you want to achieve, I would try something like that:

create or replace procedure getdata(par1 varchar2, results out sys_refcursor)
as

sqlBase varchar2(100);

begin
    sqlBase := 'begin open :X for select * from students;end;';
    execute immediate sqlbase USING IN OUT results;
end;

You can't concatenate a cursor into a string as you tried it, that's where your error came from.

But if you could clearify your question we could help you better.

edited Nov 22, 2013 at 11:58

answered Nov 22, 2013 at 11:32

Armunin

9967 silver badges18 bronze badges

3 Comments

chengdu.jack Over a year ago

I just want to use some paramters to filter data, but the parameters is option to select ,if par1 is null ,then don't use it to filter.if par2 is not null,then use par2, then the same as par3, par4, par5 .....

Armunin Over a year ago

Ok, I think you don't need dynamic SQL. Just use the answer of @AlenOblak

chengdu.jack Over a year ago

Yes. Now, I thinks @AlenOblak solution is OK, thanks

Collectives™ on Stack Overflow

Oracle dynamic run error

2 Answers 2

10 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related