title: Convert SQL Server stored procedure to Oracle procedure to query from tables
body: <p>I'm trying to move from SQL Server to an Oracle database. I have to move my stored procedure from SQL Server to Oracle that do a query from multiple table with an <code>INNER JOIN</code>. I am trying to clarify few things here.</p>
  
  <p>SQL Server stored procedure:</p>
  
  <pre><code>[dbo].[QueryAll] 
      @score1_min int = 0,
      @score1_max int = 999,  
      @type1 varchar (1) = '%',
  AS
  BEGIN
      SET NOCOUNT ON;
  
      SELECT * 
      FROM FUNIJ1 uni
      INNER JOIN CAPI99 api99 on api99.application_id = uni.application_id
      INNER JOIN CAPI41 api41 on api41.application_id = uni.application_id
      INNER JOIN CAPI10 api10 on api10.application_id = uni.application_id
      WHERE 
          api10.score1 BETWEEN @score1_min AND @score1_max 
          AND uni.type1 LIKE @type1
  END
  </code></pre>
  
  <p>And my Oracle procedure</p>
  
  <pre><code>create or replace PROCEDURE QUERYALL 
  (
    SCORE1_MIN IN NUMBER DEFAULT 0 
  , SCORE1_MAX IN NUMBER DEFAULT 999 
  , TYPE IN VARCHAR2 
  ) 
  AS 
  BEGIN
      SELECT *
      FROM FUNIJ1 uni
      INNER JOIN CAPI99 on CAPI99.APPLICATION_ID = uni.APPLICATION_ID
      INNER JOIN CAPI41 on CAPI41.APPLICATION_ID = uni.APPLICATION_ID
      INNER JOIN CAPI10 on CAPI10.APPLICATION_ID = uni.APPLICATION_ID
      WHERE 
          CAPI10.score1 BETWEEN score1_min AND score1_max 
          AND uni.type LIKE type
  END REPOQUERYALL ;
  </code></pre>
  
  <ol>
  <li><p>I used % as default query parameter to return all values in SQL Server
  in case of no input from user. I am not sure what I have to use in Oracle as default value to return all.</p></li>
  <li><p>List item Oracle is using new term INTO following to <code>SELECT *</code>. I am not sure whether I need to use INTO or cursor in this case. I don't know WHICH will be appropriate here and HOW to use it.</p></li>
  <li><p>I'd appreciate if anybody can transfer this SQL Server to an Oracle procedure. I am not sure that my Oracle is 100% correct.</p></li>
  </ol>