PL/SQL ORACLE

A simple procedure for create a flat csv file from table

Sometimes can be useful to produce a CSV file semicolon separated from a table. Here a simple dynamic procedure that works without write sql scripts pl sql code. Just put in some parameters and execute it from everywhere. With little customizations you can add a parameter for separator, add “” for strings, columns to avoid etc. CREATEORREPLACEprocedureTABLETOCSV( P_OWNERinvarchar2,–owner of the table P_TABLENAMEinvarchar2, –tablename P_PATHinvarchar2default‘/’, — server shared path where file can be putted in P_FILENAMEinvarchar2,–exported filename P_HEADERinvarchar2default‘Y’,–flag Y/N for header presence P_DATEFORMATinvarchar2default‘dd/mm/yyyy’ –-dateformat in csv )as     sql_scriptvarchar2(2000);     initnumber:=0;     v_ownervarchar2(30):=upper(P_OWNER);     v_table_namevarchar2(100):=upper(P_TABLENAME);     flg_headervarchar2(1):=P_HEADER;     v_headervarchar2(1000);     dt_fvarchar2(10):=P_DATEFORMAT; Read More

AUTHID property

The AUTHID property of a stored PLSQL unit, determines the authorization under which a PLSQL subprogram operates at run-time. You can use two clause:  . CURRENT_USER : when the subprogram is executed , it will run using the rights of the person that invoked the code, not with the creator’s rights  . DEFINED : when the subprogram is executed , it will run using the rights of the person that created the code, not with the invoker’s rights (triggers and views always behave in                         this way). This is Read More