How to run a stored procedure in Oracle SQL Developer?

Question

I'm trying to run this stored procedure

DECLARE
  P_TICKER_SERIAL VARCHAR2(200);
  P_SECTOR_CODE   VARCHAR2(200);
  P_SOURCE_ID     VARCHAR2(200);
  P_COUNTRY_CODE  VARCHAR2(200);
  P_FILTER_TYPE   NUMBER;
  CUR_OUT         SYS_REFCURSOR;
  dbUserTable     DBUSER%ROWTYPE;
BEGIN
  P_TICKER_SERIAL :='14232';
  P_SECTOR_CODE   := '15';
  P_SOURCE_ID     := 'TDWL';
  P_COUNTRY_CODE  := 'SA';
  P_FILTER_TYPE   := 1;

  PKG_name.GET_user(
    P_TICKER_SERIAL => P_TICKER_SERIAL,
    P_SECTOR_CODE   => P_SECTOR_CODE,
    P_SOURCE_ID     => P_SOURCE_ID,
    P_COUNTRY_CODE  => P_COUNTRY_CODE,
    P_FILTER_TYPE   => P_FILTER_TYPE,
    CUR_OUT         => CUR_OUT
  );
  open CUR_OUT;
  LOOP
    FETCH CUR_OUT INTO dbUserTable;
    dbms_output.put_line(dbUserTable.email);
  END LOOP;
  CLOSE CUR_OUT;
END;
 /

But it gives me this error

Error report:
ORA-06550: line 8, column 15:
PLS-00201: identifier 'DBUSER' must be declared
ORA-06550: line 8, column 15:
PL/SQL: Item ignored
ORA-06550: line 24, column 2:
PLS-00382: expression is of wrong type
ORA-06550: line 24, column 2:
PL/SQL: SQL Statement ignored
ORA-06550: line 26, column 24:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 26, column 5:
PL/SQL: SQL Statement ignored
ORA-06550: line 27, column 28:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 27, column 7:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Any one knows what is wrong ? Thanks.

1) The GET_user procedure, most likely, opens the cursor, there is no need to open it again(cause of the PLS-00382 error) - remove open CUR_OUT statement; 2) You would need an exit loop condition exit when CUR_OUT%notfound for instance, if you do not want to hang your session forever. — Nick Krasnov
– Nick Krasnov, Commented Nov 25, 2013 at 7:55

Dba · Accepted Answer · 2013-11-25 07:15:15Z

72

Try to execute the procedure like this,

var c refcursor;
execute pkg_name.get_user('14232', '15', 'TDWL', 'SA', 1, :c);
print c;

answered Nov 25, 2013 at 7:15

Dba

6,6791 gold badge26 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Samy Louize Hanna Over a year ago

thanks for your quick reply but it give me this error : Bind Variable "c" is NOT DECLARED anonymous block completed

Dba Over a year ago

@Samy Louize Hanna, Before executing the proceudre, you need to run var c refcursor This mechanism is for assigning values to a variable.

Samy Louize Hanna Over a year ago

Error starting at line 3 in command: execute pkg_name.get_user('14232', '15', 'TDWL', 'SA', 1, :c) Error report: ORA-00904: "TDWL": invalid identifier ORA-06512: at "schema.PKG_name", line 156 ORA-06512: at line 1 00904. 00000 - "%s: invalid identifier" *Cause: *Action: Line 156 doesn't refer to my stored procedure

Dba Over a year ago

@Samy Louize Hanna, The error message looks like, there is something wrong with your procedure code.

Samy Louize Hanna Over a year ago

Line 156 doesn't refer to my stored procedure

|

Mohan · Accepted Answer · 2017-05-19 21:10:21Z

11

Consider you've created a procedure like below.

CREATE OR REPLACE PROCEDURE GET_FULL_NAME like
(
  FIRST_NAME IN VARCHAR2, 
  LAST_NAME IN VARCHAR2,
  FULL_NAME OUT VARCHAR2 
) IS 
BEGIN
  FULL_NAME:= FIRST_NAME || ' ' || LAST_NAME;
END GET_FULL_NAME;

In Oracle SQL Developer, you can run this procedure in two ways.

1. Using SQL Worksheet

Create a SQL Worksheet and write PL/SQL anonymous block like this and hit f5

DECLARE
  FULL_NAME Varchar2(50);
BEGIN
  GET_FULL_NAME('Foo', 'Bar', FULL_NAME);
  Dbms_Output.Put_Line('Full name is: ' || FULL_NAME);
END;

2. Using GUI Controls

Expand Procedures
Right click on the procudure you've created and Click Run
In the pop-up window, Fill the parameters and Click OK.

Cheers!

edited May 19, 2017 at 21:10

answered May 19, 2017 at 20:56

Mohan

5,1332 gold badges31 silver badges20 bronze badges

2 Comments

BohBah Over a year ago

sorry but the number one point gives problem 'identifier FULL_NAME must be declared'

Woodchuck Over a year ago

If I'm not mistaken, I believe you may have meant to write "...you can run this procedure in two steps" (i.e., "steps", not "ways").

Mihiraj Madhusanka · Accepted Answer · 2023-03-23 08:52:54Z

3

execute PROCEDURE_NAME
execute PRECEDURE_NAME(Parameters)

answered Mar 23, 2023 at 8:52

Mihiraj Madhusanka

881 bronze badge

1 Comment

Yunnosch Over a year ago

Please consider editing to make more obvious what additional insight you contribute beyond stackoverflow.com/a/20186667/7733418 ; ideally according to How to Answer.

serenesat · Accepted Answer · 2019-01-31 13:29:41Z

-4

-- If no parameters need to be passed to a procedure, simply:

BEGIN
   MY_PACKAGE_NAME.MY_PROCEDURE_NAME
END;

edited Jan 31, 2019 at 13:29

serenesat

4,72210 gold badges41 silver badges53 bronze badges

answered Jul 25, 2018 at 19:20

Ania

5

Collectives™ on Stack Overflow

How to run a stored procedure in Oracle SQL Developer?

4 Answers 4

9 Comments

2 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

9 Comments

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related