String manipulation

[ace333]

create or replace procedure add_row_to_test
(
aid in test.id%TYPE,
afn in test.firstname%TYPE,
aln in test.surname%TYPE
)
is
begin
/**
In this procedure I want to put single quotes around the values of aid,afn, and aln.
Any ideas how I do this ???
**/
insert into test (id,firstname,surname)
values (aid, afn,aln);
dbms_output.put_line('Added a row to the table');
commit;
end;

/

 

[taupirho]

Use this syntax:

values (chr(39) || aid || chr(39), chr(39) || afn || chr(39), chr(39) || aln || chr(39), );

 

[ace333]

This is what I tried but it did not work I'm afraid

CREATE OR REPLACE PROCEDURE add_row_to_test
(
aid IN TEST.id%TYPE,
afn IN TEST.firstname%TYPE,
aln IN TEST.surname%TYPE
)
IS
BEGIN


INSERT INTO TEST (id,firstname,surname)
values (chr(39) ||aid||chr(39) ,chr(39) ||afn||chr(39) ,chr(39) ||aln||chr(39) );
dbms_output.put_line('Added a row to the table');
COMMIT;
END;
/

EXECUTE add_row_to_test(30,xxxx,yyyy);

Got the following error:


ERROR at line 1:
ORA-06550: line 1, column 26:
PLS-00201: identifier 'XXXX' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

 

[lewisp]

Use

EXECUTE add_row_to_test(30,'xxxx','yyyy');

 

[ace333]

does not work either, you see what i want is that when a user calls a procedure or function from .net
that all the variables are treated as strings so that
the procedure/func executes correctly.

 

[ace333]

And i dont want to user to have to include single quotes around the values entered i want the stored procedure or the c# code to add the single quotes.

The above would work in normal cases easily

 

[stefanhei]

Ace,
maybe you should read the Concepts Guide and the Pro*C/C++ Procompiler Programmer's Guide.
There is no need to put single quotes around a variable declared as string or varchar2 if you use it as a bind variable.

Stefan