| how to pass varchar2 in sp [message #35621] |
Mon, 01 October 2001 16:31  |
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  |
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 |
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 |
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
----------------------------------------------------------------------
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
