This discussion is archived
3 Replies Latest reply: Sep 29, 2013 7:32 AM by Solomon Yakobson RSS

Remove character from my DDL query

Erhan_toronto Newbie
Currently Being Moderated

I am using below query to get DDL of my function without getting the user name, if you see my query I am getting / slash also and I dont want to see the / too. How can I get rid off this /

 

FUNCTION_NAME= "EMP"

USER_NAME="USER1"

 

SELECT  REGEXP_REPLACE ( REPLACE ( dbms_metadata.get_ddl ('FUNCTION', '" + function_name+ @"'), '""" + User_name + @""".'),'^\s+', NULL, 1, 0, 'm') FROM dual

 

result:

 

CREATE OR REPLACE FUNCTION "EMP"

(str_in IN VARCHAR2) RETURN BOOLEANAS

-- validating ###-##-#### format

BEGIN

  IF TRANSLATE(str_in, '0123456789A','AAAAAAAAAAB') = 'AAA-AA-AAAA' THEN

    RETURN TRUE;

  END IF;

  RETURN FALSE;

END ssn_candy;

/

 

 

 

Expected result

 

CREATE OR REPLACE FUNCTION "EMP"

(str_in IN VARCHAR2) RETURN BOOLEANAS

-- validating ###-##-#### format

BEGIN

  IF TRANSLATE(str_in, '0123456789A','AAAAAAAAAAB') = 'AAA-AA-AAAA' THEN

    RETURN TRUE;

  END IF;

  RETURN FALSE;

END ssn_candy;

  • 1. Re: Remove character from my DDL query
    rp0428 Guru
    Currently Being Moderated
    I am using below query to get DDL of my function without getting the user name, if you see my query I am getting / slash also and I dont want to see the / too. How can I get rid off this /

    You don't seem to be learning anything from your multiple posts about using the metadata package. Your own previous post already shows how to do that.

    exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',FALSE);

    If you eliminate the terminator the DDL won't run properly.

  • 2. Re: Remove character from my DDL query
    Erhan_toronto Newbie
    Currently Being Moderated

    Thanks for the reply, yes you are right DDL wont run properly. but if I use this /  it will store the / inside my DDL. I dont want to see this inside my DDL. how can I get rid off this?

  • 3. Re: Remove character from my DDL query
    Solomon Yakobson Guru
    Currently Being Moderated

    Which part of

     

    exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',FALSE);

     

    you didn't understand?

     

    SQL> select dbms_metadata.get_ddl('FUNCTION','F1') from dual;

    DBMS_METADATA.GET_DDL('FUNCTION','F1')
    --------------------------------------------------------------------------------

      CREATE OR REPLACE EDITIONABLE FUNCTION "SCOTT"."F1"
        return number
        is
        begin
            return 1;
    end;
    /


    SQL> exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',FALSE);

    PL/SQL procedure successfully completed.

    SQL> select dbms_metadata.get_ddl('FUNCTION','F1') from dual;

    DBMS_METADATA.GET_DDL('FUNCTION','F1')
    --------------------------------------------------------------------------------

      CREATE OR REPLACE EDITIONABLE FUNCTION "SCOTT"."F1"
        return number
        is
        begin
            return 1;
    end;


    SQL>

     

    SY.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points