Read a DBMS Procedure in ADVPL

Navigation
3

Is it possible to read a DBMS Procedure in ADVPL (TOTVS language) handling the return of a Cursor?

Is there an 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 SP x from advpl and have the Cursor returned.

    
asked by anonymous 06.10.2017 / 23:57

1 answer

3

In AdvPL there are three ways to execute a Statement in a SGDB via DBAccess:

  • TCSPEXEC () - To execute stored procedures with one or more input parameters and / or output parameters (Return is a list of parameters defined in the Procedure as IN / OUT or OUT only)
  • TCSQLEXEC () - To execute DDL or DML statements (No return from data, only Success or Failure)
  • TCGENQRY () - To execute 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 would result in a TCGenQry (), used in conjunction with the DbUseArea () function or even the USE command ... However, DBAccess does not consider a statement that starts with "EXECUTE "as a" select ", and does not allow its execution, returning error "Invalid Select / Query Statement"

If, instead of creating a Stored Procedure, you create a Table Function, you can retrieve the return through AdvPL using TCGenQry (). To do this, see the following topic: Oracle procedure return query lines

Once using the above alternative, the open cursor in AdvPL has no difference from a result set obtained with a "Select". For more information on opening a result set in AdvPL, see the documentation for the TCGenQry function.

References:

30.10.2017 / 02:21
Remove whitespaces from the list in Python How to validate a text field with InputVerifier?