HOWTO Run multiple SQL statements from ASP/ADO to an Oracle 10g.

[ATS]

HOWTO Run multiple SQL statements from ASP/ADO to an Oracle 10g.

Please help,

I'm trying to write an ASP page to use ADO to run a long query against an
Oracle 10g database, to create tables, if they do not already exist. In terms
of ASP/ADO, that would be fine in a SQL Server Sense by a simply
ASP/Server-Side JavaScript as such:

var cnTemp = Server.CreateObject("ADODB.Connection");
cnTemp.Open("~~~~String for DSN~~~~");
rsTemp = cnTemp.Execute("~~~~ LONG Query ~~~~");

while (rsTemp.EOF != true)
{
~~~~~ Get data ~~~~~~
rsTemp.MoveNext();
}

In terms of the query for SQL Server it would look something like this:

if exists(~~~ MY TABLE)
begin
select 'TABLE ALREADY EXISTS' as 'RET'
end
else
begin
create table ~~~~~~
select 'TABLE CREATED' as 'RET'
end

From this, the Connection object in ADO works GREAT. The returned Recordset
object will have a single row from the multiple statements in that query
returned as the single column called 'RET'.

I need to do the same in Oracle 10g. I need to be able to run many
statements inside a single ORacle query, and have it return Recordset
compatible data, with a single column I define, like the 'RET'. But I do not
know how to do this. As best I can determine, in ORacle I would use something
like this:

