Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2014
    Posts
    294

    Unanswered: Problem with CREATE_WRAPPED function

    Hi,

    I have created few procedures & i can obfuscate my procedure body using this CREATE_WRAPPED function.
    problem is my procedure is working fine when I ran that alone without obfuscation.But,
    During Obfuscation as I need to replace single quotes with double quotes.I am done with the replacing.But I am unable to get the output as it is throwing some error may be with the quotes, I started learning few days back itself. I am posting my code part..
    can some one kindly correct me , where should I change my single quotes. i tried from my end a lot , hence I am doing this.


    CALL DBMS_DDL.CREATE_WRAPPED('CREATE OR REPLACE PROCEDURE SP_M_LOGS (IN V_JOB_ID INTEGER,IN LOG_PROCESS VARCHAR(4000))
    DYNAMIC RESULT SETS 1
    P1: BEGIN
    DECLARE VAR1 VARCHAR(4000);
    DECLARE VAR2 INTEGER;
    DECLARE VAR3 VARCHAR(50);
    DECLARE FINAL_LOG VARCHAR(5000);
    ---DECLARE LOG_PROCESS VARCHAR(4000);
    -- Declare cursor

    SET VAR2 = (SELECT MAX(LINE_NUM) FROM FR_JOB_LOGS WHERE JOB_ID = V_JOB_ID);

    IF (VAR2 IS NULL)
    THEN

    SET VAR2=1;
    ELSE
    SET VAR2=VAR2+1;

    END IF;

    SET VAR3= (SELECT CURRENT TIMESTAMP AS CURRENT_SERVER_TIME FROM sysibm.sysdummy1);
    SET FINAL_LOG= LOG_PROCESS CONCAT '' at '' CONCAT VAR3;

    SET VAR1=''INSERT INTO FR_JOB_LOGS (JOB_ID,LINE_NUM,LOG) values (''||V_JOB_ID||'',''||VAR2||'',''||FINAL_LOG||'')' ';
    execute immediate VAR1;

    -- Cursor left open for client application

    END P1 ')


    Error:
    An unexpected token "STARTED" was found following "s (3584,16,DISCOVERY". Expected tokens may include: "<space>".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.17.30




    for this step in the code:
    SET FINAL_LOG= LOG_PROCESS CONCAT '' at '' CONCAT VAR3;

    log_process will get values from other procedure & it is concatanating with the timestamp variable.

  2. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    you can get your routine text right from the db2 system catalog.
    Firstly, you create your routine as usual.
    Secondly, you wrap it like in the example below.

    Code:
    create or replace function test_wrapped_f()
    specific TEST_WRAPPED
    returns timestamp
    return current timestamp;
    
    select text from syscat.routines where routineschema=user and specificname='TEST_WRAPPED' with ur;
    
    CALL DBMS_DDL.CREATE_WRAPPED((select text from syscat.routines where routineschema=user and specificname='TEST_WRAPPED' with ur));
    
    select text from syscat.routines where routineschema=user and specificname='TEST_WRAPPED' with ur;
    To make it work you:
    - should assign to your routine some meaningful unique specific name to select the routine text easier
    - have to use CREATE OR REPLACE clause and not just CREATE to avoid a routine creation error with a duplicate name
    Regards,
    Mark.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •