Skip Ribbon Commands
Skip to main content
Navigate Up
Sign In

Quick Launch

Average Rating:

(1 Rating)
facebook Twitter
Email
Print Bookmark Alert me when this article is updated

Feedback

HOW TO: Pass parameters containing single quotes to stored procedure in a PowerCenter mapping
Problem Description

The PowerCenter session fails when passing parameters with two single quotes even though outside Informatica the stored procedure is executed using two single quotes.

What is the correct syntax for passing parameters to the stored procedure from stored procedure transformation when the call text contains a single quote?

Cause
Solution

To use a single quote in the stored procedure Call Text use only one instance of CHR (39) for each single quote.

Example:

'insert into tera1 values( ' ||chr(39)||'test2'||chr(39) ||')'

More Information

Sample Stored Procedures:

Teradata:

create table tera25 (a varchar(20));
CREATE PROCEDURE tera8 (IN vin1 varchar(3000), OUT return_value integer)
BEGIN 
 call dbc.sysexecsql(:vin1);
END;

Oracle:

create table tera1 (a varchar2(20));
CREATE OR REPLACE PROCEDURE tera8
(
 vin1 IN     VARCHAR2,
 return_value OUT number)
IS
BEGIN
execute immediate(vin1);
END tera8;
/

To call these stored procedures outside Informatica:

Teradata:

call tera8 ('insert into tera1 values (''A2'')',m) ;

Oracle:

set serveroutput on
var x varchar2(20)
exec tera8('insert into tera1 values(''a'')',:x);

DECLARE
x  VARCHAR2(50) ;
y  number;
BEGIN
x := 'insert into tera1 values(''a'')';
tera8(x,y);
END;
/

In theses examples the single quote are passed with escape character single quote.

The following syntax in the Mapping will result in a session error:

'insert into tera1 values( ' ||chr(39)|| chr(39) ||'l '||chr(39)|| chr(39) ||')'

Applies To
Product: PowerCenter
Problem Type:
User Type: Administrator
Project Phase:
Product Version: PowerCenter
Database:
Operating System:
Other Software:

Reference
Attachments
Last Modified Date:5/27/2009 1:02 PMID:105541
People who viewed this also viewed

Feedback

Did this KB document help you?



What can we do to improve this information (2000 or fewer characters)