Stored procedures are subprogrammes stored in the database and can be called &executee multiple times wherein an application procedure is the one being used for a particular application same is the way for function.
Both can be executed any number of times. Only difference is that stored procedures/ functions are stored in database in complied format while the application procedures/functions are not in precomplied format and at run time has to be compiled.
ROWID - Hexa decimal number each and every row having unique.Used in searching.
ROWNUM - It is a integer number also unique for sorting Normally TOP N Analysys.
Other Psudo Column are
NEXTVAL,CURRVAL Of sequence are some exampls
psudo columns are default columns provided by oracle
One must declare an identifier before referencing it. Once it is declared it can be referred even before defining it in the PL/SQL. This rule applies to function and procedures also.
s Distributed database system based on 2 phase commit,one query is independent of 2 nd query so of course we can run.
CREATE OR REPLACE PROCEDURE ddl_create_proc (p_table_name IN VARCHAR2)
l_stmt := 'create table '|| p_table_name || ' as (select * from emp )';
execute IMMEDIATE l_stmt;
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('exception '||SQLERRM || 'message'||sqlcode);