| pass number and character in dynamic sql [message #37059] |
Mon, 14 January 2002 07:34  |
George
Messages: 68
Registered: April 2001
|
Member |
|
|
Hi,
I have a sp like below (I have to use dynamic SQL), there are two places wrong: 1 how to pass a number "dno" into dynamic sql and 2. how to pass character ias parameter to the sp test. At both cases the error message is
ORA-00904: invalid column name
Here is my code:
create or replace package testpkg AS
TYPE sumCur IS REF CURSOR;
TYPE estType IS REF CURSOR;
function totalNo (
dn IN varchar2)
RETURN NUMBER;
procedure test(
sum_cv IN OUT NOCOPY sumCur);
END;
CREATE OR REPLACE PACKAGE BODY testpkg AS
function totalNo (
dn IN varchar2)
RETURN NUMBER IS
summ NUMBER(10);
sql_statement VARCHAR2(100);
dno NUMBER;
BEGIN
SELECT deptno into dno from dept where dname = dn;
sql_statement := 'SELECT count(1) FROM emp where deptno = dno ' ;
EXECUTE IMMEDIATE sql_statement INTO summ ;
RETURN summ;
END totalno;
procedure test(
sum_cv IN OUT NOCOPY sumCur) AS
sql_statement VARCHAR2(100);
BEGIN
sql_statement :='SELECT dname, totalNo(dname) from dept ';
OPEN sum_cv FOR sql_statement ;
END test;
END;
Thanks
|
|
|
|
| Re: pass number and character in dynamic sql [message #37064 is a reply to message #37059] |
Mon, 14 January 2002 10:46  |
Suresh Vemulapalli
Messages: 624
Registered: August 2000
|
Senior Member |
|
|
compile ths package body..
CREATE OR REPLACE PACKAGE BODY testpkg AS
function totalNo (
dn IN varchar2)
RETURN NUMBER IS
summ NUMBER(10);
sql_statement VARCHAR2(100);
dno NUMBER;
BEGIN
SELECT deptno into dno from dept where dname = dn;
sql_statement := 'SELECT count(1) FROM emp where deptno = :dno ' ;
EXECUTE IMMEDIATE sql_statement INTO summ using dno;
RETURN summ;
END totalno;
procedure test(
sum_cv IN OUT NOCOPY sumCur) AS
sql_statement VARCHAR2(100);
BEGIN
sql_statement :='SELECT dname, testpkg.totalNo(dname) tot_dept from dept ';
OPEN sum_cv FOR sql_statement ;
END test;
END;
|
|
|
|
] 
Search
Help
Members
Register
Login
Home
