Home » SQL & PL/SQL » SQL & PL/SQL » how to execute the type variable procedure. (Oracle 11g)
how to execute the type variable procedure. [message #583129] Fri, 26 April 2013 02:12 Go to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member

Hi Experts,
I have created the following procedure.
Since I am using this first time I don't know how to execute this.
Please help me.
CREATE OR REPLACE PACKAGE GAFT_PROG_DIT.ram_package
    IS
    
      TYPE type_ots IS TABLE OF ORDER_TREND_SCORE%ROWTYPE INDEX BY PLS_INTEGER;
      
      PROCEDURE InsertTrend( P_TYPE_OTS_REC IN type_ots );
      
END;
/

CREATE OR REPLACE PACKAGE BODY GAFT_PROG_DIT.ram_package 
    IS
    PROCEDURE InsertTrend(
                            P_TYPE_OTS_REC IN type_ots
                           )
        IS
        BEGIN
            FORALL i in P_TYPE_OTS_REC.FIRST.. P_TYPE_OTS_REC.LAST
              DELETE
                FROM ORDER_TREND_SCORE
                WHERE BUID = P_TYPE_OTS_REC(i).BUID
                  AND ORDER_NUM = P_TYPE_OTS_REC(i).ORDER_NUM
                  AND TREND_VARIABLE = P_TYPE_OTS_REC(i).TREND_VARIABLE; 
                  
            FORALL i in P_TYPE_OTS_REC.FIRST.. P_TYPE_OTS_REC.LAST
              INSERT INTO ORDER_TREND_SCORE(
                                            BUID,
                                            ORDER_NUM,
                                            TREND_VARIABLE,
                                            DEDUCTION_POINTS,
                                            FRAUD_PERCENTAGE,
                                            FRAUD_WORKED_PERCENTAGE
                                           )
              VALUES(
                     P_TYPE_OTS_REC(i).BUID,
                     P_TYPE_OTS_REC(i).ORDER_NUM,
                     P_TYPE_OTS_REC(i).TREND_VARIABLE,
                     P_TYPE_OTS_REC(i).DEDUCTION_POINTS,
                     P_TYPE_OTS_REC(i).FRAUD_PERCENTAGE,
                     P_TYPE_OTS_REC(i).FRAUD_WORKED_PERCENTAGE
                    );
END InsertTrend;
end;
/


Thanks in advance.
Re: how to execute the type variable procedure. [message #583131 is a reply to message #583129] Fri, 26 April 2013 02:27 Go to previous messageGo to next message
Michel Cadot
Messages: 57612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe I'll consider to help you when you'll consider to feedback to and thank those that help you.

BlackSwan wrote on Sun, 21 April 2013 05:25
below STILL applies

Michel Cadot wrote on Thu, 28 March 2013 04:56
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel



Regards
Michel

[Updated on: Fri, 26 April 2013 02:29]

Report message to a moderator

Re: how to execute the type variable procedure. [message #583135 is a reply to message #583131] Fri, 26 April 2013 03:56 Go to previous message
sss111ind
Messages: 428
Registered: April 2012
Location: India
Senior Member

CREATE OR REPLACE
PACKAGE ram_package
IS
TYPE t1
IS
  TABLE OF emp%rowtype INDEX BY binary_integer;
  PROCEDURE emp_print(
      emp_details t1);
END;


CREATE OR REPLACE
PACKAGE ram_package body
IS
  PROCEDURE emp_print(
      emp_details t1)
  IS
  BEGIN
    FOR i IN emp_details.FIRST .. emp_details.LAST
    LOOP
      dbms_output.put_line(emp_details(i).ename);
  END LOOP;
END ;


DECLARE
  ll ram_package.t1;
BEGIN
  SELECT * bulk collect INTO ll FROM emp ;
  ram_package.emp_print(ll);
END;

Previous Topic: Creating custom error message
Next Topic: function returning pl/sql table of dynamic structure
Goto Forum:
  


Current Time: Sat Apr 19 20:33:45 CDT 2014

Total time taken to generate the page: 0.07559 seconds