Read a DBMS Procedure in ADVPL

Asked

Viewed 1,116 times

3

It is possible to read a DBMS Procedure in ADVPL (TOTVS language) treating the return of a Cursor ?

Is there any object ?

The bank in question is Oracle. 

create or replace PACKAGE abc IS
   TYPE abcCursor IS REF CURSOR;
END;

create or replace PROCEDURE x (p1 IN VARCHAR2,
                               pCursor IN OUT abc.abcCursor)
   IS
BEGIN
  IF PCURSOR%ISOPEN THEN
     CLOSE PCURSOR;
  END IF;
    SELECT * from tabela where chave=p1;
END;

In case you call advpl SP x and have Cursor returned.

oracle pl-sql advpl

1 answer

3

In Advpl there are three ways to run a Statement on an SGDB through Dbaccess:

  • TCSPEXEC() -- To perform stored procedures with one or more Input and/or output parameters (Return is a list of parameters defined in Procedure as IN/OUT or OUT only)
  • TCSQLEXEC() -- To execute DDL or DML statements (No return from data, only Success or Failure)
  • TCGENQRY() -- To run a SELECT in SGDB (A Query, whose return is or can be treated as a result-set.

The only function that would make Advpl visible to a result-set would be the TCGenQry(), used in conjunction with the function DbUseArea() or even the command USE ... But Dbaccess only allows you to open a cursor using Tcgenqry() s the SQL statement is started with a SELECT. An SQL statement started with "EXECUTE","CALL" or the like, if executed by TCGenQry(), results in the execution error "Invalid Select / Query Statement".

Already the function TCSqlExec() allows the execution of SQL statements such as INSERT, UPDATE, CALL, EXECUTE and the like, but if the execution generates a result set, it is not recovered in Advpl.

If, instead of creating a Stored Procedure, you create a Table Function, it will be possible to recover the return by Advpl using TCGenQry(). For this, see the following topic: Procedure Oracle return query lines

Once using the above alternative, the cursor opened in Advpl has no difference compared to a result-set obtained with a "Select". For more information on how to open a result-set in Advpl, see the function documentation Tcgenqry.

A cursor in Advpl through Dbaccess using the Tcgenqry() function is always opened as a cursor Read-Only and Forward-Only (read-only and read-sequentially) addressed via the ALIAS used in the opening as a parameter for the function DbUseaArea(). The columns of the current return row are accessed using the syntax ALIAS->COLUNA. To check if the cursor has reached the end, use the function EOF(), and to fetch the next cursor result, use the function DBSKIP() -- amounts to one Fetch on the cursor.

References:

  • 1

    And is the result set manipulation equivalent to the return of the cursor? At no time of your answer have I seen you dealing with this in specific.

  • Thanks, I’ll test it.

Browser other questions tagged oracle pl-sql advpl

You are not signed in. Login or sign up in order to post.