SET SERVEROUTPUT ON;
declare
if not exists (~~~~ My Table)
then
create table ~~~~~;
DBMS_OUTPUT.PUT_LINE('TABLE CREATED' ;
else
DBMS_OUTPUT.PUT_LINE('TABLE ALREADY EXISTS' ;
end if;
end;

The first problem is that the PUT_LINE command does not let me have an
option to name the column as I desire. The 2nd problem is that to ORacle via
the ADO Connection object, I get an error as such on the "SET SERVEROUTOUT
ON" statement as such:

undefined(i = 0) - [Microsoft][ODBC driver for Oracle][Oracle]ORA-06550:
line 3, column 5: PL/SQL: ORA-00922: missing or invalid option ORA-06550:
line 3, column 1: PL/SQL: SQL Statement ignored

If I run the query in PL/SQL, it works, (albeit, the output returned does
not let me name the column for the PUT_LINE as I desire.

So, with all that said, how can I do this?

The keys are:

#1 A single LARGE query with multiple statements.
#2 Returns rows of data that ADO-Recordset can access.
#3 Lets one set the column names as desired.
#4 Lets me run complicated IF-THEN statements.
#5 Lets me create tables, indexes, execute grants, and create users.
#6 Same coding for SQL Server with ASP/ADO works for Oracle. IE, SOMETHING
has to happen inside the Oracle query to make it work. AND/OR because the
database is Oracle, we execute some simple QUERIES from an ADO-Connection
object ahead of time.
#7 Under no circumstance can the ADO-Command object be used.

 

[Bob Barrows [MVP]]

Why don't you use a stored procedure? You're making too many trips to the
database using straight ADO queries.

HOWTO Run multiple SQL statements from ASP/ADO to an Oracle 10g.

Please help,

I'm trying to write an ASP page to use ADO to run a long query
against an Oracle 10g database, to create tables, if they do not
already exist. In terms of ASP/ADO, that would be fine in a SQL
Server Sense by a simply ASP/Server-Side JavaScript as such:

var cnTemp = Server.CreateObject("ADODB.Connection");
cnTemp.Open("~~~~String for DSN~~~~");
rsTemp = cnTemp.Execute("~~~~ LONG Query ~~~~");

while (rsTemp.EOF != true)
{
~~~~~ Get data ~~~~~~
rsTemp.MoveNext();
}

In terms of the query for SQL Server it would look something like
this:

if exists(~~~ MY TABLE)
begin
select 'TABLE ALREADY EXISTS' as 'RET'
end
else
begin
create table ~~~~~~
select 'TABLE CREATED' as 'RET'
end

From this, the Connection object in ADO works GREAT. The returned
Recordset object will have a single row from the multiple statements
in that query returned as the single column called 'RET'.

I need to do the same in Oracle 10g. I need to be able to run many
statements inside a single ORacle query, and have it return Recordset
compatible data, with a single column I define, like the 'RET'. But I
do not know how to do this. As best I can determine, in ORacle I
would use something like this:

SET SERVEROUTPUT ON;
declare
if not exists (~~~~ My Table)
then
create table ~~~~~;
DBMS_OUTPUT.PUT_LINE('TABLE CREATED' ;
else
DBMS_OUTPUT.PUT_LINE('TABLE ALREADY EXISTS' ;
end if;
end;

The first problem is that the PUT_LINE command does not let me have an
option to name the column as I desire. The 2nd problem is that to
ORacle via the ADO Connection object, I get an error as such on the
"SET SERVEROUTOUT ON" statement as such:

undefined(i = 0) - [Microsoft][ODBC driver for
Oracle][Oracle]ORA-06550: line 3, column 5: PL/SQL: ORA-00922:
missing or invalid option ORA-06550: line 3, column 1: PL/SQL: SQL
Statement ignored

If I run the query in PL/SQL, it works, (albeit, the output returned
does not let me name the column for the PUT_LINE as I desire.

So, with all that said, how can I do this?

The keys are:

#1 A single LARGE query with multiple statements.
#2 Returns rows of data that ADO-Recordset can access.
#3 Lets one set the column names as desired.
#4 Lets me run complicated IF-THEN statements.
#5 Lets me create tables, indexes, execute grants, and create users.
#6 Same coding for SQL Server with ASP/ADO works for Oracle. IE,
SOMETHING has to happen inside the Oracle query to make it work.
AND/OR because the database is Oracle, we execute some simple QUERIES
from an ADO-Connection object ahead of time.
#7 Under no circumstance can the ADO-Command object be used.

 

[Bob Barrows [MVP]]

#7 Under no circumstance can the ADO-Command object be used.

This is a ridiculous requirement. ALL queries run by ADO are executed via a
Command object: whether you explicitly create it yourself or ADO creates it
implicitly.

 

[ATS]

Thanks for the reply.
Sorry if #7 seems ridiculous, but it is absolutely necessary, or we will
have to do a major rewrite. Not that anything related to Oracle requires
anything less, but surely others have used ADODB.Connection.Execute to build
schemas and launch complicated queries in Oracle.

Basically, by using the ADODB.Connection object, we can use its "Execute"
method to run any query against SQL Server, including complex schema building
queries which contain many statements, and return rowsets with columns we
define on the fly.

Literally we invoke the ADO like this:

var cnTemp = Server.CreateObject("ADODB.Connection");
var csDSN = "~~~ DSN STRING ~~~";
cnTemp.Open(csDSN);
var rsTemp = cnTemp.Execute("~~~ BIG COMPLEX QUERY ~~~~");
var csRET;

if (!rsTemp.EOF)
{
csRET = rsTemp("RET");
}
else
{
csRET = "ERR";
}

While the query inside may be big and/or complex, its output is always a
simple single rowset, for which this code should always work. But with Oracle
(10g), it is just kicking our buts. Surely someone at Microsoft has used ADO
to launch big/complex Oracle queries to build schema's, and get rowsets back?

To elaborate the kind of query we need, here is a SQL Server query example:

begin transaction
set NOCOUNT on
create table #TX(TX int null)
-- Create many tables....
exec sp_executesql '~~ complex and dynamically created queries ~~~'

if (~~ tables do not exist ~~~)
begin
-- create table...
end
commit transaction
select 'RET=OK' as 'RET'

With Oracle, we need to do similar things, but the disconnect is that we do
not know how to get ADODB.Connection to work with Oracle for the Oracle
equivalent DECLARE. As best we see it would be something like this:

-- SET SERVEROUTPUT ON ---- This is supposedly how to get ORacle to send
output back using PUT_LINE, but ADODB.Connection.Execute to ADODB.Recordset
does not like it. We always get ORA-00922 error.
DECLARE
iTemp INTEGER;
BEGIN
execute immediate 'create table #TX(TX int null)';
-- Create many tables....
execute immediate '~~ complex and dynamically created queries ~~~'

select count(*) into iTemp from DBA_USERS where USERNAME = '~~some
table~~~';

if (iTemp = 0)
begin
-- create table...
commit;
end
-- How do we do the equiv of "select 'RET=OK' as 'RET'", as this will not
work in Oracle DECLARE so that ADODB.Connection.Execute to ADODB.Recordset
will get it?
-- Supposedly DBMS_OUTPUT.PUT_LINE('RET=OK'); should do this, but ADODB
does not seem to catch it. Also, it does not let us name the column as we
desire.
END;

------------------------
So, basically, this stems to a problem where we have code using
ADODB.Connection.Execute, and LOTS of it, and we need a way to get it to
launch similar complex queries to Oracle. Or, we are faced with rewrites. By
the way, we are using the Microsoft Oracle ODBC Driver for our connection:
DRIVER={Microsoft ODBC for Oracle};. Perhaps a change to a better driver will
help us bridge this?

 

[Bob Barrows [MVP]]

Thanks for the reply.
Sorry if #7 seems ridiculous, but it is absolutely necessary, or we
will have to do a major rewrite. Not that anything related to Oracle
requires anything less, but surely others have used
ADODB.Connection.Execute to build schemas and launch complicated
queries in Oracle.

Basically, by using the ADODB.Connection object, we can use its
"Execute" method to run any query against SQL Server, including
complex schema building queries which contain many statements, and
return rowsets with columns we define on the fly.

Again, when you do this, a <GASP> Command object gets created behind the
scenes to execute the commandtext.

<snip>
Again: why not encapsulate all this in a stored procedure?

 

[ATS]

Thanks for the reply,

Sadly, we can not use stored procedures as we are trying to use this kind of
logic to create the stored procedures, tables, users, triggers, and then some
in the 1st place. Basically, the idea is to let the database's native SQL do
the work of dynamically creating our schema as need be to get our product
started with it. But the key for this approach to work is to be able to get
feedback returned from the query that builds the schema after it analyzes the
database. Put another way, we can not arbitrarily create anything in any
database, even for expediting the creation of our schema, without checking
out the database 1st.

Sadly, we are kind of giving up on this for Oracle. It appears Oracle can
not work with this approach. It appears that one would have to create
something 1st and/or manually code in TONS of manualy queries to get the
euiqvalent of what every other database product, including DB2, SQL Server,
and now MySQL, that Oracle can not do.

By the way. The "litness" test for this is this simple ADO setup:

var cnTemp = Server.CreateObject("ADODB.Connection");
cnTemp.Open("~~~~String for DSN~~~~");
rsTemp = cnTemp.Execute("~~~~ LONG Schema Buillding Query ~~~~");

while (rsTemp.EOF != true)
{
~~~~~ Get data ~~~~~~
rsTemp.MoveNext();
}

From this approach, we were hoping that someone with ADO had found a way to
get Oracle to work with it, but nothing seems to let ADO get result sets back
from Oracle. Including if we try "SET SERVEROUTPUT ON", which always fails if
executed from ADODB.Connection. And without SET SERVEROUTPUT ON, the PUT_LINE
commands will no work. But even if the PUT_LINE did work, the Oracle DECLARE
statement do not seem to have a way to return rows as desired, with columns
defined as we need. Example:

set serveroutput on; <=== This fails to ADODB.Connection.Execute every time.
declare
iTemp INTEGER;
begin
DBMS_OUTPUT.PUT_LINE('RET=OK');
-- Without serveroutput on, this does nothing.
-- Also, there is no way to specify the columns name.
-- Also, there appears ot be no way to run a select statement by itself to
-- return data, without creating something like a stored procedure 1st.
end;

 

[Bob Barrows [MVP]]

Thanks for the reply,

Sorry I could not help. I've never worked with Oracle.

 

[paab]

SET SERVEROUTPUT ON option is applicateble only as commnad of Oracle Sqlplus tool.