Home » SQL & PL/SQL » SQL & PL/SQL » procedures parameters
procedures parameters [message #36335] Fri, 16 November 2001 10:14 Go to next message
chandra shekar
Messages: 3
Registered: November 2001
Junior Member
could anyone please help me how to use a parameter value in a "SELECT * FROM EMP WHERE EMPNO IN (parameter_value)" in inside a procedure.
for ex:
procedure ex(val varchar2)
begin
-- val can be like '100,200,300' or '100','200','300'
select * from emp where empno in (val);
end;

I will be passing val dynamically and I need to get results.

thanks

----------------------------------------------------------------------
Re: procedures parameters [message #36340 is a reply to message #36335] Sun, 18 November 2001 06:57 Go to previous messageGo to next message
Rae
Messages: 1
Registered: November 2001
Junior Member
Hi,
try dynamic sql , or native depending if you are on 8i+.

==================================================
CREATE OR REPLACE PROCEDURE Ex(pVal IN VARCHAR2)
IS

dynSql dbms_sql.varchar2s;
dynCur PLS_INTEGER;
execValue PLS_INTEGER;

BEGIN

dynCur := DBMS_SQL.OPEN_CURSOR;

dynSql.DELETE;
dynSql(0) := 'select * from emp where emp_no in ('||pVal||') ';

-- Display sql created

dbms_output.put_line(dynSql(0));

DBMS_SQL.PARSE(dynCur, dynSql, dynSql.FIRST, dynSql.LAST, TRUE, DBMS_SQL.NATIVE);
execValue := DBMS_SQL.EXECUTE(dynCur);

dynSql.DELETE;
DBMS_SQL.CLOSE_CURSOR(dynCur);

EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
/
==================================================
Have fun

Rae



----------------------------------------------------------------------
Re: procedures parameters [message #36353 is a reply to message #36335] Mon, 19 November 2001 11:29 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
if you want to return the results of the select,
then you need a bit more complexity. Also,
dbms_sql is difficult to work with and a bit
outdated for what you want.
CREATE TABLE ABC (A  NUMBER);

insert into abc values(1);
insert into abc values(9);
insert into abc values(13);
insert into abc values(999);

CREATE OR REPLACE PACKAGE ref_cur_tst
IS
   TYPE t_cur IS REF CURSOR; 

   PROCEDURE get_abc (cv_cur IN OUT t_cur, p_list IN varchar2);
END ref_cur_tst;
/
CREATE OR REPLACE PACKAGE BODY ref_cur_tst
AS
   PROCEDURE get_abc (cv_cur IN OUT t_cur, p_list IN varchar2)
   IS
   BEGIN
      OPEN cv_cur FOR 'SELECT a FROM abc where a in ('||p_list||')';
   END get_abc;
END ref_cur_tst;
/

set serveroutput on;

DECLARE
   a       abc.a%TYPE;
   cv_c1   ref_cur_tst.t_cur;
   p_list  varchar2(20) := '1, 9';
BEGIN
   ref_cur_tst.get_abc (cv_c1, p_list);

   LOOP
      FETCH cv_c1 INTO a;
      EXIT WHEN cv_c1%NOTFOUND;
      DBMS_OUTPUT.put_line (a);
   END LOOP;
   CLOSE cv_c1;
END;
/
the way I've shown here is frowned upon because it doesn't use bind variables, so worse performance.

see this link too:
http://asktom.oracle.com/pls/ask/f?p=4950:8:254370::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:210612357425,

----------------------------------------------------------------------
Previous Topic: multiple inline view in pl/sql?
Next Topic: sequential trigger after re-boot?
Goto Forum:
  


Current Time: Tue Jun 02 10:56:05 CDT 2020