Need Help on Creating FUNCTION [message #670520] |
Tue, 10 July 2018 09:17 |
|
vharish006
Messages: 70 Registered: August 2015 Location: Chicago
|
Member |
|
|
Hi All,
I'm new to PL/SQL and trying to create a function that will be used for updating some columns.
Please let me know if this correct or should we write it in a different way.
/*
FUNCTION Get_updated_recs(p_class_id)
RETURN SUB_REC_TYPE
IS
p_sub_rec SUB_REC_TYPE;
BEGIN
IF p_class_id = 'Business'
THEN
p_sub_rec.Business_General := 'x';
END IF;
BEGIN
IF p_class_id='The_Arts'
THEN
p_sub_rec.Arts_general='x';
END IF;
BEGIN
IF p_class_id='ProQuest Catalog news'
THEN
p_sub_rec.All Products='x';
END IF;
BEGIN
IF p_class_id='General,K12&Public Library'
THEN
p_sub_rec.K12&Public Library General='x'
END IF;
RETURN p_sub_rec;
END get_updated_recs; */
Thanks for your help.
|
|
|
Re: Need Help on Creating FUNCTION [message #670521 is a reply to message #670520] |
Tue, 10 July 2018 09:38 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
What is the type SUB_REC_TYPE defined as and p_sub_rec.K12&Public Library General is not a valid column name. Only alphanumeric characters from your database character set and the underscore (_), dollar sign ($), and pound sign (#). all other characters are illegal. so if we assume the variable is actually K12_Public_Library_General then the following code will work.
CREATE OR REPLACE FUNCTION Get_updated_recs(P_class_id IN VARCHAR2)
RETURN Sub_rec_type
IS
P_sub_rec Sub_rec_type;
BEGIN
CASE P_class_id
WHEN 'Business' THEN
P_sub_rec.Business_general := 'x';
WHEN 'The_Arts' THEN
P_sub_rec.Arts_general := 'x';
WHEN 'ProQuest Catalog news' THEN
P_sub_rec.All_products := 'x';
WHEN 'General,K12&Public Library' THEN
P_sub_rec.K12_public_library_general := 'x';
END CASE;
RETURN P_sub_rec;
END Get_updated_recs;
[Updated on: Tue, 10 July 2018 09:54] Report message to a moderator
|
|
|
|
|
|
|
|
Re: Need Help on Creating FUNCTION [message #670527 is a reply to message #670524] |
Tue, 10 July 2018 09:58 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
vharish006 wrote on Tue, 10 July 2018 15:48Hi Bill,
SUB_REC_TYPE is VARCHAR
No it's not - it's some form of record.
The record may contain individual items that are varchar (hopefully varchar2) but the record itself isn't varchar.
|
|
|
|
Re: Need Help on Creating FUNCTION [message #670529 is a reply to message #670525] |
Tue, 10 July 2018 10:01 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
vharish006 wrote on Tue, 10 July 2018 15:50Hi Black Swan,
Thanks for your response.
You mean to write it in form of Update Statement??Please let know
But this might be a ongoing process so I thought I should create a function and use it
Thanks
BS means you need to use the PL/SQL assignment operator (:=) to assign values to PL/SQL variables not the equality operator (=).
you got it right in the first IF but wrong in all the others.
That code is an utter uncompilable mess - Bill has pointed out some other issues as well.
|
|
|
Re: Need Help on Creating FUNCTION [message #670530 is a reply to message #670526] |
Tue, 10 July 2018 10:05 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
vharish006 wrote on Tue, 10 July 2018 15:53The other process updates just column the column with x on a different column based on this condition.
e.g: if Business has the value 'x' then it needs to be translated to Business General for these columns
That's vague - are you talking about updating columns in a table?
Where does the class_id come from?
Why are you using record types?
What is the actual code you are trying to plug this in to?
|
|
|
|
|
Re: Need Help on Creating FUNCTION [message #670545 is a reply to message #670533] |
Wed, 11 July 2018 03:43 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
vharish006 wrote on Tue, 10 July 2018 19:34That's vague - are you talking about updating columns in a table?
Well my requirement is to update a column in a table
A column? Or multiple columns?
Cause if it's a single column then why is that function setting multiple different items in a record type.
vharish006 wrote on Tue, 10 July 2018 19:34
Where does the class_id come from?
From a file.
And how are you getting the value out of the file?
|
|
|