locked
oracle package RRS feed

  • Question

  • User-1075092149 posted
    create or replace 
    PACKAGE PCKG_test IS 
    
     TYPE r_item_list IS RECORD( 
        aid         table_a.aid%TYPE, 
        name     table_a.name%TYPE, 
        id        table_a.id%TYPE, 
        es        table_a.es%TYPE 
      ); 
    
      TYPE ref_item_list IS REF CURSOR RETURN r_item_list; 
    
      /* TODO enter package declarations (types, exceptions, methods etc) here */ 
      PROCEDURE PROC_IMP_APP_QUERY_ITEMLIST( 
                                         totalNum  OUT VARCHAR2, 
                                        o_item_list OUT ref_item_list); 
    END PCKG_test;
     

     

    i have create a package, i would like to ask how to run the package (script) in oracle ??

    Sunday, May 13, 2012 10:04 PM

All replies

  • User143067745 posted
    define body of the procedure 'PROC_IMP_APP_QUERY_ITEMLIST' first.
     then you can run it as:
    Begin
    Package.ProcedureName(Values);
    end
    If you are using SQLPLUS then command as:  
     execute package_name.procedure_name(ParameterValues)
     
    In this case:
    Begin
    PCKG_test.PROC_IMP_APP_QUERY_ITEMLIST(Parameter Values);
    End;
    Or
    execute PCKG_test.PROC_IMP_APP_QUERY_ITEMLIST(Parameter Values);
    If you want to have a GUI inerface then download sqlDeveloper tool for oracle from:
    http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html
    Sunday, May 13, 2012 10:36 PM
  • User-1075092149 posted

    define body of the procedure 'PROC_IMP_APP_QUERY_ITEMLIST' first.
     then you can run it as:
    Begin
    Package.ProcedureName(Values);
    end
    If you are using SQLPLUS then command as:  
     execute package_name.procedure_name(ParameterValues)
     
    In this case:
    Begin
    PCKG_test.PROC_IMP_APP_QUERY_ITEMLIST(Parameter Values);
    End;
    Or
    execute PCKG_test.PROC_IMP_APP_QUERY_ITEMLIST(Parameter Values);
    If you want to have a GUI inerface then download sqlDeveloper tool for oracle from:
    http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html

    i am using sql developer,but i can't find how to run the package script. anyone knows.?

    Monday, May 14, 2012 2:31 AM
  • User143067745 posted

    If you are using sqldeveloper you can run it in two ways:

    1.  From tools menu select SQLWorksheet. One work sheet will open with your connection.

    type here as :

    execute packagename.Procedure(arguments)

    2. You can directly run by clicking the run icon. To do it open your package body firs then click run icon. It will open another window it will have two parts:

    In top most part  titled as Target you will see all the procedures. Select One procedure that you want to run.

    In the second part in bottom titled by PL/Sql Block.

    In this part you will find a begin statement:

    after this statement  all list of vaiables are  here assigned by NULL. Replace all NULL by appropriate values and click ok. Procedure will execute. In diffrent windows like message,output vairables window you can see the result.

    Monday, May 14, 2012 3:00 AM
  • User-1075092149 posted

    If you are using sqldeveloper you can run it in two ways:

    1.  From tools menu select SQLWorksheet. One work sheet will open with your connection.

    type here as :

    execute packagename.Procedure(arguments)

    2. You can directly run by clicking the run icon. To do it open your package body firs then click run icon. It will open another window it will have two parts:

    In top most part  titled as Target you will see all the procedures. Select One procedure that you want to run.

    In the second part in bottom titled by PL/Sql Block.

    In this part you will find a begin statement:

    after this statement  all list of vaiables are  here assigned by NULL. Replace all NULL by appropriate values and click ok. Procedure will execute. In diffrent windows like message,output vairables window you can see the result.

     

    it is always prompted an error about wrong no. of argument.

    i have 4 In arguemnt and 1 out argument.

    procedure proc_testing(a in varchar2,

    b in varchar2,

    c in varchar2,

    d in varchar2,

    result out varchar2) IS

     

    ---

    then i run the script by pass those argument:

    execute pkg.proc_tessting('1','2','3','4')

     

    what is wrong?

    Monday, May 14, 2012 4:16 AM
  • User143067745 posted

    call procedure like this:

    var result  varchar2(100);
    
    execute pkg.proc_tessting('1','2','3',:result);
    
    print result;

    Output variable should be sent as Bind vairable (:result)..I tested it in my SqlDeveloper Worksheet and it's successfull. Try.

    Monday, May 14, 2012 4:50 AM