MyBatis parameter Generator for Oracle Object (empty PKG_NAME will write plain OBJ_NAME.)

all_sp_in_pkg.sql

--------------------------------------------------------------------------------------
-- GENEATES ALL STORED PROCEDURES ON DESIRED PACKAGE IN YOUR SCHEMA.
--------------------------------------------------------------------------------------
set pagesize 0; -- PRETTY OUTPUT FOR SQL*PLUS OR OTHER TOOLS OUTPUT.
WITH SCRT AS (
SELECT OBJECT_NAME AS PKG_NAME, PROCEDURE_NAME AS SP_NAME
      , '{ CALL ' AS Q_PRE
      , '}' AS Q_SUR
      , 'anyMapType' AS P_MAP
  FROM USER_PROCEDURES
  WHERE OBJECT_NAME = 'PKG_NAME'
  AND   PROCEDURE_NAME IS NOT NULL
)
SELECT SCRIPT FROM (
    SELECT POSITION AS ORD_SC,B.SP_NAME,
        DECODE(A.POSITION, 1, 
            Q_PRE || PKG_NAME || '.' || SP_NAME || ' (
       '
            , '     , '
        )
        || '#{' ||
        lower(SUBSTR(A.ARG_NAME, 1,1))||SUBSTR(replace(INITCAP(lower(A.ARG_NAME)), '_',''), 2)
        || ', mode=' || DECODE(A.IN_OUT, 'IN/OUT', 'INOUT', A.IN_OUT) ||
        (CASE WHEN A.DATA_TYPE LIKE 'REF_CURSOR' THEN ', jdbcType=CURSOR, javaType=ResultSet' ELSE ', jdbcType=VARCHAR, javaType=string' END) ||
        (CASE WHEN A.DATA_TYPE LIKE 'REF_CURSOR' THEN ', resultMap=' || P_MAP ELSE '' END) ||
        '}' ||
        DECODE(A.POSITION, (SELECT MAX(POSITION) FROM SYS.ALL_ARGUMENTS WHERE PACKAGE_NAME = A.PACKAGE_NAME AND OBJECT_NAME = A.OBJECT_NAME)
        , '
  )
'       || Q_SUR
        , ''
        ) AS SCRIPT
    FROM (
            SELECT A.*, -- REFINE ARGUMENT NAME BELOW OR USE ARGUMENT_NAME IF YOU NOT WANT.
            DECODE(ARGUMENT_NAME, 'CHANGE_COL', 'REPLACE_COL', SUBSTR(ARGUMENT_NAME, 3)) AS ARG_NAME FROM SYS.ALL_ARGUMENTS A
         ) A, SCRT B
    WHERE A.PACKAGE_NAME = B.PKG_NAME
    AND   A.OBJECT_NAME = B.SP_NAME
) ORDER BY SP_NAME, ORD_SC

all_sp_in_pkg_with_owner.sql

