Perfect, but what syntax do I need for the execute immediate command.
I want to open the ref cursor from my procedure and access it within
c# using an oledb connection.
CREATE OR REPLACE PROCEDURE xyz
(
accounts IN varchar2,
rs OUT SYS_REFCURSOR
)
IS
stmt_sql VARCHAR2(4000);
BEGIN
stmt_sql := ' SELECT * FROM table ' ||
' WHERE code in (' || accounts || ') AND next_code =
''x'' ';
EXECUTE IMMEDIATE stmt_sql INTO rs;
END
This compiles but does not work when running either from my oledb
connection or from sqlplus... I can return and open a ref cursor no
problem through compiled SQL but not with dynamic SQL which I need to
in this case as I am passing in a comma delimited list of "codes" to
check against.
Thanks
Jeremy.
On Jan 13, 5:46 pm, "Michael Moore" <[email protected]> wrote:
> check this outhttp://www.orafaq.com/wiki/REF_CURSOR
>
>
>
> On Tue, Jan 13, 2009 at 2:17 PM, Jer <[email protected]> wrote:
>
> > Hi,
> > This is hopefully a real easy question but I want to write a simple
> > stored procedure to open a return ref cursor based on dynamic sql...
> > Basically I want to do the following - but dont know the proper
> > syntax!
>
> > create or replace PROCEDURE "SP_GET_TRADES"
> > (
> > accounts IN varchar2,
> > rs IN OUT sys_refcursor
> > )
> > IS
> > stmt_sql VARCHAR2(4000);
>
> > BEGIN
> > stmt_sql := ' SELECT * FROM trades ' ||
> > ' WHERE p.acct_cd in (''1000'') AND a.sec_id = p.sec_id
> > ';
>
> > EXECUTE IMMEDIATE stmt_sql INTO rs;
> > END Sp_Account_Trades;
>
> > Used to doing this sort of thing in SQL Server and am very new to
> > Oracle and am finding it very different in how datasets are
> > returned...
>
> > Thanks in advance.
>
> > Jeremy.- Hide quoted text -
>
> - Show quoted text -
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
