DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: packages

  1. #1
    Join Date
    Jul 2005
    Posts
    23

    packages

    can anyone tell me if its possible to call a procedure in a package from a standard SQL query something along the lines of

    ** the actual procedure I want to use has multiple OUT variables which I need to retrive into the result set


    SELECT tc.id,
    tc.obj_id,
    tc.description,
    packagename.procedure(parameter) as col1
    FROM txs_containers tc
    WHERE tc.service_id = :aservice
    AND tc.schedule_class_id = :aclass
    AND tc.txs_date = :adate
    AND tc.type in ( 1, 13 )
    AND tc.txs_container_id IS NULL

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    did you try it? What error are you getting?
    Jeff Hunter

  3. #3
    Join Date
    Jul 2005
    Posts
    23

    packages

    I cant actually figure out the syntax. The procedure has multiple out variables, Im not sure how to go about it. This is the package header

    PROCEDURE SP_GET_PRG_DETAILS
    (
    p_id IN NUMBER, -- txs_containers.id
    p_ctr_key OUT NUMBER, -- unique contract (series) id
    p_prg_auk OUT NUMBER, -- unique programme id
    p_ctr_contrt_num OUT NUMBER, -- user contract ref
    p_prg_prog_num OUT NUMBER, -- user programme ref
    p_ss_box_cod OUT NUMBER, -- schedule id (unique by date)
    p_transhist_key OUT NUMBER, -- unique transmission id
    p_ss_box_nam OUT VARCHAR2, -- schedule description
    p_time OUT NUMBER, -- bill time (from ss_box)
    p_dur OUT NUMBER, -- slot duration (from ss_box)
    p_mm_file OUT NUMBER, -- mm_file_key (from mm_txlock)
    p_aitprg_current_title_tex OUT VARCHAR2, -- current title text (from aitprg)
    p_obj_id_override IN NUMBER, -- obj_id to override the txs_containers.obj_id - needed by cached update
    p_duration_tim OUT NUMBER,
    p_mm_item OUT NUMBER -- mm_item_key (from mm_txlock)
    )

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Had you read about Calling stored procedures, you would know that you can only call a function from directly from SQL. I think Jeff was trying to get you to realize this.

    # A procedure can be called within the body of another procedure or a trigger.
    # A procedure can be interactively called by a user using an Oracle tool.
    # A procedure can be explicitly called within an application, such as a SQL*Forms or a precompiler application.
    # A stored function can be called from a SQL statement in a manner similar to calling a built-in SQL function, such as LENGTH or ROUND.

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    You might be able to wrap that procedure in a function that concatanates the out parameter values and calls that, then strip out the parameter values in the SQL
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    Jul 2005
    Posts
    23

    packages

    can you give me some examples of this ?

  7. #7
    Join Date
    Jul 2005
    Posts
    23

    packages

    im new to oracle, so please excuse my ignorance. I'll outline what Im trying to achive and hopefully some of the more oracle savvy folks out there could give me some pointers.

    Im migrating from sybase to oracle (delphi & DOA components)
    I have a dataset returned to the application from a bit of sql thats the union of two queries. Is it possible to replace this bit of sql in a package and return the data as a result set (not as individual variables as there could be many rows of data). Is there some sort of 'dataset variable' I could return ? The stored procedure is currently being called from the 'OnCalc' event of the dataset in the app, but I want to call it from with the SQL.

  8. #8
    Join Date
    Jan 2003
    Location
    Hull, UK
    Posts
    220
    Hi

    You can use REF CURSOR if you wanna return dataset or recordset to a client application.

    Define a package which has got a REF CURSOR variable and a procedure.. you can add related functions and procedures to ur package.


    Code:
    CREATE OR REPLACE PACKAGE MyPkg AS 
      TYPE emp_cursor IS REF CURSOR;
    
     Procedure GetEmpRSet(p_deptno    IN  emp.deptno%TYPE,
                                  p_recordset OUT emp_cursor ) AS 
    END MyPkg; 
    
    
    CREATE OR REPLACE PACKAGE Body MyPkg AS 
    
    Procedure GetEmpRSset(p_deptno    IN  emp.deptno%TYPE,
                                  p_recordset OUT emp_cursor ) 
    
    BEGIN 
      OPEN p_recordset FOR
        SELECT ename,
               empno,
               deptno
        FROM   emp
        WHERE  deptno = p_deptno
        ORDER BY ename;
    END GetEmpRSet;
    
    
    End Mypkg;
    You can call this procedure in ur client application if its vb or .net...

    you can also use this in a anonymous pl/sql block...

    check this link...

    http://www.oracle-base.com/articles/...Recordsets.php

    HTH

    Srini

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width