Home » SQL & PL/SQL » SQL & PL/SQL » pl/sql views
icon5.gif  pl/sql views [message #230336] Wed, 11 April 2007 10:14 Go to next message
thorin666
Messages: 144
Registered: March 2007
Senior Member

I want to know where are stored the procedures, i am noob on pl/sql and i want to create 1 procedure, and after that i want to use a view to see all procedures i created to know witch one modify.
how can i do that??
Re: pl/sql views [message #230340 is a reply to message #230336] Wed, 11 April 2007 10:35 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
Are you looking for USER_PROCEDURES.


Regards
Taj
Re: pl/sql views [message #230341 is a reply to message #230336] Wed, 11 April 2007 10:36 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can use the USER_SOURCE / ALL_SOURCE views to see the source code, and USER_/ALL_PROCEDURES for an overview of what packages, procedures and functions exist.
Re: pl/sql views [message #230343 is a reply to message #230340] Wed, 11 April 2007 10:48 Go to previous messageGo to next message
thorin666
Messages: 144
Registered: March 2007
Senior Member

yes, that's the view that i was searching for
i can "play" too with all_procedures and dba_procedures i think

thnx a lot!!!
Re: pl/sql views [message #230391 is a reply to message #230336] Wed, 11 April 2007 14:09 Go to previous messageGo to next message
thorin666
Messages: 144
Registered: March 2007
Senior Member

i had played viewing and searching the correct procedure, now how can i see the code of a procedure? sorry but i am really noob, i am reading documents and all teach me how to create a procedure but i cant find how to call it and how can edit an exist procedure

thnx again
Re: pl/sql views [message #230439 is a reply to message #230391] Thu, 12 April 2007 00:32 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
You should go through below link
http://www.techonthenet.com/oracle/procedures.php

http://download-uk.oracle.com/docs/cd/B14117_01/appdev.101/b10807/13_elems036.htm

Create Procedure
SQL> create procedure TAJ
  2  is
  3  begin
  4  dbms_output.put_line ('HELLO ORAFAQ');
  5  end;
  6  /

Procedure created.

SQL> show err
No errors.
SQL> set SERVEROUTPUT ON
SQL> exec taj
HELLO ORAFAQ

PL/SQL procedure successfully completed.

SQL> --if you want to edit.
SQL> create or REPLACE procedure taj
  2  is
  3  begin
  4  dbms_output.put_line ('www.orafaq.com');
  5  end;
  6  /

Procedure created.

SQL> show err
No errors.
SQL> exec taj
www.orafaq.com

PL/SQL procedure successfully completed.

SQL>--for view procedure code.
SQL> select TEXT
  2  from user_source
  3  where name = 'TAJ' and type = 'PROCEDURE';

TEXT
--------------------------------------------------------------------------------

procedure taj
is
begin
dbms_output.put_line ('www.orafaq.com');
end;


SQL> set long 1000000
SQL> select dbms_metadata.get_ddl('PROCEDURE','TAJ','TAJ') from dual;

DBMS_METADATA.GET_DDL('PROCEDURE','TAJ','TAJ')
--------------------------------------------------------------------------------


  CREATE OR REPLACE PROCEDURE "TAJ"."TAJ"
is
begin
dbms_output.put_line ('www.orafaq.com');
end;

SQL>-procedure validity( status)

SQL> select STATUS
  2  from user_objects
  3  where object_name = 'TAJ'
  4  and object_type = 'PROCEDURE';

STATUS
-------
VALID



Regards
Taj
Re: pl/sql views [message #230669 is a reply to message #230336] Thu, 12 April 2007 09:56 Go to previous message
thorin666
Messages: 144
Registered: March 2007
Senior Member

Taj you're a genious!!! you explain me a lot of things with that examples

thnx again and twice thanks!!!

Previous Topic: Materialized View ORGANIZATION
Next Topic: help with pl/sql proc
Goto Forum:
  


Current Time: Sun Dec 11 08:01:19 CST 2016

Total time taken to generate the page: 0.11337 seconds