--------------------------------------------------------------------------------------
-- GENEATES ALL STORED PROCEDURES ON DESIRED PACKAGE IN YOUR SCHEMA.
--------------------------------------------------------------------------------------
set pagesize 0; -- PRETTY OUTPUT FOR SQL*PLUS OR OTHER TOOLS OUTPUT.
WITH SCRT AS (
SELECT OWNER AS OWN_NAME, OBJECT_NAME AS PKG_NAME, PROCEDURE_NAME AS SP_NAME
      , '{ CALL ' AS Q_PRE
      , '}' AS Q_SUR
      , 'anyMapType' AS P_MAP
  FROM ALL_PROCEDURES
  WHERE OWNER = 'OWNER'
  AND   OBJECT_NAME = 'PKG_NAME'
  AND   PROCEDURE_NAME IS NOT NULL
)
SELECT SCRIPT FROM (
    SELECT POSITION AS ORD_SC,B.SP_NAME,
        DECODE(A.POSITION, 1, 
            Q_PRE || OWN_NAME || '.' || PKG_NAME || '.' || SP_NAME || ' (
       '
            , '     , '
        )
        || '#{' ||
        lower(SUBSTR(A.ARG_NAME, 1,1))||SUBSTR(replace(INITCAP(lower(A.ARG_NAME)), '_',''), 2)
        || ', mode=' || DECODE(A.IN_OUT, 'IN/OUT', 'INOUT', A.IN_OUT) ||
        (CASE WHEN A.DATA_TYPE LIKE 'REF_CURSOR' THEN ', jdbcType=CURSOR, javaType=ResultSet' ELSE ', jdbcType=VARCHAR, javaType=string' END) ||
        (CASE WHEN A.DATA_TYPE LIKE 'REF_CURSOR' THEN ', resultMap=' || P_MAP ELSE '' END) ||
        '}' ||
        DECODE(A.POSITION, (SELECT MAX(POSITION) FROM SYS.ALL_ARGUMENTS WHERE PACKAGE_NAME = A.PACKAGE_NAME AND OBJECT_NAME = A.OBJECT_NAME)
        , '
  )
'       || Q_SUR
        , ''
        ) AS SCRIPT
    FROM (
            SELECT A.*, -- REFINE ARGUMENT NAME BELOW OR USE ARGUMENT_NAME IF YOU NOT WANT.
            DECODE(ARGUMENT_NAME, 'CHANGE_COL', 'REPLACE_COL', SUBSTR(ARGUMENT_NAME, 3)) AS ARG_NAME FROM SYS.ALL_ARGUMENTS A
         ) A, SCRT B
    WHERE A.PACKAGE_NAME = B.PKG_NAME
    AND   A.OBJECT_NAME = B.SP_NAME
) ORDER BY SP_NAME, ORD_SC

all_sp_in_pkg_with_owner_ide_friendly.sql

WITH SCRT AS (
--------------------------------------------------------------------------------------
-- GENEATES ALL STORED PROCEDURES ON DESIRED PACKAGE IN YOUR SCHEMA.
--------------------------------------------------------------------------------------
SELECT OWNER AS OWN_NAME, OBJECT_NAME AS PKG_NAME, PROCEDURE_NAME AS SP_NAME
      , '{ CALL ' AS Q_PRE
      , '}' AS Q_SUR
      , :A_MAP_CLASS AS P_MAP
  FROM ALL_PROCEDURES
  WHERE OWNER = 'FOFADM'
  AND   OBJECT_NAME = UPPER('PKG_' || :A_PROG_ID)
  AND   PROCEDURE_NAME IS NOT NULL
)
SELECT SCRIPT FROM (
    SELECT POSITION AS ORD_SC,B.SP_NAME,
        DECODE(A.POSITION, 1, 
            Q_PRE || OWN_NAME || '.' || PKG_NAME || '.' || SP_NAME || ' (
       '
            , '     , '
        )
        || '#{' ||
        lower(SUBSTR(REPLACE(A.ARG_NAME, 'SEC_', ''), 1,1))||SUBSTR(replace(INITCAP(lower(REPLACE(A.ARG_NAME, 'SEC_', ''))), '_',''), 2)
        || ', mode=' || DECODE(A.IN_OUT, 'IN/OUT', 'INOUT', A.IN_OUT) ||
        (CASE WHEN A.DATA_TYPE LIKE 'REF_CURSOR' THEN ', jdbcType=CURSOR, javaType=ResultSet'
            WHEN A.DATA_TYPE LIKE 'DATE' THEN ', jdbcType=DATE, javaType=date'
            WHEN A.DATA_TYPE LIKE 'CLOB' THEN ', jdbcType=BLOB, javaType=string'
            WHEN A.DATA_TYPE LIKE 'NUMBER' AND NVL(A.DATA_PRECISION,0) <> 0 THEN ', jdbcType=NUMERIC, javaType=float'
            WHEN A.DATA_TYPE IN ('NUMBER', 'INT', 'INTEGER') THEN ', jdbcType=NUMERIC, javaType=int'
            ELSE ', jdbcType=VARCHAR, javaType=string' END) ||
        (CASE WHEN A.DATA_TYPE LIKE 'REF_CURSOR' OR A.ARG_NAME IN ('RESULT','MSG') THEN ', resultMap=' || P_MAP ELSE '' END) ||
        '}' ||
        DECODE(A.POSITION, (SELECT MAX(POSITION) FROM SYS.ALL_ARGUMENTS WHERE PACKAGE_NAME = A.PACKAGE_NAME AND OBJECT_NAME = A.OBJECT_NAME)
        , '
  )
'       || Q_SUR
        , ''
        ) AS SCRIPT
    FROM (
            SELECT A.*, -- REFINE ARGUMENT NAME BELOW OR USE ARGUMENT_NAME IF YOU NOT WANT.
            DECODE(ARGUMENT_NAME, 'O_RESULTLIST', 'RESULT_LIST', SUBSTR(ARGUMENT_NAME, 3)) AS ARG_NAME FROM SYS.ALL_ARGUMENTS A
         ) A, SCRT B
    WHERE A.PACKAGE_NAME = B.PKG_NAME
    AND   A.OBJECT_NAME = B.SP_NAME
) ORDER BY SP_NAME, ORD_SC

