CODEWITHSUNDEEP
oracle-databaseplsqloracle10gsyntax-error
Sesuraj
Sesuraj

Reputation: 7

Procedure created with compilation errors in oracle.?

I wrote simple stored procedure in oracle. but its shows procedure created with compilation errors.

My code is:

CREATE OR REPLACE PROCEDURE PRC_SELECT
AS
BEGIN
SELECT * FROM tb_name;
END;
/

Please help me to solve this problem.

Upvotes: 0

Views: 1064

Answers (3)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

If you really want to read all the rows in tb_name you could try:

CREATE OR REPLACE PROCEDURE PRC_SELECT
AS
BEGIN
  FOR aRow IN (SELECT * FROM tb_name)
  LOOP
    DBMS_OUTPUT.PUT_LINE('Retrieved ' || aRow.SOME_FIELD);
  END LOOP;
END;

Note that this may produce a lot of output if there are many rows in tb_name.

Share and enjoy.

Upvotes: 0

HP Frei
HP Frei

Reputation: 126

a procedure cannot do a select * back to the screen. you would have to bulk collect it into a collection and return it or open a cursor and return that. a procedure is pl/sql, which doesn't directly write to the screen. if you want it, you can use dbms_output.put_line to write each record. you might also want to look into pipelined functions, which can be used in SQL later. it depends what you're requirements are really.

Upvotes: 0

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

This is a very basic skeleton, for your requirement.

CREATE OR REPLACE PROCEDURE PRC_SELECT(p_OLD IN VARCHAR2)
AS
my_rec tb_name%rowtype;
BEGIN
SELECT * into my_rec  FROM tb_name WHERE old = p_OLD;
END;
/

my_rec will be created as a PL/SQL type whose structure would be table tb_name's structure!

Upvotes: 2

Related Questions