asked on

Oralce Dynamic SQL statemnt

Dear all,

I read this from the web:

CREATE OR REPLACE PACKAGE BODY APEX_DEVELOPER.create_table
IS

   PROCEDURE create_bss_table (p_table IN VARCHAR2)
   IS
      v_global     VARCHAR2 (4000);
      v_refdata    VARCHAR2 (4000);
      v_sequence   VARCHAR2 (4000);
      v_map        VARCHAR2 (4000);
   BEGIN
      v_global :=
            'create table bss_'
         || p_table
         || '_global ('
         || p_table
         || '_ID NUMBER, BSS_ID VARCHAR2(40), '
         || p_table
         || '_name VARCHAR2(4000), '
         || p_table
         || '_desc VARCHAR2(4000), '
         || p_table
         || '_notes VARCHAR2(4000), LAST_UPDATE DATE)';

      EXECUTE IMMEDIATE v_global;

      v_refdata :=
            'create table bss_'
         || p_table
         || '_refdata ('
         || p_table
         || '_ID NUMBER, '
         || p_table
         || '_name VARCHAR2(4000))';

      EXECUTE IMMEDIATE v_refdata;

      --v_sequence := 'CREATE or replace SEQUENCE SEQ_'|| p_table ||' START WITH 1  MAXVALUE 99999999';
      -- EXECUTE IMMEDIATE v_sequence;
      v_map :=
            'create table bss_'
         || p_table
         || '_map ('
         || p_table
         || '_ID NUMBER, LAST_UPDATE DATE)';

      EXECUTE IMMEDIATE v_map;
   END create_bss_table;
   
   
   PROCEDURE update_map (v_objet IN VARCHAR2, v_system IN VARCHAR2, v_relation IN VARCHAR2)
   IS
   v_req_table VARCHAR2(4000);
   v_req_alter VARCHAR2(4000);
   v_req_vue VARCHAR2(4000);
   v_cast_obj varchar2(10);
   v_cast_sys varchar2(15);
   v_obj bss_objects.object_name%type;
   v_objsn bss_objects.object_sn%type;
   v_sys bss_systems.system_name%type;
   v_rel bss_relationship.relationship_name%type;
   BEGIN
   select UPPER(REPLACE(object_name,' ','')) into v_obj from bss_objects where object_id = v_objet;
   select UPPER(REPLACE(object_sn,' ','')) into v_objsn from bss_objects where object_id = v_objet;
   select UPPER(REPLACE(system_name,' ','')) into v_sys from bss_systems where system_id = v_system;
   select UPPER(relationship_name) into v_rel from bss_relationship where relationship_id = v_relation;
   
   v_cast_obj := substr(v_objsn,1,10);
       v_cast_sys := substr(v_sys,1,10);
   IF(v_rel = 'SLAVE') THEN
       
       v_req_table := 'create table BSS_'||v_cast_obj||'_'||v_cast_sys||'_TRANSACTION ('||v_objsn||'_ID NUMBER, '||v_cast_sys||'_IN VARCHAR2(4000) , '||v_cast_sys||'_OUT VARCHAR2(4000) , LAST_UPDATE DATE, ERROR CHAR(2), ERROR_DESC VARCHAR2(4000), '||v_cast_sys||'_schema VARCHAR2 (40))';
       INSERT INTO BSS_LOG (LOG_DATE, LOG_PROCESS, LOG_RETURN, LOG_DESCRIPTION) VALUES(SYSDATE,'',0,v_req_table);
       COMMIT;
       EXECUTE IMMEDIATE v_req_table;
   --creation table de transaction
   --creation vue transaction
   END IF;
   IF(v_rel <> 'NOT AFFECTED') THEN
   v_req_alter := 'alter table bss_'|| v_obj ||'_map add '||v_cast_sys||'_ID VARCHAR2 (40)';
   EXECUTE IMMEDIATE v_req_alter;
   END IF;
   END update_map;
   
   PROCEDURE table_del (v_ob IN NUMBER)
   IS
   
   v_obj bss_objects.object_name%TYPE;
   v_req varchar2(4000);
   BEGIN
   select object_name into v_obj from bss_objects where object_id = v_ob; 
   v_req := 'drop table bss_'||v_obj||'_refdata' ;
   execute immediate v_req;
   v_req := 'drop table bss_'||v_obj||'_map ';
   EXECUTE IMMEDIATE v_req;
   v_req := 'drop table bss_'||v_obj||'_global ';
   EXECUTE IMMEDIATE v_req;
   END table_del;
   
   
   
   
   PROCEDURE del_col_table (v_objet IN VARCHAR2, v_system IN VARCHAR2, v_relation IN VARCHAR2)
   IS
   v_req_table VARCHAR2(4000);
   v_req_alter VARCHAR2(4000);
   v_req_vue VARCHAR2(4000);
   v_cast_obj varchar2(5);
   v_cast_sys varchar2(5);
   v_obj bss_objects.object_name%type;
   v_sys bss_systems.system_name%type;
   v_rel bss_relationship.relationship_name%type;
   BEGIN
   select object_name into v_obj from bss_objects where object_id = v_objet;
   select system_name into v_sys from bss_systems where system_id = v_system;
   select relationship_name into v_rel from bss_relationship where relationship_id = v_relation;
   
   v_cast_obj := substr(v_obj,1,4);
       v_cast_sys := substr(v_sys,1,3);
   IF(v_rel = 'SLAVE') THEN
       
       v_req_table := 'drop table bss_'||v_cast_obj||'_'||v_cast_sys||'_transaction ';
       EXECUTE IMMEDIATE v_req_table;
   --creation table de transaction
   --creation vue transaction
   END IF;
   IF(v_rel <> 'NOT AFFECTED') THEN
   v_req_alter := 'alter table bss_'|| v_obj ||'_map drop column '||v_cast_sys||'_ID ';
   EXECUTE IMMEDIATE v_req_alter;
   END IF;
   END del_col_table;
   
   PROCEDURE create_BSS_INCIDENTS2_table
   IS
   BEGIN
   
