Dynamic SQL and out field in oracle procedure

I get error when I use this:

PROCEDURE GET_BY_CRIT(vchFilter varchar2(500),
                      intCantTotal OUT INT,
                      curResult OUT sys_refcursor)
IS
BEGIN

    OPEN curResult FOR
    'SELECT COLUMN1,COLUMN2 FROM SOME_TABLE WHERE '||vchFilter

    EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM SOME_TABLE WHERE '||vchFilter 
    INTO intCantTotal 
END

Error:

ORA-00936: missed expression 

But when I execute each sentence by separate it run correcly.

Answers


The error you're getting doesn't seem to make sense. Oracle should be throwing a compilation error because parameters to functions don't have a length. vchFilter should be declared as a VARCHAR2, not a VARCHAR2(500).

Additionally, as Lolo pointed out in the comments, statements in a PL/SQL block need to be terminated with semicolons.

PROCEDURE GET_BY_CRIT(vchFilter varchar2,
                      intCantTotal OUT integer,
                      curResult OUT sys_refcursor)
IS
BEGIN
    OPEN curResult FOR
    'SELECT COLUMN1,COLUMN2 FROM SOME_TABLE WHERE '||vchFilter;

    EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM SOME_TABLE WHERE '||vchFilter 
    INTO intCantTotal;
END;

Be aware as well that there is no guarantee that second SQL statement will see the same COUNT that the first SQL statement did unless you can guarantee that SOME_TABLE is not being modified by any other sessions at the same time you're querying it. I'd generally be rather wary of a need to run a query and execute a separate count-- that generally indicates a more basic problem. If you need the COUNT to be consistent with the query you're running, you'd want to add an analytic COUNT to your query and let the caller fetch that column.

PROCEDURE GET_BY_CRIT(vchFilter varchar2,
                      curResult OUT sys_refcursor)
IS
BEGIN
    OPEN curResult FOR
    'SELECT COLUMN1,COLUMN2, COUNT(*) OVER () cnt FROM SOME_TABLE WHERE '||vchFilter;
END;

Need Your Help

Java “trick”, redefining daughter class member

java inheritance

I'm training for a Java exam, and I've come across something I don't understand in last year subject. Here is the code

FBUserSettingsViewController crashes on dismiss just on ios 6

iphone objective-c facebook ios6

I'm trying to use FBUserSettingsViewController but it's crashing every time I try to dismiss it. I use the following code to display it:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.