pkg_or_sp.sql

--------------------------------------------------------------------------------------
-- GENEATES A STORED PROCEDURE ON DESIRED PACKAGE AND PROCEDURE NAME IN YOUR SCHEMA.
--------------------------------------------------------------------------------------
WITH SCRT AS (
SELECT
      'PKG_NAME' AS PKG_NAME  -- OR SET '' FOR STORED PROCEDURE OUTSIDE PACKAGE.
    , 'SP_NAME' AS OBJ_NAME
    , '{ CALL ' AS Q_PRE
    , '}' AS Q_SUR
    , 'anyMapType' AS P_MAP
FROM DUAL
)
SELECT SCRIPT FROM (
    SELECT -1 AS ORD_SC, Q_PRE || DECODE(NVL(PKG_NAME, ''), '', '', PKG_NAME || '.') || OBJ_NAME || ' (' AS SCRIPT FROM DUAL, SCRT
    UNION ALL
    SELECT POSITION,
        DECODE(A.POSITION, 1, '      ', '    , ') ||
        '#{' ||
        lower(SUBSTR(A.ARG_NAME, 1,1))||SUBSTR(replace(INITCAP(lower(A.ARG_NAME)), '_',''), 2)
        || ', mode=' || DECODE(A.IN_OUT, 'IN/OUT', 'INOUT', A.IN_OUT) ||
        (CASE WHEN A.DATA_TYPE LIKE 'REF_CURSOR' THEN ', jdbcType=CURSOR, javaType=ResultSet' ELSE ', jdbcType=VARCHAR, javaType=string' END) ||
        (CASE WHEN A.DATA_TYPE LIKE 'REF_CURSOR' THEN ', resultMap=' || P_MAP ELSE '' END) ||
        '}'
    FROM (
            SELECT A.*, -- REFINE ARGUMENT NAME BELOW OR USE ARGUMENT_NAME IF YOU NOT WANT.
            DECODE(ARGUMENT_NAME, 'CHANGE_COL', 'REPLACE_COL', SUBSTR(ARGUMENT_NAME, 3)) AS ARG_NAME FROM SYS.ALL_ARGUMENTS A
         ) A, SCRT B
    WHERE A.PACKAGE_NAME = B.PKG_NAME
    AND   A.OBJECT_NAME = UPPER(B.OBJ_NAME) -- PROCEDURE NAME SHOULD BE UPPERCASE.
    UNION ALL
    SELECT 1000, '  )
' || Q_SUR FROM SCRT
) ORDER BY ORD_SC