OraFAQ Forum: SQL & PL/SQL » Passing a text string value into an insert statement

Home » SQL & PL/SQL » SQL & PL/SQL » Passing a text string value into an insert statement

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

Passing a text string value into an insert statement [message #9888]

Thu, 11 December 2003 11:47

ally
Messages: 6
Registered: November 2003

Junior Member

Help!

I am trying to insert into a table. Using a cursor I am fetching the records from a table and populating it into my publisher_tmp table. The values I'm inserting are values returned from the cursor. I am having problems representing the string when the value from the variable has an apostrophe. For example when pub_desc contains a string that has an apostrophe as input oracle fails and returns me 'ORA-00917: missing comma.' I know why it's failing. It's because when I pass the value with an apostrophe, oracle can't parse it when it encounters the apostrophe in the text string. I don't know how to fix the problem. Below is what I'm trying to do. I hope someone out there knows what I'm talking about and can help me. Thanks!

example: pub_desc contains the value Ally's
my_sql := 'INSERT INTO publisher_tmp'
|| '('
|| ' publisher_id'
|| ', ck_id'
|| ', description'
|| ', create_date'
|| ', enabled'
|| ')'
|| ' VALUES '
|| '('
|| '''' || pub_id || ''''
|| ',''' || new_pub_ck_id || ''''
|| ', ''' || pub_desc || ''''
|| ',''' || pub_date || ''''
|| ', 1'
|| ')';

EXECUTE IMMEDIATE my_sql;

Report message to a moderator

Re: Passing a text string value into an insert statement [message #9889 is a reply to message #9888]

Thu, 11 December 2003 12:09

Art Metzer
Messages: 2480
Registered: December 2002

Senior Member

Ally,

Instead of

DECLARE
    CURSOR c_pub IS
        SELECT mt.publisher_id
        ,      mt.new_pub_ck_id
        ,      mt.pub_desc
        ,      mt.pub_date
        FROM   my_table      mt
    ;
    my_sql          VARCHAR2(1000);
BEGIN
    FOR x IN c_pub LOOP
        my_sql := 'INSERT INTO publisher_tmp'
        || '('
        || ' publisher_id'
        || ', ck_id'
        || ', description'
        || ', create_date'
        || ', enabled'
        || ')'
        || ' VALUES '
        || '('
        || '''' || x.pub_id || ''''
        || ',''' || x.new_pub_ck_id || ''''
        || ', ''' || x.pub_desc || ''''
        || ',''' || x.pub_date || ''''
        || ', 1'
        || ')';
        EXECUTE IMMEDIATE my_sql;
    END LOOP;
    COMMIT;
END;
/

...where you have to mess with escaping single quotes, counting out quotation marks, dynamic SQL, and matching up different data types, why not just code the following?

INSERT INTO publisher_tmp (
    publisher_id
,   ck_id
,   description
,   create_date
,   enabled
)
SELECT mt.publisher_id
,      mt.new_pub_ck_id
,      mt.pub_desc
,      mt.pub_date
,      1
FROM   my_table      mt
/
  
COMMIT
/

HTH,

A.

Report message to a moderator

Re: Passing a text string value into an insert statement [message #9890 is a reply to message #9889]

Thu, 11 December 2003 12:48

ally
Messages: 6
Registered: November 2003

Junior Member

Art-

I'm trying upgrade a table by creating a temporary table and moving the data over to the new table.
Below is what I am actually trying to do. I've thought of doing it your way but when when I insert a record into the com_keys table (which is shown below) I need to be able to return the new ck_id and insert it into my pub_tmp table. I don't know how I can do that if I use an INSERT-SELECT statement.

SASchema.create_table
(
'pub_tmp'
, ' publisher_id INT NOT NULL'
|| ', ck_id INT NOT NULL'
|| ', description VARCHAR (128) NOT NULL'
|| ', create_date DATE NOT NULL'
|| ', enabled INT NOT NULL'
);

SELECT COUNT(*) INTO pub_num
FROM publisher;

IF (pub_num > 0) THEN
OPEN pub_table_cursor FOR
'SELECT * FROM publisher';

LOOP

FETCH pub_table_cursor INTO pub_id, pub_desc, pub_date;
EXIT WHEN pub_table_cursor%NOTFOUND;

INSERT INTO com_keys
(
public_key
, private_key
)
VALUES
(
'UPDATE_ME', 'UPDATE_ME'
)
RETURNING ck_id into new_pub_ck_id;

my_sql := 'INSERT INTO pub_tmp'
|| '('
|| ' publisher_id'
|| ', ck_id'
|| ', description'
|| ', create_date'
|| ', enabled'
|| ')'
|| ' VALUES '
|| '('
|| '''' || pub_id || ''''
|| ',''' || new_pub_ck_id || ''''
|| ', ''' || pub_desc || ''' || '''
|| ',''' || pub_date || ''''
|| ', 1'
|| ')';

EXECUTE IMMEDIATE my_sql;
commit;

Thanks,
ally

Report message to a moderator

Previous Topic:	How to Increment the value while inserting?
Next Topic:	Cannot Get Correct SQL

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 07:34:01 CDT 2024