Monday, July 23, 2012

Create table at Run Time

create or replace procedure p1
as
begin
execute immediate 'create table table_name (column_name datatype)';
end;
/



SQL> exec p1;
ORA-01031: insufficient privileges
ORA-06512: at "schema.procedure", line 4 / line 7
ORA-06512: at line 1

oops error !!!!!!


Don't worry

Just add one line to your procedure:



create or replace procedure p1
AUTHID CURRENT_USER
as
begin
execute immediate 'create table table_name (column_name datatype)';
end;
/

SQL> exec p1;
PL/SQL procedure successfully completed.

Sunday, July 1, 2012

TRY to execute select query on tablAe , that contains BLOB datatype

sql>select * from blob_test; (in 10g)
error
SP2-0678: Column or attribute type can not be displayed by SQL*Plus

solve (try this)
-------
 select utl_raw.cast_to_varchar2(a)  from blob_test;
-- where 'a' is column_name and 'blob_test' is table_name

web stats