try running from a simple sql*plus window to test: I just rana quick test
with your code and it worked:
SQLcreate table PERSONS
2 (
3 prsn_uid NUMBER,
4 prsn_first_nm VARCHAR2(20),
5 prsn_middle_nm VARCHAR2(20),
6 prsn_last_nm VARCHAR2(20)
7 );
Table created.
SQLCREATE OR REPLACE PROCEDURE insert_person(uid IN NUMBER,
2 first_nm IN VARCHAR,
3 middle_nm IN VARCHAR,
4 last_nm IN VARCHAR) IS
5 BEGIN
6 INSERT INTO PERSONS (prsn_uid, prsn_first_nm, prsn_middle_nm,
7 prsn_last_nm) values (uid, first_nm, middle_nm,
8 last_nm);
9 END insert_person;
10 /
Procedure created.
SQLset serveroutput on
SQLexec insert_person(1,'first', 'middle', 'last');
PL/SQL procedure successfully completed.
SQLCOMMIT;
Commit complete.
SQLselect * from persons;
PRSN_UID PRSN_FIRST_NM PRSN_MIDDLE_NM PRSN_LAST_NM
---------- -------------------- -------------------- --------------------
1 first middle last
SQL>
..................
I created a simple table based on your procedure, but it might be different.
--you should also look at adding exception handling in your procedure, such
as below:
CREATE OR REPLACE PROCEDURE insert_person
(
uid IN NUMBER,
first_nm IN VARCHAR,
middle_nm IN VARCHAR,
last_nm IN VARCHAR) IS
BEGIN
INSERT INTO PERSONS (prsn_uid, prsn_first_nm, prsn_middle_nm,
prsn_last_nm) values (uid, first_nm, middle_nm,
last_nm);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE ('Errors found: '||SQLERRM);
END insert_person;
/
who owns the table, who's calling the procedure: do you have privs? are
there public/private synonymns?
easiet way to debug is go to the basics: sqlplus. try inserting into the
table with the same user you are running from the java, then try the
procedure, etc....
"Eraser" <er****@nospam.comwrote in message
news:pa****************************@nospam.com...
Hello,
I'm just starting to learn PL/SQL. To get my feet wet,
I'm trying to write a simple stored procedure that takes some
values as parameters, and inserts those values into a table.
For some reason my simple procedure is not working, I'm
probably missing something simple. Here is how I'm trying to
create this procedure:
CREATE OR REPLACE PROCEDURE insert_person(uid IN NUMBER,
first_nm IN VARCHAR,
middle_nm IN VARCHAR,
last_nm IN VARCHAR) IS
BEGIN
INSERT INTO PERSONS (prsn_uid, prsn_first_nm, prsn_middle_nm,
prsn_last_nm) values (uid, first_nm, middle_nm,
last_nm);
END insert_person;
I'm not sure this is relevant, but I'm typing the above
declaration into an Java based SQL client called SquirrelSQL
(http://sourceforge.net/projects/squirrel-sql/), the
output I get after executing the above is:
Warning: Warning: execution completed with warning
SQLState: null
ErrorCode: 17110
0 Rows Updated
Query 1 elapsed time (seconds) - Total: 0.053, SQL query: 0.053,
Building output: 0
Error: java.sql.SQLException: ORA-00900: invalid SQL statement
Can anyone please point out what is wrong with the above
procedure?
Thanks,
Eraser