EXECUTE IMMEDIATE 'TRUNCATE TABLE BSS_INCIDENTS2';
   
INSERT INTO BSS_INCIDENTS2
   SELECT   p."PNumber" AS PNumber,
            p."PDescript" AS Description,
            p."PStatus" AS Status,
            p."PLogdBy" AS LoggedBy,
            p."PAssignee" AS Assignee,
            P."PTracker" AS Tracker,
            p."PContact" AS Contact,
            p."PContCmpy" AS Company,
            p."PTPRef" AS Cust_Ref,
            p."PCurrSLA" AS SLA,
            TO_DATE ('01-JAN-1970') + p."PEntered" / (24 * 60 * 60)
               AS Entered,
            TO_DATE ('01-JAN-1970') + p."POccurred" / (24 * 60 * 60)
               AS Occurred,
            TO_DATE ('01-JAN-1970') + p."PModified" / (24 * 60 * 60)
               AS Modified,
            TO_DATE ('01-JAN-1970') + p."PSolvAt" / (24 * 60 * 60)
               AS SolvedAt,
            TO_DATE ('01-JAN-1970') + p."PClosdAt" / (24 * 60 * 60)
               AS ClosedAt,
            p."PModiBy" AS ModifiedBy,
            p."PSolvBy" AS SolvedBy,
            e.Value1 AS ReportedBy,
            e.Value2 AS ReportedByBE,
            p."PRespTime" AS RespTime,
            p."PFixedTime" AS FixedTime,
            p."PPriority" AS Priority,
            p."PSeverity" AS Severity,
            p."PImpact" AS Impact,
            a."DictKeyDes" AS Whocause,
            b."DictKeyDes" AS Rootcause,
            c."DictKeyDes" AS Resolution,
            d."DictKeyDes" AS Service,
            d."DictKey1" AS SKey1,
            d."DictKey2" AS SKey2,
            d."DictKey3" AS SKey3
     FROM   SUPPORT_TPROBLEM@LINK_MARVAL_ESS.GATEWAYCOMMS.COM p,
            PURSUIT_TDICT9@LINK_MARVAL_ESS.GATEWAYCOMMS.COM a,
            PURSUIT_TDICT10@LINK_MARVAL_ESS.GATEWAYCOMMS.COM b,
            PURSUIT_TDICT11@LINK_MARVAL_ESS.GATEWAYCOMMS.COM c,
            PURSUIT_TDICT1@LINK_MARVAL_ESS.GATEWAYCOMMS.COM d,
            (  SELECT   "ProbNo",
                        MIN ("Value1") || MAX ("Value1") AS VALUE1,
                        MIN ("Value2") || MAX ("Value2") AS VALUE2
                 FROM   (SELECT   *
                           FROM   SUPPORT_PROBATTR@LINK_MARVAL_ESS.GATEWAYCOMMS.COM
                          WHERE   "ProbNo" IN
                                        (SELECT   "ProbNo"
                                           FROM   (  SELECT   COUNT ( * )
                                                                 AS conteur,
                                                              "ProbNo"
                                                       FROM   SUPPORT_PROBATTR@LINK_MARVAL_ESS.GATEWAYCOMMS.COM e
                                                   GROUP BY   "ProbNo")
                                          WHERE   conteur >= 2))
             GROUP BY   "ProbNo"
             UNION ALL
             SELECT   "ProbNo", "Value1" AS VALUE1, "Value2" AS VALUE2
               FROM   SUPPORT_PROBATTR@LINK_MARVAL_ESS.GATEWAYCOMMS.COM
              WHERE   "ProbNo" IN
                            (SELECT   "ProbNo"
                               FROM   (  SELECT   COUNT ( * ) AS conteur,
                                                  "ProbNo"
                                           FROM   SUPPORT_PROBATTR@LINK_MARVAL_ESS.GATEWAYCOMMS.COM e
                                       GROUP BY   "ProbNo")
                              WHERE   conteur = 1)) e
    WHERE      P."PCallType" = 'INC'
            AND p."PNumber" = e."ProbNo"(+)
            AND p."PDict9" = a."DictSNo"(+)
            AND p."PDict10" = b."DictSNo"(+)
            AND p."PDict11" = c."DictSNo"(+)
            AND p."PDict1" = d."DictSNo"(+);
   
   COMMIT;
   
   END create_BSS_INCIDENTS2_table;
