Solved

Oralce Dynamic SQL statemnt

Posted on 2014-03-02
17
337 Views
Last Modified: 2014-03-08
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 .
0
Comment
Question by:marrowyung
  • 8
  • 6
  • 3
17 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39899149
I'm afraid I don't understand your question.

Do you want to remove the dynamic SQL?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39899444
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 ?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39899632
could you provide the URL perhaps?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39899697
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 ?
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 100 total points
ID: 39901847
Why do you want to dynamically build triggers?

>>and it said I help to build the dynamic SQL and I can't see how it works, can you explain ?

We really cannot teach you PL/SQL on a Q&A site like this.


You have now posted two completely different pieces of code.

All dynamic sql is, is taking a string that contains a valid SQL statement and executing it.

This allows you to change things at parse time like tables, columns, where clauses etc...

For example, the code below uses dynamic SQL to return the table count for any table you pass in.

It is that simple.

create or replace function myfunc(p_in_table in varchar2) return number
is
	retVal number;
	v_sql varchar2(4000);
begin
	v_sql := 'select count(*) from ' || p_in_table;
	execute immediate v_sql into retVal;
	return retVal;
end;
/
show errors


select myfunc('TAB1') from dual;

Open in new window

0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 400 total points
ID: 39901919
Why build triggers dynamically?
There are many examples of how that is useful, but DBA's hate each and every one of these in my experience.

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.


BTW: In the cocde posted in the question there are several examples of dynamic sql

e.g. lines 11 to 24 (repeated below) which forms a create table statement using the parameter p_table, then executes that via  EXECUTE IMMEDIATE

      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;
If the parameter (p_table) held the string 'marrowyung' then that code snippet would form the following (which I have formatted):

CREATE TABLE bss_marrowyung_global (
      marrowyung_ID NUMBER
      , BSS_ID VARCHAR2(40)
      , marrowyung_name VARCHAR2(4000)
      , marrowyung_desc VARCHAR2(4000)
      , marrowyung_notes VARCHAR2(4000)
      , LAST_UPDATE DATE
      )
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39902061
>>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...
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39902344
"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.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39903405
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.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39908433
'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 !!
0
 
LVL 1

Author Closing Comment

by:marrowyung
ID: 39908436
anything from the web can learn more then how to build dynamic SQL statement ?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39909167
>>thing needs to be take care when it is AFTER UPDATE, very special care !!

Or you just turn on auditing.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39909317
"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 !
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39909355
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?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39911544
"when Oracle provides auditing to track changes? "

What is that you talking about? FGA?
standard audit ?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39913824
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.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39914244
"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!!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now