CODEWITHSUNDEEP
oracle-databaseplsqloracle11g
Shekhar Nalawade
Shekhar Nalawade

Reputation: 127

Oracle Function Select * from Query Not giving any Results

The following is the function definition I am using:

CREATE OR REPLACE FUNCTION MA_FACTSET.totalCustomers
RETURN number as
    total INTEGER := 0;
    FID VARCHAR2(30) := 'NT33H0-S-US';
    stmt varchar2(1000);

BEGIN
    execute immediate 'TRUNCATE TABLE MovingAverage';
    execute immediate 'DROP TABLE MovingAverage';
    execute immediate 'CREATE GLOBAL TEMPORARY TABLE MovingAverage
    (ID INTEGER,
     PDATE DATE,
     PPRICE FLOAT,
     MA1 FLOAT) ON COMMIT PRESERVE ROWS';

    stmt := 'INSERT INTO  MovingAverage (ID,PDATE,PPRICE) SELECT 
    ROWNUM,"DATE",P_PRICE FROM FP_BASIC_BD WHERE FS_PERM_SEC_ID = ''NT33H0-S-US''';
    DBMS_OUTPUT.PUT_LINE(stmt);
    execute immediate stmt;
    dbms_output.put_line(SQL%ROWCOUNT);
    commit;

    execute immediate 'UPDATE  MovingAverage A SET MA1 =(SELECT AVG(PPRICE) 
    FROM MovingAverage WHERE ID>=A.ID-5 AND ID<A.ID )' ;
    dbms_output.put_line(SQL%ROWCOUNT);
    commit;

    execute immediate 'SELECT * FROM MovingAverage ORDER BY ID ASC';
    dbms_output.put_line(SQL%ROWCOUNT);
    commit;

    dbms_output.put_line(total);
    RETURN total;
END;
/

I have applied some checks to see whether the function is running properly or not, like getting SQL%ROWCOUNT.

The following is the DBMS output of the function:

INSERT INTO  MovingAverage (ID,PDATE,PPRICE) SELECT ROWNUM,"DATE",P_PRICE     FROM FP_BASIC_BD WHERE FS_PERM_SEC_ID = 'NT33H0-S-US'
4114
4114
0
0

I want know why rowcount of "SELECT * FROM MovingAverage ORDER BY ID ASC" is 0. And if there is any way to print complete table in Data Grid. I am using TOAD for Oracle.

Upvotes: 0

Views: 938

Answers (2)

Shekhar Nalawade
Shekhar Nalawade

Reputation: 127

With collections and the table function, a function can return a table that can be queried in an SQL statement. This is demonstrated in the following example.

Create your record type variable first.

create or replace type t_record as object (i number,n varchar2(30));

Create table type variable which is based on record type variable.

create or replace type t_table as table of t_record;

Then we can proceed to create a function as follows.

create or replace function return_table return t_table as
  v_ret   t_table;
begin

 --
 -- Call constructor to create the returned
 -- variable:
 --
v_ret  := t_table();

 --
 -- Add one record after another to the returned table.
 -- Note: the »table« must be extended before adding
 -- another record:
 --
    v_ret.extend; v_ret(v_ret.count) := t_record(1, 'one'  );
    v_ret.extend; v_ret(v_ret.count) := t_record(2, 'two'  );
    v_ret.extend; v_ret(v_ret.count) := t_record(3, 'three');

 --
 -- Return the record:
 --
    return v_ret;

end return_table;

For more details and further extension of work please refer following URL.

https://renenyffenegger.ch/notes/development/databases/Oracle/PL-SQL/collection-types/return-table-from-function/index

http://www.adp-gmbh.ch/ora/plsql/coll/return_table.html

Upvotes: 1

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

I suggest you to rewrite your function removing dynamic sqls where not necessary. Also, If you have DDLs, DMLs don't use PL/SQL function, rather use procedure.

For select Statements in EXECUTE IMMEDIATE, it will work if you use BULK COLLECT INTO 

CREATE OR REPLACE FUNCTION totalCustomers
   RETURN NUMBER
AS
   total   INTEGER := 0;
   FID     VARCHAR2 (30) := 'NT33H0-S-US';
   stmt    VARCHAR2 (1000);

   TYPE mrectype IS TABLE OF MovingAverage%ROWTYPE;

   mrec    mrectype;
BEGIN
   EXECUTE IMMEDIATE 'TRUNCATE TABLE MovingAverage';

   EXECUTE IMMEDIATE 'DROP TABLE MovingAverage';

   EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE MovingAverage
(ID INTEGER,
 PDATE DATE,
 PPRICE FLOAT,
 MA1 FLOAT) ON COMMIT PRESERVE ROWS';

   stmt :=
      'INSERT INTO  MovingAverage (ID,PDATE,PPRICE) SELECT 
ROWNUM,"DATE",P_PRICE FROM FP_BASIC_BD WHERE FS_PERM_SEC_ID = ''NT33H0-S-US''';
   DBMS_OUTPUT.PUT_LINE (stmt);

   EXECUTE IMMEDIATE stmt;

   DBMS_OUTPUT.put_line (SQL%ROWCOUNT);
   COMMIT;

   EXECUTE IMMEDIATE 'UPDATE  MovingAverage A SET MA1 =(SELECT AVG(PPRICE) 
FROM MovingAverage WHERE ID>=A.ID-5 AND ID<A.ID )';

   DBMS_OUTPUT.put_line (SQL%ROWCOUNT);
   COMMIT;

   EXECUTE IMMEDIATE 'SELECT * FROM MovingAverage ORDER BY ID ASC'
      BULK COLLECT INTO mrec;

   DBMS_OUTPUT.put_line (SQL%ROWCOUNT);
   COMMIT;
   DBMS_OUTPUT.put_line (total);
   RETURN total;
END;
/

Call this function like this.

SET SERVEROUTPUT ON
DECLARE
x NUMBER;
BEGIN
x := totalCustomers;
END;
/

You cannot call the function in SQL select

Upvotes: 1

Related Questions