END create_table;
/

Open in new window

but I don't see why Dynamic SQL statement here that allow me to build dynamic SQL statement, please suggest how to do this .

PortletPaul

I'm afraid I don't understand your question.

Do you want to remove the dynamic SQL?

marrowyung

ASKER

I find this script from the web and it said I help to build the dynamic SQL and I can't see how it works, can you explain ?

PortletPaul

could you provide the URL perhaps?

marrowyung

ASKER

I lost that link but one thing I am doing is dynamicallly create SQL statement to generate the Trigger command, the one of MySQL I built for BEFORE DELETE trigger:

DELIMITER $$

CREATE FUNCTION `Function_Create_BEFORE_DELETE_TRIGGER` (Trigger_databasename VARCHAR(40),  trigger_tablename VARCHAR(40))  	
RETURNS TEXT
NOT DETERMINISTIC      
READS SQL DATA
BEGIN  
DECLARE a, b, fieldnameCursor_finished  INT DEFAULT 10;
DECLARE temptable VARCHAR(50);	
DECLARE today TIMESTAMP DEFAULT CURRENT_DATE;
DECLARE v1, v2, v3 TINYINT ;
DECLARE current_fieldname  VARCHAR(50) DEFAULT ""; 
/* DECLARE fullexcutecmd VARCHAR(65535);
DECLARE fullexcutecmd1 VARCHAR(5000);
DECLARE fullexcutecmd2 VARCHAR(5000);
DECLARE fullexcutecmd3 VARCHAR(60000);*/
DECLARE fullexcutecmd TEXT;
DECLARE fullexcutecmd1 TEXT;
DECLARE fullexcutecmd2 TEXT;
DECLARE fullexcutecmd3 TEXT;

/*Declare and populate the cursor with a SELECT statement */  	
	  
DECLARE fieldname CURSOR FOR 	
SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = Trigger_databasename AND TABLE_NAME = trigger_tablename ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET fieldnameCursor_finished = 1;     


 
 /* DROP TRIGGER IF EXISTS QRTZ_TRIGGERS_20130915_BEFORE_DELETE_TRIGGER; */
  

  
