Use record type as an OUT parameter in Oracle PL/SQL Stored Procedure

-
Objective: How to print record type values in Oracle PL/SQL .
Solution:

Step 1: Create package fxgn_sample_pkg.

DROP PACKAGE fxgn_sample_pkg;

create or replace
  PACKAGE fxgn_sample_pkg
    as
        TYPE fxgn_sample_rectype IS RECORD (p_name varchar2(40),         
                                                                                     p_emp_id number
                                                                                    );

        type fxgn_sample_table_rectype is table of fxgn_sample_rectype;

        procedure fxgn_sample_prc (fxgn_sample_table_rec1 OUT fxgn_sample_table_rectype                                                                            );
END fxgn_sample_pkg;
/

create or replace
  PACKAGE BODY fxgn_sample_pkg
    AS
      PROCEDURE fxgn_sample_prc(fxgn_sample_table_rec1 OUT fxgn_sample_table_rectype)
        IS
           BEGIN
               SELECT  ename,
                               empno
                   BULK COLLECT
                    INTO fxgn_sample_table_rec1
                  FROM  emp;
      END fxgn_sample_prc;
 END fxgn_sample_pkg ;

Step 2: Calling fxgn_sample_pkg.fxgn_sample_proc.

SET serveroutput ON;

DECLARE
  l_table_rec_type fxgn_sample_pkg.fxgn_sample_table_rectype;
BEGIN
  dbms_output.put_line('Calling fxgn_sample_pkg.fxgn_sample_prc');
  fxgn_sample_pkg.fxgn_sample_prc(l_table_rec_type);
  
  FOR l_rec IN 1..l_table_rec_type.count
  LOOP
    dbms_output.put_line ('Employee Details: ' || l_table_rec_type(l_rec).p_name ||' ['||l_table_rec_type(l_rec).p_emp_id||']' );
  END LOOP;
END;

Output:
That's it.

Comments

Post a Comment

Popular posts from this blog

Highlight the cell of Interactive Report based on Search Criteria in Oracle APEX

