Re: sequences, Ole and "invalid table name"

From: <codefragment_at_googlemail.com>
Date: Sun, 22 Jul 2007 01:10:41 -0700
Message-ID: <1185091841.793775.75710@m3g2000hsh.googlegroups.com>

This is how I've got it to work:

(1) Made a stored procedure to do this via dynamic sql

CREATE OR REPLACE PACKAGE "EXECANYTHING" AS

	  TYPE CUR IS REF CURSOR;
	PROCEDURE EXECANYTHING (pCUR1 OUT CUR,
		pCODE IN VARCHAR);

END EXECANYTHING;
/

CREATE OR REPLACE PACKAGE BODY "EXECANYTHING" AS

	PROCEDURE EXECANYTHING (pCUR1 OUT CUR,
		pCODE IN VARCHAR) IS
		BEGIN

BEGIN
OPEN pCUR1 FOR pCODE;

		END;
	END EXECANYTHING;
	END EXECANYTHING;

/

(2) Connection string looks like this, note the bit at the end

<connectionstring1 machine="mymachine"
type="Oracle">Provider=OraOLEDB.Oracle;Data Source=ORAC9I;User ID=me;Password=password;PLSQLRSet=1;</connectionstring1>

(3) Code looks (something) like this

// note the cursor is done automatially for you due to the PLSQLRSet bit in the connection string
OleDbCommand selectCommand = new OleDbCommand("{call ExecAnything.ExecAnything(?)}", dataConnection);

                        selectCommand.Parameters.Add("pCODE", OleDbType.VarChar, 100).Value = sqlString;

			OleDbDataReader reader = selectCommand.ExecuteReader();
			if (reader.Read())
			{
				object obj = reader[0];
				return (int)(Decimal)obj;
			}

(4) and the sqlstring is whatever you want, in my case

select MYSEQUENCE.nextval from dual

thanks for all your help Received on Sun Jul 22 2007 - 03:10:41 CDT