Home » SQL & PL/SQL » SQL & PL/SQL » Dynaic Procedure (Oralce 10g)
Dynaic Procedure [message #385236] Sun, 08 February 2009 03:16 Go to next message
raopapa
Messages: 10
Registered: January 2009
Junior Member
Hi,

How to create dynamic procedure using INPUT parameters.

for example..
Procedure proc_emp
( pi_empno IN Number,
  pi_ename IN varchar2,
  pi_deptno IN Number,
  Po_refcur OUT sys_refcursor;
)
is
Begin
 if pi_empno is not null and pi_ename and pi_deptno is null
then  
open po_refcur
  for
  Select * from emp where empno = pi_empno;
elsif
 pi_deptno is  null and pi_ename and pi_deptno is not null
 then
 open po_refcur
  for
  Select * from emp where ename = pi_ename and deptno = pi_deptno;
end if;
End;
/


In that way query should build..based on given INPUT query should build ...if no INPUT are given all data should return.

pl give me an solution .

Paparao

[EDITED by LF: applied [code] tags]

[Updated on: Sun, 08 February 2009 08:56] by Moderator

Report message to a moderator

Re: Dynaic Procedure [message #385237 is a reply to message #385236] Sun, 08 February 2009 03:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What happened when you tried?
Copy and paste your SQL*Plus session.
Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

In the end, what is the question?

Regards
Michel
Re: Dynaic Procedure [message #385239 is a reply to message #385237] Sun, 08 February 2009 03:30 Go to previous messageGo to next message
raopapa
Messages: 10
Registered: January 2009
Junior Member
in the procedur all INPUT parameters they will not pass always,whenever they pass with limited INPUT parametes bases on that sql statement should build.


Re: Dynaic Procedure [message #385243 is a reply to message #385239] Sun, 08 February 2009 03:56 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Is this not what your procedure does?
2/ You can open a cursor for a dynamic string which is a less good solution

Regards
Michel
Previous Topic: qery problem
Next Topic: Issueing COMMIT in Exception (merged 3)
Goto Forum:
  


Current Time: Sun Dec 11 02:11:13 CST 2016

Total time taken to generate the page: 0.06426 seconds