-
Image
Oracle APEX Interactive Report has default search option, where we can search the values which are exists in the report. Here I am giving an elegant method to highlight the cell of interactive report based on search criteria. Step 1:  Create a new blank page. Step 2:  Create a new Interactive Report region to the page. Step 3:  Set  CSS Classes  "data-highlighter" to the region. Fig 1: Set CSS Classes Step 4: Create a class by adding below CSS to the page in inline CSS section. It will change the style of report cells. Go to   Page => CSS => Inline . highlight- data  {     font-weight: bold !important ;      background -color: #ffecb4 !important ; } Step 5: Get a class for IR search component by following below steps. Place a mouse cursor on IR search component. Right click a mouse (The right mouse button is often used to open contextual menus, which are pop-up menus that change depending where you click) and select the menu " Inspect " . DevTools pop-up come
Read more

Querying Data From Flat Files in Oracle

-
Image
Couple of days before i had an interesting discussion with my client related to  Oracle external tables , Client: Is there any way to read the data from flat file(s) which are stored outside the database? Raja: Could you please explain in more detail? Client: Sure. We have data analytics team, what they will do is, they will export the file(s) (.csv/.txt) from their system and push it to our file system ( Oracle directory ).  They have a job to push their files to our file system, which will run in predefined interval. Once it's uploaded we should read the file(s) data and display it in our system. Raja: Yes! it's very much possible, if we go with O racle external table . Client: What is that? Raja:  It can be used for query, join and sort operations. External tables allow oracle to query data that is stored outside the database in flat files. Once it's running we can create views against it. Client: Oh! Sounds interesting. That's what i needed. Is there any limit
Read more

Number of Tables by The Number of Rows in Oracle Database

-
Image
Here I am giving an example to find out number of tables by the number of rows in oracle database. Query 1: Number of all tables accessible to the current user in Oracle database by the number of rows grouped into predefined intervals . SELECT  row_interval ,         count ( * )   AS  no_of_tables    FROM   ( SELECT   owner ,                table_name ,                num_rows ,                 CASE                   WHEN  num_rows >  1000000000                   THEN   '1b rows and more'                   WHEN  num_rows >  1000000                   THEN   '1m - 1b rows'                   WHEN  num_rows >  1000                   THEN   '1k - 1m rows'                   WHEN  num_rows >  100                   THEN   '100 - 1k rows'                   WHEN  num_rows >  10                   THEN   '10 - 100 rows'                   ELSE   '0 - 10 rows'                 END   AS  row_interval            FROM  sys . all_tables
Read more

Save Selected Interactive Grid Records into a Collection - Oracle APEX

-
Image
Here I am giving an example to save selected interactive grid records into a oracle apex collection. Step 1: Create a new blank page. Note: Mine was page 20. You will need to update reference to " P20 " with your page number if it's different. Step 2: Create a new interactive grid report region to the page using below query. Set Static Id "EmpDetails" to the region. SELECT  *     FROM   ( SELECT  emp . empno ,                emp . ename ,                emp . JOB ,                dept . dname department ,                dept . loc  LOCATION ,                mgr . ename  manager ,                emp . hiredate ,                 nvl ( emp . sal , 0 )  salary ,                 nvl ( emp . comm , 0 )  commission            FROM  eba_demo_chart_emp emp ,                eba_demo_chart_dept dept ,                eba_demo_chart_emp mgr           WHERE  emp . deptno = dept . deptno             AND  emp . mgr      = mgr . empno  ( + )           ORDER   BY  emp . ename
Read more

Highlight Data That Meet Certain Criteria in Oracle APEX Report

-
Image
  Here I am giving an example to highlight data when it meets certain criteria in oracle apex report. Step 1:  Create a new blank page. Step 2: Function Call:  Create a function  get_data_highlighter.  If you want to render it in multiple places, it would be good practice to put this code in a package and call it in your query. Sample function in a package called  FXGN_GENERAL create   or   replace   FUNCTION  get_data_highlighter (       p_value  IN   NUMBER )      RETURN   VARCHAR2    IS     l_return            VARCHAR2   ( 4000 );    BEGIN      IF  p_value    >=  95   THEN   -- Very Strong       l_return               :=  '<span class="t-Badge t-Badge--basic t-Badge--small is-success w100p">Very Strong</span>' ;      elsif  p_value >=  66   THEN   -- Strong       l_return               :=  '<span class="t-Badge t-Badge--basic t-Badge--small is-success w100p">Strong</span>' ;      elsif  p_value >=  33   THEN   -- M
Read more

Return a Validation Message using JavaScript if the Oracle Application Express Item is Empty

-
Image
Here I am giving an example to Return a custom validation message using JavaScript if the Oracle Application Express Item is Empty. Step 1:  Create a new blank page. Note:  Mine was page 15. You will need to update reference to "P15" with your page number if it's different. Step 2: Create a new HTML region to the page. Step 3:  Create a new item to the region. Set name to P15_ATTRIBUTE_1 and leave the default of Text Field Step 4:  Create a new item to the region. Set name to  P15_ATTRIBUTE_2  and leave the default of  Text Field Step 5:  Create a new button to the region. Set name to  SUBMIT  and action to Defined Dynamic Action Step 6:  Create a new dynamic action that fires when the button is clicked. The easiest way to do this is to right click the button and select  Create Dynamic Action  Set name to  Not Null Check Validation Step 7:  Set action to Execute JavaScript Code and copy and paste the following code into the JavaScript Code section. var attribute1 = $(&
Read more

Remove X (Close Mark) from Oracle APEX Modal Dialog

-
Image
  Here I am giving an example to remove X (Close Mark) from the Oracle APEX modal dialog. Step 1:  Get a class for X (Close Mark) by following below steps. Place a mouse cursor on Close Mark (X). Right click a mouse (The right mouse button is often used to open contextual menus, which are pop-up menus that change depending where you click) and select the menu "Inspect" . DevTools pop-up comes up when we click on Inspect menu, from there we can get a class (JQuery Selector) for the particular components. Step 2:  Copy and paste below code in page JavaScript (Execute when Page Loads) var button = parent.$('. ui-dialog-titlebar-close ');  button.hide();  Output: That's it. The close mark (X) has been removed. Happy APEXing!!!... Reference: Oracle APEX hide the x on the modal dialog
Read more

Display User Rating Icons Dynamically in Oracle APEX Report

-
Image
Here I am giving an example to display user rating icons dynamically in oracle apex report. Step 1:  Create a new blank page. Step 2: Function Call:  Create a function  get_user_ratings.  If you want to render it in multiple places, it would be good practice to put this code in a package and call it in your query. Sample function in a package called  FXGN_GENERAL create   or   replace   FUNCTION  get_user_ratings ( p_value  IN   NUMBER )      RETURN   VARCHAR2    IS     l_return      VARCHAR2   ( 4000 );     l_checked     VARCHAR2 ( 240 )  :=  '<span class="fa fa-star" style="color:orange;"></span>' ;     l_un_checked  VARCHAR2 ( 240 )  :=  '<span class="fa fa-star" style="color:grey;"></span>' ;     l_max_value   NUMBER         :=  5 ;     l_remaining   NUMBER ;    BEGIN      IF  p_value  BETWEEN  1  AND   5   THEN   -- Check given values are in the range       l_remaining := l_max_value - p_value ;       
Read more

Printing Page Numbers in RTF Template [Oracle BI Publisher]

-
Image
Here I am giving an example to print the page numbers dynamically in the RTF (Rich Text Format) template. Step 1:  Go to page footer and copy and paste the below script. Page |  <?fo:page-number?>  of  <?fo:page-number-citation:xdofo:lastpage-joinseq?> <fo:page-number> :   This is the object, which is used to represent the current page-number. <?fo:page-number-citation:xdofo:lastpage-joinseq?> :  This is the syntax, which is used to represent the total number of pages. Step 2:  Load the XML and preview the result. Output: That's it. References: fo:page-number Printing Page Number Code in Oracle XMLP RTF Template
Read more

Find Where Specific Table or View is Used in Oracle Database

-
Image
Here I am giving an example to find where specific table or view is used in oracle database Query 1: All objects where specific table or view accessible to the current user is used in Oracle database. SELECT  referenced_owner        ||  '.'        || referenced_name  AS  table_name ,        referenced_type     AS   TYPE ,         owner        ||  '.'        ||  NAME   AS  referencing_object ,         TYPE      AS  referencing_type    FROM  sys . all_dependencies   WHERE  referenced_type  IN   ( 'TABLE' ,   'VIEW' )     AND  referenced_name    =  'FXGN_USERS'   -- put your table/view name here     --AND referenced_owner = 'SCHEMA_NAME'     AND   owner   NOT   IN   ( 'ANONYMOUS' , 'CTXSYS' , 'DBSNMP' , 'EXFSYS' ,                         'MDSYS' ,   'MGMT_VIEW' , 'OLAPSYS' , 'OWBSYS' ,                       'ORDPLUGINS' ,   'ORDSYS' ,   'SI_INFORMTN_SCHEMA&#
Read more