OraFAQ Forum: SQL & PL/SQL » URGENT PL/SQL FUNCTIONS IN SELECT STATEMENTS

Home » SQL & PL/SQL » SQL & PL/SQL » URGENT PL/SQL FUNCTIONS IN SELECT STATEMENTS

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

URGENT PL/SQL FUNCTIONS IN SELECT STATEMENTS [message #38413]

Wed, 17 April 2002 03:47

Anderson
Messages: 10
Registered: December 1999

Junior Member

I have written a function which takes one argument as below:
FUNCTION CHECKING (DPT_CODE VARCHAR2)RETURN VARCHAR2 is
DEPT VARCHAR2(4);

BEGIN
DEPT := :DPT_CODE;
if :DPT_CODE like 'C%'
then
return 'y';
else
return 'n';
end if;
END;
If I use this in the select statement as this select DPT_Code from tableName where Checking(DPT_Code) = 'y'
it doesnt work.

I get an error messege INVALID COLUMN NAME. CAN U PLEASE HELP. I HAVE PUT THIS UNDER THE PROGRAM UNITS IN REPORTS. PLEASE HELP

Report message to a moderator

Re: URGENT PL/SQL FUNCTIONS IN SELECT STATEMENTS [message #38415 is a reply to message #38413]

Wed, 17 April 2002 04:15

Jon
Messages: 483
Registered: May 2001

Senior Member

Remove the colons from in front of DPT_CODE in your function. Compile it in SQL*PLUS and rerun. If you still have a problem, DESC the table and post. In fact, post the whole series of steps from your SQL*PLUS session.

Report message to a moderator

Re: URGENT PL/SQL FUNCTIONS IN SELECT STATEMENTS [message #38419 is a reply to message #38413]

Wed, 17 April 2002 06:02

Jon
Messages: 483
Registered: May 2001

Senior Member

I created it in the database. If you don't, you can't use it in a SQL statement. I have posted exactly what I did. If you do the same (except, of course, you don't need to create the departmentses [[what a name!]] table), you should get the same results. Do exactly what I did and copy and post the results from SQL*Plus, so I can see exactly what you have done.

Report message to a moderator

Re: URGENT PL/SQL FUNCTIONS IN SELECT STATEMENTS [message #38421 is a reply to message #38413]

Wed, 17 April 2002 06:19

Anderson
Messages: 10
Registered: December 1999

Junior Member

Thank you ver very much man. It has now worked. I di what you did. (Departmentses.....TYPO) WILL CHANGE THAT.

HEy do you know where I can download SQL loader. I am trying to populate the Departmenteses table using a text file and someone said I should use something called SQL loader,

thanks

Report message to a moderator

Re: URGENT PL/SQL FUNCTIONS IN SELECT STATEMENTS [message #38429 is a reply to message #38413]

Wed, 17 April 2002 10:10

Jon
Messages: 483
Registered: May 2001

Senior Member

Are you sure you don't have it? I think it is a utility that comes with the system - not positive. Check in the bin directory, and I think its SQLLDR or something like that.

Report message to a moderator

Previous Topic:	Delete or Update, what is faster
Next Topic:	Bind Variables in DBMS_SQL - PL/SQL GURU NEEDED

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon May 06 13:45:47 CDT 2024