Newbie PL/SQL Question - Inserting into a table from a stored procedure

[Eraser]

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(u id 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.SQLExc eption: ORA-00900: invalid SQL statement

Can anyone please point out what is wrong with the above
procedure?

Thanks,
Eraser

[Alex Ivascu]

There doesn't seem to be nothing wrong with your statement.
It might be the way SquirrelSQL interprets it.

Alex Ivascu
"Eraser" <er****@nospam. comwrote in message
news:pa******** *************** *****@nospam.co m...

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(u id 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.SQLExc eption: ORA-00900: invalid SQL statement

Can anyone please point out what is wrong with the above
procedure?

Thanks,
Eraser

[Kris]

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(u id 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.co m...

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(u id 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.SQLExc eption: ORA-00900: invalid SQL statement

Can anyone please point out what is wrong with the above
procedure?

Thanks,
Eraser