.NET to Oracle issue

  • Thread starter Cowboy \(Gregory A. Beamer\)
  • Start date
C

Cowboy \(Gregory A. Beamer\)

Problem:
1. Listboxes on page
2. User selects multiple values
3. Pass multiple values to Oracle sproc and get a return DataTable

Portions solved
1. Using ref_cursors (in sproc) and the OracleClient namespace, can return
DataTables
2. Know how to loop through CSV and create Oracle Table Type (if this is
best option)

Potential Solution Ideas

1. Pass in Oracle Table Type. Problem: Cannot use Oracle created .NET
provider, have to use Microsoft OracleClient. This does not support passing
in Table Type.

2. SELECT from the table type (NOTE: convention not mine)

--DECLARATIONS
TYPE sr_typ IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
v_fac_code sr_type;
v_count NUMBER;

-- Load Oracle Table (value converted to numeric
v_count := number_of_comas(FACIN) + 1;
FOR i IN 1 .. v_count LOOP
v_fac_code(i) := nvl(loc_fld_value(FACIN, i));
END LOOP;

-- This bombs
SELECT * FROM Table1 WHERE x IN v_fac_code

There must be a way to do this, but I do not know the Oracle conversion.

3. Oracle guy suggested just sending in the string for my in clause

FACIN IN VARCHAR2

--and

SELECT * FROM Table1WHERE x IN FACIN

But, this bombs also. As you cannot do this in SQL Server either, I am not
surprised.

4. ???? ANY IDEAS ????

___________________
If I cannot solve this quickly, I will end up embedding all of my SQL in my
ASP.NET app (which is what everyone around here does already, so the fact it
is crappy will fly over everyone's head). I am loathe to that idea. If this
were SQL Server, the report would already be running.

One of the theories above has to work, I am not sure of which one.


--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

**********************************************************************
Think Outside the Box!
**********************************************************************
 
M

Miha Markic

Hi Gregory,

Interesting question, indeed.

Cowboy (Gregory A. Beamer) said:
Problem:
1. Listboxes on page
2. User selects multiple values
3. Pass multiple values to Oracle sproc and get a return DataTable

Portions solved
1. Using ref_cursors (in sproc) and the OracleClient namespace, can return
DataTables
2. Know how to loop through CSV and create Oracle Table Type (if this is
best option)

Potential Solution Ideas

1. Pass in Oracle Table Type. Problem: Cannot use Oracle created .NET
provider, have to use Microsoft OracleClient. This does not support passing
in Table Type.

2. SELECT from the table type (NOTE: convention not mine)

--DECLARATIONS
TYPE sr_typ IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
v_fac_code sr_type;
v_count NUMBER;

-- Load Oracle Table (value converted to numeric
v_count := number_of_comas(FACIN) + 1;
FOR i IN 1 .. v_count LOOP
v_fac_code(i) := nvl(loc_fld_value(FACIN, i));
END LOOP;

-- This bombs
SELECT * FROM Table1 WHERE x IN v_fac_code

There must be a way to do this, but I do not know the Oracle conversion.

Neither do I. I guess this method is for defense against code injection.
Since v_fac_code is some sort of table, maybe SELECT * FROM Table1 WHERE x
IN (SELECT * FROM v_fac_code) might work?
Just wild guessing here.

3. Oracle guy suggested just sending in the string for my in clause

FACIN IN VARCHAR2

--and

SELECT * FROM Table1WHERE x IN FACIN

But, this bombs also. As you cannot do this in SQL Server either, I am not
surprised.

Me neither :)
4. ???? ANY IDEAS ????

I went with building dynamic sql strings...
Not that I am saying that this is an ideal solution, but it works...
However, if I would have to chose again I would consider No.3.
 
C

Cowboy \(Gregory A. Beamer\)

Tried #3 and it does not work, as expected. We have a communication problem
due to language barrier, so I probaby misunderstood him.

I may have to build dynamic strings in the app, but that goes against my
grain.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

**********************************************************************
Think Outside the Box!
**********************************************************************
 
M

Miha Markic

Hi,

Cowboy (Gregory A. Beamer) said:
Tried #3 and it does not work, as expected. We have a communication problem
due to language barrier, so I probaby misunderstood him.

I may have to build dynamic strings in the app, but that goes against my
grain.

If everything was perfect ;-)
Anyway, it is not clear at all to me, why this problem is not solved in a
friendly way by database itself...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top