search oracle object (stored procedure , function....etc)

Query to search object by name

select * from ALL_OBJECTS
where OBJECT_NAME like '%CM_RT004_MOWE%'

ref:
https://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2005.htm#i1583352

PLSQL CONCAT multiple rows in one string

     
       select CONCAT(REPLACE(listagg( ENTITY_NAME, chr(300)||'(') within group( order by NAME_TYPE_FLG DESC),',',' ')  ,')')AS X
 FROM  CISADM.CI_PER_NAME PN
            WHERE PN.PER_ID ='2252300000'
           AND  PN.NAME_TYPE_FLG IN(   'ALT' , 'PRIM')


output

another solution

SELECT wm_concat('('||ENTITY_NAME||')') 
 FROM
        (   SELECT PER_ID, PN.ENTITY_NAME
            FROM  CISADM.CI_PER_NAME PN
            WHERE PN.PER_ID ='2252300000'
           AND  PN.NAME_TYPE_FLG IN(   'ALT' , 'PRIM')
           ORDER BY PN.NAME_TYPE_FLG DESC
       )
       GROUP BY PER_ID ;

Oracle case when based on numeric check for varchar2 column and select based on range of integers

SELECT
 case
        when SUBSTR( CAST(PGEO.GEO_VAL AS NUMBER) , 1,3) <=199 then  'شرق المدينة'
        when  SUBSTR( CAST(PGEO.GEO_VAL AS NUMBER), 1,3) <=299 THEN   'شمال المدينة'
        when  SUBSTR( CAST(PGEO.GEO_VAL AS NUMBER), 1,3) <=399 then   'غرب المدينة'
        when  SUBSTR( CAST(PGEO.GEO_VAL AS NUMBER), 1,3) <= 499 then  'جنوب المدينة'
        when  SUBSTR( CAST(PGEO.GEO_VAL AS NUMBER), 1,3) <= 599 then 'وسط المدينة'
        else 'other'
     end AS GEO_VAL  ,COUNT(SA.SA_ID) AS ACCT_CNT
 FROM CISADM.CI_SA SA
INNER JOIN  CISADM.CI_PREM PREM ON PREM.PREM_ID = SA.CHAR_PREM_ID
INNER  JOIN CISADM.CI_PREM_GEO PGEO ON PGEO.PREM_ID = PREM.PREM_ID AND PGEO.GEO_TYPE_CD ='9405'--'9401'
 WHERE SA.CIS_DIVISION ='4501'
 AND SA.SA_STATUS_FLG  IN ('20')
 AND SA.SA_TYPE_CD IN ('184015','184012','184013')
 group by case
        when  SUBSTR( CAST(GEO_VAL AS NUMBER), 1,3) <=199 then  'شرق المدينة'
        when  SUBSTR( CAST(GEO_VAL AS NUMBER), 1,3)<= 299 then 'شمال المدينة'
        when  SUBSTR( CAST(GEO_VAL AS NUMBER), 1,3)<= 399 then   'غرب المدينة'
        when  SUBSTR( CAST(GEO_VAL AS NUMBER), 1,3) <= 499 then 'جنوب المدينة'
        when  SUBSTR( CAST(GEO_VAL AS NUMBER), 1,3) <= 599 then 'وسط المدينة'
        else 'other'
        end
        order by geo_val asc;
-------------------------------------------
result

 

close bootstrap modal from iframe page when click on button

 

 Problem: i have button in Modal Page (using iframe to load another page inside)
i need to click on close button in child page to close the modal and refresh data in parent page
1- button code

     <asp:Button CssClass="btn btn-info m-bot15 closeStyle" runat="server" ID="btnReturnToMyTasks" Text="اغلاق الشاشة"  
         Width="150px" CausesValidation="False" meta:resourcekey="btnReturnToMyTasksResource1" OnClientClick='window.parent.$("#modal").modal("hide"); parent.refreshData(true);'/>  

------------------------------------
2- parent page script to refresh data when click on X button or when press on previous button from child page inside iframe

 //////////////////////////////////////////////////////////////////////////////////// refresh data after modal close  

       $(window).bind("load", function () {  
         $(".closeStyle").click(function () {  
           refreshData(true);  
         });  
       });  
       function refreshData(args) {  
         try {  
           if (args) {  
             var masterTable = $find("<%= RadGrid1.ClientID %>").get_masterTableView();  
          masterTable.fireCommand("RebindGrid", "0");  
        }  
         else  
          return false;  
      }  
      catch (e) {  
        //do nothing  
      }  
       }  
       ////////////////////////////////////////////////////////////////////////////////////////////////////////////////

----------------------------------------------------------------------------------
Modal Html (inside master or parent page)

<!-- Modal -->  
     <div class="modal fade" id="modal" tabindex="-1" role="dialog" aria-labelledby="myModalLabel2" aria-hidden="true">  
       <div class="modal-dialog">  
         <div class="modal-content">  
           <div class="modal-header">  
             <!--onclick="alert('now in close'); window.location.reload();"-->  
             <button type="button" class="close closeStyle" data-dismiss="modal" aria-hidden="true" id="btnclose">&times;</button>  
             <h4 class="modal-title" id="myModalLabel2"></h4>  
           </div>  
           <div class="modal-body">  
             <iframe src="#" id="iframepopup" style="background-color: white;"></iframe>  
           </div>  
           <div class="modal-footer">  
             <button type="button" class="btn btn-primary closeStyle" data-dismiss="modal" id="Button1">اغلاق الشاشة</button>  
           </div>  
         </div>  
       </div>  
     </div>  

       
     -----------------------------------------------------------------------------------------
script to create modal height according to screen height

<script type="text/javascript">  
      $(document).ready(function () {  

        $('#modal').on('show.bs.modal', function () {  
          $('.modal-content').css('height', $(window).height() * 0.9);  
          $('.modal-body iframe').css('height', $(window).height() * 0.75);  
        });  
      });  
     </script>  

                    

linked server Sql server OpenQuery by parameter example

DECLARE @SUBNAME NVARCHAR(100) ='محبوبه محمد  الشنقيطي'
DECLARE @SUBNO AS VARCHAR(100)='61319056000'

EXEC ('SELECT *  from CISWEB.CM_PER_NATIONAL_IQAMA_VW
WHERE  subnum = ?',  @SUBNO) AT [SAFALS];

---------------------------------------------------------------------------------------------------

COMPLETE EXAMPLE

ALTER PROCEDURE [dbo].[SAFA_GET_ACC_ID_BY_FILENO]
(
-- Add the parameters for the function here
@FILENO VARCHAR(10) ,
@ACCT_ID BIGint Output
)
AS

BEGIN
DECLARE @tmptable table (ACCT_ID VARCHAR(15))
--DECLARE @ACCT_ID VARCHAR(15) = '0'

insert into @tmptable
EXEC ('SELECT ACCT_ID  from CISADM.CI_ACCT_CHAR
WHERE  CHAR_TYPE_CD=''43036'' AND  ADHOC_CHAR_VAL = ?',  @FILENO) AT [SAFALS];

select @ACCT_ID = ACCT_ID from @tmptable
RETURN 

END

-------------------------------------------------------

CALL PROCEDURE

DECLARE @ACCT_ID bigint

EXEC [dbo].[SAFA_GET_ACC_ID_BY_FILENO]

@FILENO = N'80387',

@ACCT_ID = @ACCT_ID OUTPUT

SELECT @ACCT_ID as N'@ACCT_ID'

--SELECT 'Return Value' = @return_value

GO