Work at SourceForge, help us to make it a better place! We have an immediate need for a Support Technician in our San Francisco or Denver office.

Close

DML operations in Open Reports?

2013-01-30
2013-04-25
  • Hi,
    Is there any way i can perform Delete(DML) operation using open reports Query Reports?
    Tried creating the following function and calling it in an anonymous block, doesn't work. The function is given below

    CREATE OR REPLACE
    function Delete_func(idParam IN number)
    Return number
    is
    CountRec varchar2(200);
    Begin
    delete
    FROM format_table formattr
    WHERE formattr.id=idParam;
    if SQL%FOUND then
    CountRec:=SQL%ROWCOUNT;
    Else CountRec:=0;
    End IF;
    return CountRec;
    END;

     
  • John
    John
    2013-01-30

    I have found that JapserServer does a transaction rollback after the query. If you are using msSQL, add

    IF @@TRANCOUNT > 0 COMMIT TRANSACTION

    before exiting Stored Procedure

    John

     
  • Hi,
    I am able to execute DML queries but in return it expects some data to be returned. So we tried to write DML queries in an Function so that it could return data always. But through open reports, I am not able to invoke function written using Pl/SQl query.

    thanks in advance for the help.

     

  • Anonymous
    2013-02-01

    Hi,
    you can call function in ordinary SQL request.

    For example (Oracle): select Delete_func(123) from dual;

    For other RDBMS you could check the syntax of calling a function. Just remember that calling a procedure is different thing.

    I used a function which is executing procedures in its body .

    Ivo