1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Pl spl function that returns a nestedtable

Discussion in 'SQL PL/SQL' started by benjaminzz, Jul 29, 2018.

  1. benjaminzz

    benjaminzz Starter

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    HN
    i would like help with creating a function for login in pl sql that takes in input parameters and then queries four tables and returns the information in form of an array or table that will be sent in json.
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    769
    Likes Received:
    148
    Trophy Points:
    830
    Location:
    Russian Federation
    For education :
    1) https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adjsn/toc.htm
    2) https://oracle-base.com/articles/12c/plsql-object-types-for-json-12cr2

    simple example :
    Code (SQL):

    DECLARE
      v_json json_object_t;
      --test proc
      PROCEDURE  test_json
      (
        p_tab_name user_tables.TABLE_NAME%TYPE,
        p_json_obj  OUT json_object_t
      )
      AS
        v_result CLOB;
      BEGIN
        SELECT  
        json_object('tab_name'          VALUE t.TABLE_NAME,
        'cnt_column'  VALUE COUNT(t.COLUMN_ID) ,
        'columns'    VALUE json_arrayagg(t.COLUMN_NAME
                                                          ORDER BY t.COLUMN_ID)
       
                                             FORMAT JSON)
       
        INTO v_result
        FROM all_tab_cols t
        WHERE t.TABLE_NAME = UPPER(p_tab_name)
        GROUP BY t.TABLE_NAME;
        p_json_obj := json_object_t(v_result);
      END;

    BEGIN
      test_json('dual',v_json);
      dbms_output.put_line('Table of :'||v_json.get_string('tab_name'));
      dbms_output.put_line('Cnt columns:'||v_json.get_number('cnt_column'));
      dbms_output.put_line('List columns of :'||v_json.get_array('columns').to_string);
    exception WHEN others THEN dbms_output.put_line(dbms_utility.format_error_backtrace);
    END;
    /
    sql>

    TABLE OF :DUAL
    Cnt COLUMNS:1
    List COLUMNS OF :["DUMMY"]




     
     
    Last edited: Aug 15, 2018