Skip to Main Content
Forums

ODP.NET

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

VS 2010 Dataset Designer Oracle Parameters bind by name ODAC 11 R3

user7947900Feb 8 2011 — edited Sep 3 2013
We have 3-tier application, and we implement data layer using datasets.
Now we try to change from Net provider to ODAC (11 R3) with VS tools. We have a lot of tableadapter to regenerate with ODAC connection.
We use some "where" clauses like " tpo=:tpo or :tpo is null". Dataset designer show an error because ODAC set oracleCommand property bindbyname by default to false.
I complicate and too expensive work search all instances of tableadapter to set this property to true, and we cannot try sql (and results) from dataset designer.

Is there any way we could use designer like net driver?
Does exist any place to changes bindbyname property at designer generator?
Does oracle know issue to pach it in next realeases?

Comments

AnnEdmund

That is dynamic pivoting.. Check below..

GregV

Hi,

You can use a subquery for the pivot_in_clause only in conjunction with XML. See:

http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_10002.htm#CHDFAFIE

AnnEdmund

Try the below.. Not tested...

CREATE OR REPLACE FUNCTION dynamic_fromtable


RETURN SYS_REFCURSOR


AS


v_cursor SYS_REFCURSOR;


v_sql VARCHAR2(2000);


BEGIN


 SELECT 'SELECT *


        FROM(SELECT pre_sap_cod


             FROM pre)


        PIVOT(COUNT(pre_sap_cod) FOR pre_sap_cod IN('||(SELECT LISTAGG(pre_sap_cod,',') WITHIN GROUP(ORDER BY pre_sap_cod) FROM (SELECT distinct pre_sap_cod FROM pre))||'))'


        INTO v_sql        


 FROM DUAL;


 OPEN v_cursor FOR v_sql;


 RETURN v_cursor;


END;

Thanks,

Ann

1 - 3
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Oct 1 2013
Added on Feb 8 2011
#odp-net, #oracle
2 comments
963 views