Stored Procedure ORA-00917: missing comma 2

[saustin]

Hi,
Have this stored procedure which compiles, but am getting a missing comma error when running it. Can anyone spot the punctuation error ? Thanks, Steve.

(
u_username IN VARCHAR2,
u_oraclename IN VARCHAR2,
u_fullname IN VARCHAR2,
u_emailid IN VARCHAR2,
u_company IN VARCHAR2,
u_sessionname IN VARCHAR2,
u_department IN VARCHAR2
)
IS

cid INTEGER;
rows_processed NUMBER;

BEGIN
cid := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cid, 'INSERT INTO USEREMAIL VALUES (' || u_username || ', ' || u_oraclename || ', ' || u_fullname || ', ' || u_emailid || ', ' || u_company || ', ' || u_sessionname || ', ' || u_department ||')', DBMS_SQL.V7);
rows_processed := DBMS_SQL.EXECUTE(cid);
DBMS_SQL.CLOSE_CURSOR(cid);
COMMIT;

EXCEPTION
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(cid);
RAISE;
END;

 

[nagornyi]

Looks like you are inserting less columns in the usermail table then the table has. If so, you need to specify explicitly which columns you are inserting.

 

[saustin]

Thanks for the reply Nagornyi !

There are only seven fields but did change the sp to
.....
(
username IN VARCHAR2,
oraclename IN VARCHAR2,
fullname IN VARCHAR2,
emailid IN VARCHAR2,
company IN VARCHAR2,
sessionname IN VARCHAR2,
department IN VARCHAR2
)
IS

cid INTEGER;
rows_processed NUMBER;

BEGIN
cid := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cid, 'INSERT INTO USEREMAIL (username, oraclename, fullname, emailid, company, sessionname, department) VALUES ('|| username || ',' || oraclename ||',' || fullname ||',' || emailid ||',' || company ||',' || sessionname ||',' || department ||')', DBMS_SQL.V7);
rows_processed := DBMS_SQL.EXECUTE(cid);
DBMS_SQL.CLOSE_CURSOR(cid);
COMMIT;

EXCEPTION
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(cid);
RAISE;
END;

Am now getting the nORA-00984 columns not allow here error message which seem's to me seem's like it IS still confused about columns. Thanks, Steve.

 

[saustin]

Hi nagornyi,
Cleaned the sp up a little with....
(
v_username IN VARCHAR2,
v_oraclename IN VARCHAR2,
v_fullname IN VARCHAR2,
v_emailid IN VARCHAR2,
v_company IN VARCHAR2,
v_sessionname IN VARCHAR2,
v_department IN VARCHAR2
)
IS

cid INTEGER;
rows_processed NUMBER;

BEGIN
cid := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cid, 'INSERT INTO USEREMAIL (USERNAME, ORACLENAME, FULLNAME, EMAILID, COMPANY, SESSIONNAME, DEPARTMENT) VALUES ('|| v_username || ',' || v_oraclename ||',' || v_fullname ||',' || v_emailid ||',' || v_company ||',' || v_sessionname ||',' || v_department ||')', DBMS_SQL.V7);
rows_processed := DBMS_SQL.EXECUTE(cid);
DBMS_SQL.CLOSE_CURSOR(cid);
COMMIT;

EXCEPTION
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(cid);
RAISE;
END;

but am STILL getting the columns not allowed error message. Steve.

 

[sem]

Try to check the statement: string literals are not quoted, thus they're handled like column names. You should either bind variables or add quotes

Regards, Dima

http://seminihin.narod.ru

 

[jaggiebunnet]

Why exactly are you using dynamic sql to do the insert? Your whole code could just be:

(
  v_username IN VARCHAR2,
  v_oraclename IN VARCHAR2,
  v_fullname IN VARCHAR2,
  v_emailid IN VARCHAR2,
  v_company IN VARCHAR2,
  v_sessionname IN VARCHAR2,
  v_department IN VARCHAR2
  )
