-
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
-
did you try it? What error are you getting?
Jeff Hunter
-
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)
)
-
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.
-
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
-
packages
can you give me some examples of this ?
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|