CODEWITHSUNDEEP
oracle-databaseplsqloracle-sqldeveloper
greyfox
greyfox

Reputation: 6616

Error Testing PL/SQL Insert Stored Proc

I'm new to Oracle and stored procedures. I'm having some issues getting a simple stored proc I wrote to run using SQL Developer.

Here is my stored proc:

CREATE OR REPLACE PROCEDURE sp_user_create(
  user_row IN t_user%ROWTYPE)
IS
BEGIN
  INSERT INTO T_USER (FIRST_NAME,LAST_NAME,EMAIL)
  VALUES(user_row.FIRST_NAME, user_row.LAST_NAME, user_row.EMAIL);
  COMMIT;

  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
      RAISE;

END sp_user_create;

When I try to run it with SQL Developer I get the following screen.

enter image description here

It complains the line USER_ROW PL/SQL RECORD is not valid but SQL Developer generated this?

enter image description here

UPDATE:

I got it working by using the following code to test.

DECLARE
  USER_ROW t_user%rowtype;
BEGIN
  --USER_ROW := ('BOB','MALAKI','[email protected]');
  USER_ROW.FIRST_NAME := 'BOB';
  USER_ROW.LAST_NAME := 'MALAKI';
  USER_ROW.EMAIL := '[email protected]';

  SP_USER_CREATE(
    USER_ROW => USER_ROW
  );
--rollback; 
END;

The question remains though can the USER_ROW be defined in a single line instead of setting each column individually? That can be tedious when there are lots of columns.

Upvotes: 0

Views: 365

Answers (2)

michaos
michaos

Reputation: 121

I have an addition to the accepted answer. It is possible to set USER_ROW by using select:

select 'BOB' as FIRST_NAME, 'MALAKI' as LAST_NAME, '[email protected]' as EMAIL 
into USER_ROW from dual;

You can also select values into USER_ROW from another table.

P.S.: Also, instead of the lines:

INSERT INTO T_USER (FIRST_NAME,LAST_NAME,EMAIL)
VALUES(user_row.FIRST_NAME, user_row.LAST_NAME, user_row.EMAIL);

in your stored procedure, you can write:

INSERT INTO T_USER VALUES user_row;

Some additional information you can see: Oracle: Insert rowtype data into another table

Upvotes: 1

Kirill Leontev
Kirill Leontev

Reputation: 10941

"PL/SQL record" is just a placeholder for actual data type, not the data type itself, as sql developer has no reliable way to determine it using data dictionary. Replace it with "t_user%rowtype", which is a type of user_row parameter.

Upvotes: 2

Related Questions