IS

BEGIN
  INSERT INTO USEREMAIL (USERNAME, ORACLENAME, FULLNAME,         EMAILID, COMPANY, SESSIONNAME, DEPARTMENT) 
VALUES (v_username, v_oraclename, v_fullname, v_emailid, v_company, v_sessionname, v_department);

END;

 

[saustin]

Hi Dima,
Many thanks for your reply. Have corrected the quotes and this is working perfectly.
(
v_username IN VARCHAR2,
v_oraclename IN VARCHAR2,
v_fullname IN VARCHAR2,
v_emailid IN VARCHAR2,
v_company IN VARCHAR2,
v_sessionname IN VARCHAR2,
v_department IN VARCHAR2
)
aS

cid INTEGER;
rows_processed NUMBER;

BEGIN
cid := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cid, 'INSERT INTO USEREMAIL (USERNAME, ORACLENAME, FULLNAME, EMAILID, COMPANY, SESSIONNAME, DEPARTMENT) VALUES ('''|| v_username || ''',''' || v_oraclename ||''',''' || v_fullname ||''',''' || v_emailid ||''',''' || v_company ||''',''' || v_sessionname ||''',''' || v_department ||''')', DBMS_SQL.V7);
rows_processed := DBMS_SQL.EXECUTE(cid);
DBMS_SQL.CLOSE_CURSOR(cid);
COMMIT;

EXCEPTION
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(cid);
RAISE;
END;

Question, do you prefer to bind variables as opposed to the solution above ? Have seen this before but do not understand the performance difference. Steve.

 

[saustin]

Hi Jaggiebunnet,
Thanks for your reply. Have seen your in Dov's vb oracle book and of course it works. The reason for the dynamic sql is only prior experience. Is there any performance boost or technical preference for the .....

BEGIN
INSERT INTO USEREMAIL (USERNAME, ORACLENAME, FULLNAME, EMAILID, COMPANY, SESSIONNAME, DEPARTMENT)
VALUES (v_username, v_oraclename, v_fullname, v_emailid, v_company, v_sessionname, v_department);

Thanks, Steve.

 

[sem]

The main difference is that when using bind variables you have completely the same already parsed statement, thus you save both time for parsing and space in shared pool. When your statement is executed multiple times the gain is significant. Though I also don't understand the need in dynamic sql, which normally is used when user/table/column names are not known beforehand and must be calculated during runtime. In your situation you may just write:

INSERT INTO USEREMAIL (USERNAME, ORACLENAME, FULLNAME, EMAILID, COMPANY, SESSIONNAME, DEPARTMENT) 
VALUES 
( v_username , v_oraclename, v_fullname, v_emailid, v_company, v_sessionname,v_department )

Regards, Dima

http://seminihin.narod.ru

 

[jaggiebunnet]

hi saustin

As Dima says above. You should always strive to use bind variables.

By creating your insert statement as dynamic sql you give it more overhead. I "borrowed" a list of reasons why you should use static as opposed to dynamic when you can, from Tom Kyte's website, asktom.oracle.com:

o speed
o ease of coding
o ease of maintenance
o automatic dependency tracking (if you statically reference something, it is recorded in the data dictionary and you can query up that fact. If you dynamically reference it -- it is NOT recordable)
o ease of debugging/testing (with dynamic sql, the query isn't generated until runtime -- will that query work? I don't know until i run EVERY possible combination. With STATIC sql I know at compile time that its valid sql)

hth
Jaggie

 

[sem]

Actually the only word I can not agree with is ALWAYS. In OLAP systems using bind variables is almost a no-no, because specifying explicit values in many cases improves performance by allowing optimizer to choose the most efficient execution plan.

Regards, Dima

http://seminihin.narod.ru

 

[saustin]

Hi Dima and Jaggie,
Thank you for the advice. Have changed the stored procedure and it works great. Please consider me converted to the bind variable side of the force.
Always looking to improve performance.

Best regards,

Steve.