Home » SQL & PL/SQL » SQL & PL/SQL » how to pass varchar2 in sp
how to pass varchar2 in sp [message #35621] Mon, 01 October 2001 16:31 Go to next message
George
Messages: 68
Registered: April 2001
Member
I have a sp which takes dname as parameter (see below code), I tried 'ACCOUNTING', ''ACCOUNTING'' (2 single 's) and "ACCOUNTING" none of them worked. Could you tell me how to pass this in ( from pl/SQL and Java)?
This is my code:
CREATE OR REPLACE PACKAGE BODY testpkg1 AS
procedure test(
sum_cv IN OUT NOCOPY sumCur,
name IN VARCHAR2 ) AS
sql_statement VARCHAR2(100);
where_statement VARCHAR2(100);
BEGIN
if name = ' ' then
where_statement := ' ';
else
where_statement := ' AND d.dname = name ';
end if;
sql_statement :='SELECT e.* from emp e, dept d where d.deptno = e.deptno ' ||where_statement ;
OPEN sum_cv FOR sql_statement ;
END test;
END;

and this is how I execute:

VARIABLE CV REFCURSOR
EXECUTE testpkg1.test(:cv, 'ACCOUNTING' )

Thanks

----------------------------------------------------------------------
Re: how to pass varchar2 in sp [message #35622 is a reply to message #35621] Tue, 02 October 2001 01:28 Go to previous messageGo to next message
Hans
Messages: 42
Registered: September 2000
Member
CREATE OR REPLACE PACKAGE  testpkg1
AS
   TYPE sumcur is REF CURSOR;
 
   procedure test(   sum_cv IN OUT NOCOPY sumCur,
                     name IN VARCHAR2 );
END;
/
show errors
 
CREATE OR REPLACE PACKAGE BODY testpkg1
AS
 
procedure test(   sum_cv IN OUT NOCOPY sumCur,
                  name IN VARCHAR2 )
AS
   sql_statement VARCHAR2(100);
   where_statement VARCHAR2(100);
BEGIN
   if name is null then
      where_statement := null;
   else
      where_statement := ' AND d.dname = ' || '''' || name || '''';
   end if;
   
   sql_statement :='SELECT e.* from emp e, dept d where d.deptno = e.deptno ' ||where_statement ;
   
   OPEN sum_cv FOR sql_statement ;
   
END test;
 
END;
/
show errors
 
 
VARIABLE CV REFCURSOR
EXECUTE testpkg1.test(:cv, 'ACCOUNTING' );
PRINT CV;
 
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10


----------------------------------------------------------------------
Re: how to pass varchar2 in sp [message #35633 is a reply to message #35622] Tue, 02 October 2001 11:03 Go to previous messageGo to next message
George
Messages: 68
Registered: April 2001
Member
Thanks Hans for your help. It did worked. but I need to add one more paarmeter and it failed. Could you help me again? Below is my revised code:

CREATE OR REPLACE PACKAGE BODY testpkg1 AS
procedure test(
sum_cv IN OUT NOCOPY sumCur,
name IN VARCHAR2,
location IN VARCHAR2 ) AS
sql_statement VARCHAR2(100);
name_statement VARCHAR2(100);
loc_statement VARCHAR2(100);
where_statement VARCHAR2(200);
BEGIN
if name = ' ' then
name_statement := ' ';
else
name_statement := ' AND d.dname = ''' || name || '''';
end if;
if location = ' ' then
loc_statement := ' ';
else
loc_statement := ' AND d.loc = ''' || location || '''';
end if;
where_statement := name_statement || loc_statement;
DBMS_OUTPUT.PUT_LINE(where_statement);
sql_statement :='SELECT e.* from emp e, dept d where d.deptno = e.deptno ' ||where_statement ;
OPEN sum_cv FOR sql_statement ;
END test;
END;

SQL> EXECUTE testpkg1.test(:cv, 'SALES', 'CHICAGO' );
BEGIN testpkg1.test(:cv, 'SALES', 'CHICAGO' ); END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SCOTT.TESTPKG1", line 23
ORA-06512: at line 1

Thanks

----------------------------------------------------------------------
Re: how to pass varchar2 in sp [message #35650 is a reply to message #35622] Thu, 04 October 2001 00:13 Go to previous message
Hans
Messages: 42
Registered: September 2000
Member
strange, i test your code with scott/tiger in a oracle 8.1.6 database and it works

CREATE OR REPLACE PACKAGE  testpkg1
AS
   TYPE sumcur is REF CURSOR;
 
   procedure test(   sum_cv IN OUT NOCOPY sumCur,
                     name IN VARCHAR2,
                     location IN VARCHAR2 );
END;
/
show errors
 
 
CREATE OR REPLACE PACKAGE BODY testpkg1
AS
   procedure test(   sum_cv IN OUT NOCOPY sumCur,
                     name IN VARCHAR2, 
                     location IN VARCHAR2 )
   AS
      sql_statement     VARCHAR2(100);
      name_statement    VARCHAR2(100);
      loc_statement     VARCHAR2(100);
      where_statement   VARCHAR2(200);
   BEGIN
      if name = ' ' then
         name_statement := ' ';
      else
         name_statement := ' AND d.dname = ''' || name || '''';
      end if;
      
      if location = ' ' then
         loc_statement := ' ';
      else
         loc_statement := ' AND d.loc = ''' || location || '''';
      end if;
 
      where_statement := name_statement || loc_statement;
 
      DBMS_OUTPUT.PUT_LINE(where_statement);
 
      sql_statement :='SELECT e.* from emp e, dept d where d.deptno = e.deptno ' ||where_statement ;
 
      OPEN sum_cv FOR sql_statement ;
   
   END test;
END;
/
show errors
 
 
VARIABLE cv REFCURSOR
EXECUTE testpkg1.test(:cv, 'SALES', 'CHICAGO' );
PRINT cv;
 
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      


----------------------------------------------------------------------
Previous Topic: How to get return from out parameter of function
Next Topic: hide table
Goto Forum:
  


Current Time: Thu Mar 28 03:46:22 CDT 2024