Home » SQL & PL/SQL » SQL & PL/SQL » PASS A TABLE NAME AS A PARAMETER IN PL/SQL? (ORACLE 10G, WINDOW XP)
PASS A TABLE NAME AS A PARAMETER IN PL/SQL? [message #329994] Fri, 27 June 2008 04:27 Go to next message
vlinh
Messages: 11
Registered: June 2008
Junior Member
TABLE COURSE(
	COURSE_NO      NUMBER(38),
	DESCRIPTION    VARCHAR2(50),
	COST         NUMBER(9,2),
	PREREQUISITE   NUMBER(8)
)

CREATE OR REPLACE FUNCTION TOTAL_ROWS
(V_COST COURSE.COST%TYPE, V_TABLE USER_TABLES.TABLE_NAME%TYPE)
RETURN NUMBER
IS
V_RESULT NUMBER:=0;
BEGIN
	EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||V_TABLE||'WHERE COST= '||V_COST
	INTO V_RESULT;
	RETURN V_RESULT;
END;
.
/

DECLARE 
NUM1 NUMBER;
BEGIN
NUM1:=TOTAL_ROWS(1195,'COURSE');
DBMS_OUTPUT.PUT_LINE('TOTAL ROWS:'||NUM1);
END;
.
/



when i execute this function, i have an error like this:
ERROR at line 1:
ORA-00933: SQL command not properly ended
ORA-06512: at "STUDENT.TOTAL_ROWS", line 7
ORA-06512: at line 4

how to solve this problem?
SORRY FOR MY ENGLISH.

[Updated on: Fri, 27 June 2008 04:33]

Report message to a moderator

Re: PASS A TABLE NAME AS A PARAMETER IN PL/SQL? [message #329995 is a reply to message #329994] Fri, 27 June 2008 04:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You should post this this way:
SQL> create TABLE COURSE(
  2   COURSE_NO      NUMBER(38),
  3   DESCRIPTION    VARCHAR2(50),
  4   COST         NUMBER(9,2),
  5   PREREQUISITE   NUMBER(8)
  6  )
  7  /

Table created.

SQL> CREATE OR REPLACE FUNCTION TOTAL_ROWS
  2  (V_COST STUDENT.COST%TYPE, V_TABLE USER_TABLES.TABLE_NAME%TYPE)
  3  RETURN NUMBER
  4  IS
  5  V_RESULT NUMBER:=0;
  6  BEGIN
  7   EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||V_TABLE||'WHERE COST= '||V_COST
  8   INTO V_RESULT;
  9   RETURN V_RESULT;
 10  END;
 11  /

Warning: Function created with compilation errors.

SQL> sho err
Errors for FUNCTION TOTAL_ROWS:
LINE/COL
---------------------------------------------------------------------------------
ERROR
---------------------------------------------------------------------------------
0/0
PL/SQL: Compilation unit analysis terminated
2/9
PLS-00201: identifier 'STUDENT.COST' must be declared

Is your table COURSE or STUDENT?
Does STUDENT contains a COST column?

And don't put your title in UPPER case.

Regards
Michel

[Updated on: Fri, 27 June 2008 04:33]

Report message to a moderator

Re: PASS A TABLE NAME AS A PARAMETER IN PL/SQL? [message #329999 is a reply to message #329995] Fri, 27 June 2008 04:41 Go to previous messageGo to next message
vlinh
Messages: 11
Registered: June 2008
Junior Member
TABLE "COURSE" IS AN TABLE IN "STUDENT" DATABASE
Re: PASS A TABLE NAME AS A PARAMETER IN PL/SQL? [message #330001 is a reply to message #329999] Fri, 27 June 2008 04:48 Go to previous messageGo to next message
vlinh
Messages: 11
Registered: June 2008
Junior Member
In this function, i want to pass 2 parameter into TOTAL_ROWS, the first(V_COST) is an column in an table V_TABLE.
Re: PASS A TABLE NAME AS A PARAMETER IN PL/SQL? [message #330002 is a reply to message #329994] Fri, 27 June 2008 04:51 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Missing a space before WHERE
Re: PASS A TABLE NAME AS A PARAMETER IN PL/SQL? [message #330004 is a reply to message #330002] Fri, 27 June 2008 04:55 Go to previous messageGo to next message
vlinh
Messages: 11
Registered: June 2008
Junior Member
thanks!
Re: PASS A TABLE NAME AS A PARAMETER IN PL/SQL? [message #330005 is a reply to message #329995] Fri, 27 June 2008 04:56 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Also, you need a space before the WHERE in the Execute Immediate line. It should look like this:
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||V_TABLE||' WHERE COST= '||V_COST
Re: PASS A TABLE NAME AS A PARAMETER IN PL/SQL? [message #330006 is a reply to message #330005] Fri, 27 June 2008 04:58 Go to previous messageGo to next message
vlinh
Messages: 11
Registered: June 2008
Junior Member
Thanks for your help! I have solved this problem!
Re: PASS A TABLE NAME AS A PARAMETER IN PL/SQL? [message #330010 is a reply to message #330006] Fri, 27 June 2008 05:26 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Of course this error can't be seen at compile time but at execution one as you first wrote before updating your post.
This is why you have to copy and paste your session.

Regards
Michel
Previous Topic: Result set
Next Topic: How can i find if two Tables are Equal to each other?
Goto Forum:
  


Current Time: Fri Dec 09 17:26:07 CST 2016

Total time taken to generate the page: 0.42816 seconds