how can i send an anonymous block to oracle and get result from oracle in coldfusion

Question

Hi guys, In coldfusion, how can I send an anonymous block to oracle and get some response from oracle? I tried cfquery, but it doesn't work. Great thanks.

---

@Antony, I know i can write anonymous block in cfquery. Such as:

<cfquery name="queryName" datasource="oracle11ghr" result="queryName_meta">
BEGIN
    INSERT INTO npr_t_reservation(reservation_id) VALUES(33);
    INSERT INTO npr_t_reservation(reservation_id) VALUES(34);
    UPDATE npr_t_reservation set reservation_id=35 WHERE reservation_id=34;
    COMMIT;
END;
</cfquery>

In fact what i did not know is how can i get some return value from sending anonymous block to oracle.

@Antony, Hi Antony, the upper code is just a demonstration. In fact what I want to get from anomynous is of simple datatype, not collections or object type instance. Such as VARCHAR2, NUMBER etc.

---

@APC, I don't use some kind of stored program because I'm not allowed to save it into the database. So why I want to use an anonymous block to do the database work? Because I need to do a lot of database related work. If I do these work in coldfusion it will be complicated and trivial.

Answer 1

There's no standard method that springs to mind to do this, though I can think of one really f****d up way of trying it that I would never put into production myself. I think you might be stuck with using SQL to return your value, separate from the anonymous block.

It's a shame about the restrictions on stored procedures. You might try making the case that your procedures could be in a different schema to the data so that they are logically isolated.

Answer 2

Can you not include a SELECT query in there to return your value?

<cfquery name="queryName" datasource="oracle11ghr" result="queryName_meta">
BEGIN
    INSERT INTO npr_t_reservation(reservation_id) VALUES(33);
    INSERT INTO npr_t_reservation(reservation_id) VALUES(34);
    UPDATE npr_t_reservation set reservation_id=35 WHERE reservation_id=34;
    COMMIT;
    SELECT myReturnValue AS RETURN_VALUE FROM dual;
END;
</cfquery>

<cfoutput>#queryName.RETURN_VALUE#</cfoutput>

Answer 3

if you are using a version of coldfusion that supports the cfide.adminapi you can do something like this:

<cfquery name="insData" datasource="datasourcename">
    insert into maytable
     (column1)
    values
     (42)
</cfquery>

<cfscript>
adminObj = createObject("component","cfide.adminapi.administrator");
    adminObj.login("password");

adminDbugObj = createObject("component","cfide.adminapi.debugging");
// getDebugRecordset() returns a query object.
// "name", "datasource" and "body" are three of its columns.
q = #adminDbugObj.getDebugRecordset()#;
</cfscript>

<cfloop query="q">
<cfif name EQ "insData" and datasource EQ "datasourcename">
<cfoutput>#rowcount#</cfoutput>
<cfabort>
</cfif>
</cfloop>

I would also use the cftransaction/cftry/cfcatch tag with commit/rollback rather than an anonymous block and separate the queries into their own cfquery block.