| Dynamic string substitution [message #10742] |
Fri, 13 February 2004 10:53  |
Alexander
Messages: 109
Registered: May 2000
|
Senior Member |
|
|
Hi... I have a cursor that I am changing to be more dynamic.
I am having problem with a part of the code, and don't know why it won't work...
It gives an error saying the table (e.g., vTable) being inserted into does not exist.
I suspect that there is also a problem with how I defined the sequence table also...
Can someone look it over and modify the syntax... I would appreciate it.
vTable := '';
vTable := SUBSTR (v_LASTNAME, 1, 1);
vSeq := '';
vSeq := 'Eligibility_' || vTable || '_Seq.NEXTVAL,';
vTable := 'Eligibility_' || vTable;
-- process fetched rows
Insert Into vTable(.....) values (vSeq......)
|
|
|
|
| Re: Dynamic string substitution [message #10744 is a reply to message #10742] |
Fri, 13 February 2004 11:13  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
You have to use dynamic SQL when you are substituting object names (table_name, column_name, etc.).
execute immediate 'insert into ' || vTable || ' (a, b, c) values (' || vSeq || ', :pb, :pc)' using v_b, v_c;
where v_b and v_c are variables containing values. If the other values are constants, then you do not need to use the USING clause.
|
|
|
|
|
|
| Re: Dynamic string substitution [message #10765 is a reply to message #10744] |
Mon, 16 February 2004 11:23 |
Alexander
Messages: 109
Registered: May 2000
|
Senior Member |
|
|
Hummm... I thought I had it, but I guess I don't....
I tried using the code you provided, but nothing works...
Could you take my existing code, and make the appropriate changes... I am not correctly following the example....
*********************************
vTable := '';
vTable := SUBSTR (v_LASTNAME, 1, 1);
vSeq := '';
vSeq := 'TableName_' || vTable || '_Seq.NEXTVAL ';
vTable := 'TableName_' || vTable;
-- process fetched rows
vString := 'Insert Into ' || vTable || ' ( ESequenceNumber, ISequenceNumber) VALUES ( ' || vSeq || ', v_ISEQUENCENUMBER )';
execute immediate vString;
|
|
|
|
| Re: Dynamic string substitution [message #10780 is a reply to message #10765] |
Tue, 17 February 2004 02:42 |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
It would really help if you would provide your complete code, instead of just a small piece. Hopefully you can get what you need from the following example where the relevant parts should be similar to what you are trying to do.
scott@ORA92> CREATE TABLE TableName_d
2 (ESequenceNumber NUMBER,
3 ISequenceNumber NUMBER)
4 /
Table created.
scott@ORA92> CREATE SEQUENCE Tablename_d_seq
2 /
Sequence created.
scott@ORA92> CREATE OR REPLACE PROCEDURE your_procedure
2 (v_LastName IN VARCHAR2,
3 v_ISequenceNumber IN NUMBER)
4 AS
5 vTable VARCHAR2(30);
6 vSeq VARCHAR2(30);
7 vString VARCHAR2(4000);
8 BEGIN
9 vTable := 'TableName_' || SUBSTR (v_LastName, 1, 1);
10 vSeq := vTable || '_Seq.NEXTVAL';
11 vString := 'Insert Into ' || vTable
12 || ' ( ESequenceNumber, ISequenceNumber) VALUES ( '
13 || vSeq || ',' || v_ISequenceNumber || ')';
14 DBMS_OUTPUT.PUT_LINE (vString);
15 EXECUTE IMMEDIATE vString;
16 END your_procedure;
17 /
Procedure created.
scott@ORA92> SHOW ERRORS
No errors.
scott@ORA92> SET SERVEROUTPUT ON
scott@ORA92> EXECUTE your_procedure ('Dumas', 2)
Insert Into TableName_D ( ESequenceNumber, ISequenceNumber) VALUES ( TableName_D_Seq.NEXTVAL,2)
PL/SQL procedure successfully completed.
scott@ORA92> SELECT * FROM tablename_d
2 /
ESEQUENCENUMBER ISEQUENCENUMBER
--------------- ---------------
1 2
|
|
|
|
| Re: Dynamic string substitution [message #10783 is a reply to message #10780] |
Tue, 17 February 2004 04:25 |
Alexander
Messages: 109
Registered: May 2000
|
Senior Member |
|
|
Thank you much... After you posted your reply I finally noticed that my declare variables where sized too short - urgh.... Sometimes you just can't see the trees from the forest.
Thanks again!
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
