Oracle / PLSQL: Dealing with apostrophes/single quotes in strings

Question: How can I handle apostrophes and single quotes in strings? As you know, single quotes start and terminate strings in SQL.

Answer: Now it is first important to remember that in Oracle, you enclose strings in single quotes. The first quote denotes the beginning of the string and the second quote denotes the termination of the string.

If you need to deal with apostrophes/single quotes in strings, your solution depends on where the quote is located in the string.

We'll take a look at 4 scenarios where you might want to place an apostrophe or single quote in a string.

Apostrophe/single quote at start of string

When the apostrophe/single quote is at the start of the string, you need to enter 3 single quotes for Oracle to display a quote symbol. For example:

SELECT '''Hi There'
FROM dual;

would return

'Hi There

Apostrophe/single quote in the middle of a string

When the apostrophe/single quote is in the middle of the string, you need to enter 2 single quotes for Oracle to display a quote symbol. For example:

SELECT 'He''s always the first to arrive'
FROM dual;

would return

He's always the first to arrive

Apostrophe/single quote at the end of a string

When the apostrophe/single quote is at the end of a string, you need to enter 3 single quotes for Oracle to display a quote symbol. For example:

SELECT 'Smiths'''
FROM dual;

would return

Smiths'

Apostrophe/single quote in a concatenated string

If you were to concatenate an apostrophe/single quote in a string, you need to enter 4 single quotes for Oracle to display a quote symbol. For example:

SELECT 'There' || '''' || 's Henry'
FROM dual;

would return

There's Henry

 

Oracle / PLSQL: Retrieve the value of a LONG field

Question: I have a table in Oracle that contains a field with the data type of LONG. How can I extract the contents of this LONG field?

If I run the SQL statement below, it just returns <Long>.

SELECT event_details FROM vlts_event_data;

Answer: In Oracle, LONG fields are a bit tricky. However, you can use PLSQL code to determine the value of a LONG field.

Here is an example of a function that returns the value of a LONG field called SEARCH_CONDITION. This function accepts the primary key values (owner and constraint_name fields) and returns the value of the SEARCH_CONDITION field for the selected record.

CREATE or REPLACE function Find_Value
   ( av_owner varchar2, av_cname varchar2)
   RETURN varchar2

IS
   long_var LONG;

BEGIN
   SELECT SEARCH_CONDITION INTO long_var
   FROM ALL_CONSTRAINTS
   WHERE owner = av_owner
   AND constraint_name = av_cname;

   return long_var;

END;

Once the above function has been created, you can reference this function in your SQL statement. For example,

SELECT owner, constraint_name, Find_Value(owner, constraint_name)
FROM ALL_CONSTRAINTS;

This SQL statement would retrieve the owner and constraint_name fields from the ALL_CONSTRAINTS table, as well as the value stored within the SEARCH_CONDITION field.

You can modify the Find_Value function to reference your own table and field names. You will also need to modify the function parameters to pass in your own primary key values.

 Oracle / PLSQL: Determine the length of a LONG field

Question: I have a table in Oracle that contains a field with the data type of LONG. I want to find out how many characters are stored in this LONG field for a particular record in the table.

How can I count the number of characters in a LONG data type field?

Answer: It doesn't appear that you can find the length of a LONG field in SQL. However, you can use PLSQL code to determine the length of a LONG field.

Here is an example of a function that returns the length of the LONG field called SEARCH_CONDITION. This function accepts the primary key values (owner and constraint_name fields) and returns the length of the SEARCH_CONDITION field for the selected record.

CREATE or REPLACE function Find_Length
   ( av_owner varchar2, av_cname varchar2)
   RETURN number

IS
   long_var LONG;

BEGIN

   SELECT SEARCH_CONDITION INTO long_var
   FROM ALL_CONSTRAINTS
   WHERE owner = av_owner
   AND constraint_name = av_cname;

   return length(long_var);

END;

You can modify this function to reference your table and field names. Give it a try!

Once the above function has been created, you can reference this function in your SQL statement. For example,

SELECT owner, constraint_name, Find_Length(owner, constraint_name)
FROM ALL_CONSTRAINTS;

This SQL statement would retrieve the owner and constraint_name fields from the ALL_CONSTRAINTS table, as well as the length of the SEARCH_CONDITION field.

You can modify the Find_Length function to reference your own table and field names. You will also need to modify the function parameters to pass in your own primary key values.

API to Update Customer Sites and assign Tax code in Oracle apps

API to Update Customer Sites and assign Tax code in Oracle apps

 

This below script helps to update Customer Sites and Assign Vat code/Tax Code and Vat Registration No/ Tax Registration No in Customer Master in Oracle.

This is the complete API to Update Customer Sites Information in Oracle Apps.

We are using Standard Oracle API to Achieve this Below requirement.

API to Update Customer Sites and assign Tax code in Oracle apps

DECLARE

x_return_status varchar2(10);

l_init_msg_list VARCHAR2 (1000) := FND_API.G_TRUE;

x_msg_count number(10);

x_msg_data varchar2(1200);

p_object_version_number number(10):=18;

l_cust_site_use_rec APPS.hz_cust_account_site_v2pub.cust_site_use_rec_type;

begin

fnd_global.apps_initialize(61477,61595,222);

dbms_output.put_line('API STARTED' );

mo_global.set_policy_context('S',5674);

fnd_profile.initialize(61470,61515,222);

l_cust_site_use_rec.SITE_USE_ID:=264031;

l_cust_site_use_rec.cust_acct_site_id:=225246;

l_cust_site_use_rec.TAX_CODE := ‘XX_VAT_5';

--l_cust_site_use_rec.status := 'I';

dbms_output.put_line('API STARTED EXECUTION' );

hz_cust_account_site_v2pub.update_cust_site_use(fnd_api.g_true,

l_cust_site_use_rec,

p_object_version_number,

x_return_status,

x_msg_count,

x_msg_data);IF x_return_status = 'S' THEN

dbms_output.put_line(' Now site use is active' );

ELSE

IF NVL (x_msg_count, 0) > 1 THEN

 FOR i IN 1 .. x_msg_count LOOP

 dbms_output.put_line(' Error Status ' ||x_return_status);

 dbms_output.put_line(' Error message ' ||x_msg_data);

 END LOOP;

 ELSE

 dbms_output.put_line(' Error message ' ||x_msg_data);

 END IF;

 END IF;

 commit;

 exception when others then

 dbms_output.put_line(' Error Here'||sqlcode||sqlerrm);

 end;