SET @fullexcutecmd1= CONCAT( '  DROP TRIGGER IF EXISTS ',trigger_tablename,'_BEFORE_DELETE_TRIGGER; CREATE TRIGGER ',trigger_tablename,'_BEFORE_DELETE_TRIGGER BEFORE DELETE ON ',Trigger_databasename,'.',trigger_tablename,' FOR EACH ROW
BEGIN 
INSERT INTO Audit_info.',Trigger_databasename,'_DBAudit_',trigger_tablename,' SET 
TriggerAction=','"','BEFORE','",','
ActionDone=','"','DELETE','",','
ActionDate=now(),
ActionBy=USER()');   

OPEN fieldname ;	

set @fullexcutecmd2=' ';
set @fullexcutecmd3=' ';

	get_fieldlist: LOOP	
 
   FETCH fieldname INTO current_fieldname;	
         IF fieldnameCursor_finished = 1 THEN 	
          LEAVE get_fieldlist;	
          END IF;	

   /* for debug purpose only: 
select @fullexcutecmd2; */
 
SET @fullexcutecmd2 = CONCAT(' ',current_fieldname,'=OLD.',current_fieldname);
 set @fullexcutecmd3= CONCAT(@fullexcutecmd3,',',@fullexcutecmd2); 
   
      END LOOP  get_fieldlist;	
close fieldname;                             
  
  set @fullexcutecmd3= CONCAT(@fullexcutecmd1,@fullexcutecmd3,'; END ;'); 

 return @fullexcutecmd3;

  
END $$
DELIMITER ;

Open in new window

how to do the same thing in Oracle ?

SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

slightwv (䄆 Netminder)

>>but DBA's hate each and every one of these in my experience.

Agreed for the same example you posted: ... wishes to develop a "configurable application" ...

The amount of code it would require to catch ALL conceivable and possible schema modifications to properly modify the triggers dynamically wouldn't be cost effective.

Trying to keep it up to date with every new release wouldn't be worth the developer time over putting change control/peer review in place.

I would bet a beer that any code you could post that did this, I could break.

DO NOT let developers manage their own schemas. You need a change control process that will catch changes that break things. At least this will catch most things...

marrowyung

ASKER

"Let's say one wishes to develop a "configurable application" where it is possible to amend data structures through a simple UI (and without knowledge of any SQL). That UI might also permit the ability to audit certain fields or audit for inserts and deletes.

That UI then calls on functions/procedures to produce the required SQL to alter the tables and related triggers to implement the changes.
"

excellent !! one of the example ! what I am doing (already done dynamic SQL in MySQL and it is working fine!) is to generate trigger for aLL application database's table.

so I will use cursor to loop out all field and start building trigger one by one for all field, that's why ? cursor + dynamically SQL, then let DBA or developer who change the table to run that trigger statement without checking everything in the script as the script should be just fine! the MySQL version I built is like that!

MySQL can't do execute the trigger statement dynamically, on the fire !! can Oracle?

"If the parameter (p_table) held the string 'marrowyung' then that code snippet would form the following (which I have formatted):"

I understand now and that's why p_table is the parameter of that SP

PROCEDURE create_bss_table (p_table IN VARCHAR2)

I do it in MySQL SP too!!! all the time ! this make me very easy to understand !

slightwv,

"Trying to keep it up to date with every new release wouldn't be worth the developer time over putting change control/peer review in place."

the SP will DETECT it automatically whatever developer change ! that SP will handle it and taht'as why I built for that MySQL and result will generate AUTOMATICALLY !

"EXECUTE IMMEDIATE v_refdata;"

I like that !! MYSQL and MSSQL has another keyword.

"DO NOT let developers manage their own schemas. You need a change control process that will catch changes that break things. "

yes, but it will depends on company. MY Current is stupid and if I control everything, they don't allow me to sleep .

So my SP DETECT it and let me run it LATER ON.

slightwv (䄆 Netminder)

You can create a system level trigger that looks at every DDL statement and can then take whatever action you code.

The system trigger reference:
http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/triggers.htm#LNPLS99887

There are many examples out there on DDL triggers if you look around.

marrowyung

ASKER

'You can create a system level trigger that looks at every DDL statement and can then take whatever action you code."

Yes but this is not what I want to do ! this is about schema level change notification, MySQL do not have it but MS SQL, ahah. that's why I don't like MySQL, it can't even call another SP inside the SP we are calling !! it created a lot of trouble for me.

so must call function from SP. but function cant do dynamic SQL, can't execute it in real time when SP are running !!

"There are many examples out there on DDL triggers if you look around. "

But this one we are going to do is about data level audit, even not action level audit. e.g.

1) What data cell has been changed.
2) What is the VALUE changed.
3) who change it.
4) When change it.
5) BEFORE DELETE, AFTER INSERT or AFTER UPDATE.

thing needs to be take care when it is AFTER UPDATE, very special care !!

marrowyung

ASKER

anything from the web can learn more then how to build dynamic SQL statement ?

slightwv (䄆 Netminder)

>>thing needs to be take care when it is AFTER UPDATE, very special care !!

Or you just turn on auditing.

marrowyung

ASKER

"Or you just turn on auditing. "

the target table will keep writing data even there are no update at all! it just found result set and update the target table automatically !

slightwv (䄆 Netminder)

I believe you can control what you audit down to the level of updates only.

Why re-invent the wheel and go through a LOT of trigger/DDL/dynamic code when Oracle provides auditing to track changes?

marrowyung

ASKER

"when Oracle provides auditing to track changes? "

What is that you talking about? FGA?
standard audit ?

slightwv (䄆 Netminder)

Maybe either. I just remember from reading that you can get about as granular as you want with auditing.

Since you now asked, I think I remember your previous questions where we beat auditing to death. I guess it never worked for what you needed.

I just don't remember why.

marrowyung

ASKER

"Maybe either. I just remember from reading that you can get about as granular as you want with auditing. "

but data auditing level. you know, even the oracle teacher say the trigger is the only way to work for my case!!