CODEWITHSUNDEEP
sqloracle11goracle-apex
Allan
Allan

Reputation: 140

Oracle Apex: Success With compilation error

I'm trying to create a procedure named "Greetings" in the oracle apex. The Procedure Greetings runs with some error throwing up called "Success with compilation error". Is there anything wrong with my below code.

Code:

create table tb_Animals (
    txt_Name varchar(20) Primary Key,
    int_Weight number
);

insert into tb_Animals values ('Dog',30);
insert into tb_Animals values ('Cat',15);

CREATE OR REPLACE PROCEDURE greetings 
AS 
BEGIN 
   select * from tb_Animals;
END;

Upvotes: 0

Views: 213

Answers (2)

pmdba
pmdba

Reputation: 7043

Two problems I see here:

  1. The code you provided is a SQL script that will create the procedure, not run the procedure. The error is telling you that the procedure has been created, but that it has an error. You can see the precise error by entering "SHOW ERRORS" at the sqlplus prompt after the create command completes.
  2. The problem with the procedure itself is that you have to do something with the data you've selected. It needs to be processed into variables, or used in a for/next loop for some purpose. Simply selecting data into nothing won't work - PL/SQL is a programming language, not a scripting language. See here for a beginner's guide: https://oracle-base.com/articles/misc/introduction-to-plsql. I can't offer any more specific guidance than that since you've not provided any info on what your procedure is actually trying to do.

If you just want your script to return the data you just inserted into the table as a confirmation, just run the select statement without the procedural stuff, like this (and don't forget to commit your changes!):

create table tb_Animals (
    txt_Name varchar(20) Primary Key,
    int_Weight number
);

insert into tb_Animals values ('Dog',30);
insert into tb_Animals values ('Cat',15);
commit;

select * from tb_Animals;

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 143103

In PL/SQL, you have to insert those values into something, usually a variable, However, if you're selecting the whole table contents, then it has to be something else; one option might be a refcursor. For example:

SQL> CREATE OR REPLACE PROCEDURE greetings
  2  AS
  3    rc sys_refcursor;
  4  BEGIN
  5    open rc for
  6     select * from tb_Animals;
  7  END;
  8  /

Procedure created.

SQL>

It is now correct as far as compilation is concerned, but - actual "solution" depends on what you really want to do.

Upvotes: 1

Related Questions