Home » SQL & PL/SQL » SQL & PL/SQL » pass number and character in dynamic sql
pass number and character in dynamic sql [message #37059] Mon, 14 January 2002 07:34 Go to next message
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 Go to previous message
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;
Previous Topic: X$ tables
Next Topic: %ROWTYPE question
Goto Forum:
  


Current Time: Mon Aug 03 04:23:23 CDT 2020