<commit_before># encoding: utf-8

require "spec_helper"

describe "Parameter type mapping /" do

  shared_examples "Function with string parameters" do |datatype|
    before(:all) do
      plsql.connect! CONNECTION_PARAMS
      plsql.execute <<-SQL
        CREATE OR REPLACE FUNCTION test_uppercase
          ( p_string #{datatype} )
          RETURN #{datatype}
        IS
        BEGIN
          RETURN UPPER(p_string);
        END test_uppercase;
      SQL
    end

    after(:all) do
      plsql.execute "DROP FUNCTION test_uppercase"
      plsql.logoff
    end

    it "should find existing procedure" do
      expect(PLSQL::Procedure.find(plsql, :test_uppercase)).not_to be_nil
    end

    it "should not find nonexisting procedure" do
      expect(PLSQL::Procedure.find(plsql, :qwerty123456)).to be_nil
    end

    it "should execute function and return correct value" do
      expect(plsql.test_uppercase("xxx")).to eq("XXX")
    end

    it "should execute function with named parameters and return correct value" do
      expect(plsql.test_uppercase(p_string: "xxx")).to eq("XXX")
    end

    it "should raise error if wrong number of arguments is passed" do
      expect { plsql.test_uppercase("xxx", "yyy") }.to raise_error(ArgumentError)
    end

    it "should raise error if wrong named argument is passed" do
      expect { plsql.test_uppercase(p_string2: "xxx") }.to raise_error(ArgumentError)
    end

    it "should execute function with schema name specified" do
      expect(plsql.hr.test_uppercase("xxx")).to eq("XXX")
    end

    it "should process nil parameter as NULL" do
      expect(plsql.test_uppercase(nil)).to be_nil
    end

  end

  ["VARCHAR", "VARCHAR2"].each do |datatype|
    describe "Function with #{datatype} parameters" do
      it_should_behave_like "Function with string parameters", datatype
    end
  end

  shared_examples "Function with numeric" do |ora_data_type, class_, num1, num2, expected, mandatory|
    before(:all) do
      plsql.connect! CONNECTION_PARAMS
      plsql.execute <<-SQL
        CREATE OR REPLACE FUNCTION test_sum
          ( p_num1 #{ora_data_type}, p_num2 #{ora_data_type} )
          RETURN #{ora_data_type}
        IS
        BEGIN
          RETURN p_num1 + p_num2;
        END test_sum;
      SQL
    end

    after(:all) do
      plsql.execute "DROP FUNCTION test_sum"
      plsql.logoff
    end

    it "should get #{ora_data_type} variable type mapped to #{class_.to_s}" do
      expect(plsql.test_sum(num1, num2)).to be_a class_
    end
    it "should process input parameters and return correct result" do
      expect(plsql.test_sum(num1, num2)).to eq(expected)
    end

    it "should process nil parameter as NULL" do
      expect(plsql.test_sum(num1, nil)).to be_nil
    end unless mandatory

  end

  @big_number = ("1234567890" * 3).to_i
  [
      { ora_data_type: "INTEGER",       class: Integer,    num1: @big_number, num2: @big_number, expected: @big_number * 2 },
      { ora_data_type: "NUMBER",        class: BigDecimal, num1: 12345.12345, num2: 12345.12345, expected: 24690.2469   },
      { ora_data_type: "PLS_INTEGER",   class: Integer,    num1: 123456789,   num2: 123456789,   expected: 246913578    },
      { ora_data_type: "BINARY_INTEGER", class: Integer,    num1: 123456789,   num2: 123456789,   expected: 246913578    },
      { ora_data_type: "SIMPLE_INTEGER", class: Integer,    num1: 123456789,   num2: 123456789,   expected: 246913578, mandatory: true },
      { ora_data_type: "NATURAL",       class: Integer,    num1: 123456789,   num2: 123456789,   expected: 246913578    },
      { ora_data_type: "NATURALN",      class: Integer,    num1: 123456789,   num2: 123456789,   expected: 246913578, mandatory: true },
      { ora_data_type: "POSITIVE",      class: Integer,    num1: 123456789,   num2: 123456789,   expected: 246913578    },
      { ora_data_type: "POSITIVEN",     class: Integer,    num1: 123456789,   num2: 123456789,   expected: 246913578, mandatory: true },
      { ora_data_type: "SIGNTYPE",      class: Integer,    num1: 1,           num2: -1,          expected: 0            },
  ].each do |row|
    ora_data_type, class_, num1, num2, expected, mandatory = row.values
    describe ora_data_type do
      include_examples "Function with numeric", ora_data_type, class_, num1, num2, expected, mandatory
    end
  end

  describe "Boolean to NUMBER conversion" do
    before(:all) do
      plsql.connect! CONNECTION_PARAMS
      plsql.execute <<-SQL
        CREATE OR REPLACE FUNCTION test_num ( p_num NUMBER) RETURN NUMBER
        IS
        BEGIN
          RETURN p_num;
        END test_num;
      SQL
    end

    after(:all) do
      plsql.execute "DROP FUNCTION test_num"
      plsql.logoff
    end
    it "should convert true value to 1 for NUMBER parameter" do
      expect(plsql.test_num(true)).to eq(1)
    end

    it "should convert false value to 0 for NUMBER parameter" do
      expect(plsql.test_num(false)).to eq(0)
    end
  end

  describe "Function with date parameters" do

    before(:all) do
      plsql.connect! CONNECTION_PARAMS
      plsql.execute <<-SQL
        CREATE OR REPLACE FUNCTION test_date
          ( p_date DATE )
          RETURN DATE
        IS
        BEGIN
          RETURN p_date + 1;
        END test_date;
      SQL
    end

    before(:each) do
      plsql.default_timezone = :local
    end

    after(:all) do
      plsql.execute "DROP FUNCTION test_date"
      plsql.logoff
    end

    it "should process Time parameters" do
      now = Time.local(2008, 8, 12, 14, 28, 0)
      expect(plsql.test_date(now)).to eq(now + 60 * 60 * 24)
    end

    it "should process UTC Time parameters" do
      plsql.default_timezone = :utc
      now = Time.utc(2008, 8, 12, 14, 28, 0)
      expect(plsql.test_date(now)).to eq(now + 60 * 60 * 24)
    end

    it "should process DateTime parameters" do
      now = DateTime.parse(Time.local(2008, 8, 12, 14, 28, 0).iso8601)
      result = plsql.test_date(now)
      expect(result.class).to eq(Time)
      expect(result).to eq(Time.parse((now + 1).strftime("%c")))
    end

    it "should process old DateTime parameters" do
      now = DateTime.civil(1901, 1, 1, 12, 0, 0, plsql.local_timezone_offset)
      result = plsql.test_date(now)
      expect(result.class).to eq(Time)
      expect(result).to eq(Time.parse((now + 1).strftime("%c")))
    end

    it "should process Date parameters" do
      now = Date.new(2008, 8, 12)
      result = plsql.test_date(now)
      expect(result.class).to eq(Time)
      expect(result).to eq(Time.parse((now + 1).strftime("%c")))
    end

    it "should process old Date parameters" do
      now = Date.new(1901, 1, 1)
      result = plsql.test_date(now)
      expect(result.class).to eq(Time)
      expect(result).to eq(Time.parse((now + 1).strftime("%c")))
    end

    it "should process nil date parameter as NULL" do
      expect(plsql.test_date(nil)).to be_nil
    end

  end

  describe "Function with timestamp parameters" do
    before(:all) do
      plsql.connect! CONNECTION_PARAMS
      plsql.execute <<-SQL
        CREATE OR REPLACE FUNCTION test_timestamp
          ( p_time TIMESTAMP )
          RETURN TIMESTAMP
        IS
        BEGIN
          RETURN p_time + NUMTODSINTERVAL(1, 'DAY');
        END test_timestamp;
      SQL
    end

    after(:all) do
      plsql.execute "DROP FUNCTION test_timestamp"
      plsql.logoff
    end

    it "should process timestamp parameters" do
      # now = Time.now
      now = Time.local(2008, 8, 12, 14, 28, 0)
      expect(plsql.test_timestamp(now)).to eq(now + 60 * 60 * 24)
    end

  end

  describe "Function or procedure with XMLType parameters" do
    before(:all) do
      plsql.connect! CONNECTION_PARAMS
      plsql.execute <<-SQL
        CREATE OR REPLACE FUNCTION test_xmltype
          ( p_xml XMLTYPE )
          RETURN XMLTYPE
        IS
        BEGIN
          RETURN p_xml;
        END test_xmltype;
      SQL
      plsql.execute <<-SQL
        CREATE OR REPLACE PROCEDURE test_xmltype2
          ( p_xml XMLTYPE, po_xml OUT XMLTYPE )
        IS
        BEGIN
          po_xml := p_xml;
        END test_xmltype2;
      SQL
    end

    after(:all) do
      plsql.execute "DROP FUNCTION test_xmltype"
      plsql.execute "DROP PROCEDURE test_xmltype2"
      plsql.logoff
    end
<commit_msg>Skip XMLType specs if Oracle Database version is 12c or higher<commit_after># encoding: utf-8

require "spec_helper"

describe "Parameter type mapping /" do

  shared_examples "Function with string parameters" do |datatype|
    before(:all) do
      plsql.connect! CONNECTION_PARAMS
      plsql.execute <<-SQL
        CREATE OR REPLACE FUNCTION test_uppercase
          ( p_string #{datatype} )
          RETURN #{datatype}
        IS
        BEGIN
          RETURN UPPER(p_string);
        END test_uppercase;
      SQL
    end

    after(:all) do
      plsql.execute "DROP FUNCTION test_uppercase"
      plsql.logoff
    end

    it "should find existing procedure" do
      expect(PLSQL::Procedure.find(plsql, :test_uppercase)).not_to be_nil
    end

    it "should not find nonexisting procedure" do
      expect(PLSQL::Procedure.find(plsql, :qwerty123456)).to be_nil
    end

    it "should execute function and return correct value" do
      expect(plsql.test_uppercase("xxx")).to eq("XXX")
    end

    it "should execute function with named parameters and return correct value" do
      expect(plsql.test_uppercase(p_string: "xxx")).to eq("XXX")
    end

    it "should raise error if wrong number of arguments is passed" do
      expect { plsql.test_uppercase("xxx", "yyy") }.to raise_error(ArgumentError)
    end

    it "should raise error if wrong named argument is passed" do
      expect { plsql.test_uppercase(p_string2: "xxx") }.to raise_error(ArgumentError)
    end

    it "should execute function with schema name specified" do
      expect(plsql.hr.test_uppercase("xxx")).to eq("XXX")
    end

    it "should process nil parameter as NULL" do
      expect(plsql.test_uppercase(nil)).to be_nil
    end

  end

  ["VARCHAR", "VARCHAR2"].each do |datatype|
    describe "Function with #{datatype} parameters" do
      it_should_behave_like "Function with string parameters", datatype
    end
  end

  shared_examples "Function with numeric" do |ora_data_type, class_, num1, num2, expected, mandatory|
    before(:all) do
      plsql.connect! CONNECTION_PARAMS
      plsql.execute <<-SQL
        CREATE OR REPLACE FUNCTION test_sum
          ( p_num1 #{ora_data_type}, p_num2 #{ora_data_type} )
          RETURN #{ora_data_type}
        IS
        BEGIN
          RETURN p_num1 + p_num2;
        END test_sum;
      SQL
    end

    after(:all) do
      plsql.execute "DROP FUNCTION test_sum"
      plsql.logoff
    end

    it "should get #{ora_data_type} variable type mapped to #{class_.to_s}" do
      expect(plsql.test_sum(num1, num2)).to be_a class_
    end
    it "should process input parameters and return correct result" do
      expect(plsql.test_sum(num1, num2)).to eq(expected)
    end

    it "should process nil parameter as NULL" do
      expect(plsql.test_sum(num1, nil)).to be_nil
    end unless mandatory

  end

  @big_number = ("1234567890" * 3).to_i
  [
      { ora_data_type: "INTEGER",       class: Integer,    num1: @big_number, num2: @big_number, expected: @big_number * 2 },
      { ora_data_type: "NUMBER",        class: BigDecimal, num1: 12345.12345, num2: 12345.12345, expected: 24690.2469   },
      { ora_data_type: "PLS_INTEGER",   class: Integer,    num1: 123456789,   num2: 123456789,   expected: 246913578    },
      { ora_data_type: "BINARY_INTEGER", class: Integer,    num1: 123456789,   num2: 123456789,   expected: 246913578    },
      { ora_data_type: "SIMPLE_INTEGER", class: Integer,    num1: 123456789,   num2: 123456789,   expected: 246913578, mandatory: true },
      { ora_data_type: "NATURAL",       class: Integer,    num1: 123456789,   num2: 123456789,   expected: 246913578    },
      { ora_data_type: "NATURALN",      class: Integer,    num1: 123456789,   num2: 123456789,   expected: 246913578, mandatory: true },
      { ora_data_type: "POSITIVE",      class: Integer,    num1: 123456789,   num2: 123456789,   expected: 246913578    },
      { ora_data_type: "POSITIVEN",     class: Integer,    num1: 123456789,   num2: 123456789,   expected: 246913578, mandatory: true },
      { ora_data_type: "SIGNTYPE",      class: Integer,    num1: 1,           num2: -1,          expected: 0            },
  ].each do |row|
    ora_data_type, class_, num1, num2, expected, mandatory = row.values
    describe ora_data_type do
      include_examples "Function with numeric", ora_data_type, class_, num1, num2, expected, mandatory
    end
  end

  describe "Boolean to NUMBER conversion" do
    before(:all) do
      plsql.connect! CONNECTION_PARAMS
      plsql.execute <<-SQL
        CREATE OR REPLACE FUNCTION test_num ( p_num NUMBER) RETURN NUMBER
        IS
        BEGIN
          RETURN p_num;
        END test_num;
      SQL
    end

    after(:all) do
      plsql.execute "DROP FUNCTION test_num"
      plsql.logoff
    end
    it "should convert true value to 1 for NUMBER parameter" do
      expect(plsql.test_num(true)).to eq(1)
    end

    it "should convert false value to 0 for NUMBER parameter" do
      expect(plsql.test_num(false)).to eq(0)
    end
  end

  describe "Function with date parameters" do

    before(:all) do
      plsql.connect! CONNECTION_PARAMS
      plsql.execute <<-SQL
        CREATE OR REPLACE FUNCTION test_date
          ( p_date DATE )
          RETURN DATE
        IS
        BEGIN
          RETURN p_date + 1;
        END test_date;
      SQL
    end

    before(:each) do
      plsql.default_timezone = :local
    end

    after(:all) do
      plsql.execute "DROP FUNCTION test_date"
      plsql.logoff
    end

    it "should process Time parameters" do
      now = Time.local(2008, 8, 12, 14, 28, 0)
      expect(plsql.test_date(now)).to eq(now + 60 * 60 * 24)
    end

    it "should process UTC Time parameters" do
      plsql.default_timezone = :utc
      now = Time.utc(2008, 8, 12, 14, 28, 0)
      expect(plsql.test_date(now)).to eq(now + 60 * 60 * 24)
    end

    it "should process DateTime parameters" do
      now = DateTime.parse(Time.local(2008, 8, 12, 14, 28, 0).iso8601)
      result = plsql.test_date(now)
      expect(result.class).to eq(Time)
      expect(result).to eq(Time.parse((now + 1).strftime("%c")))
    end

    it "should process old DateTime parameters" do
      now = DateTime.civil(1901, 1, 1, 12, 0, 0, plsql.local_timezone_offset)
      result = plsql.test_date(now)
      expect(result.class).to eq(Time)
      expect(result).to eq(Time.parse((now + 1).strftime("%c")))
    end

    it "should process Date parameters" do
      now = Date.new(2008, 8, 12)
      result = plsql.test_date(now)
      expect(result.class).to eq(Time)
      expect(result).to eq(Time.parse((now + 1).strftime("%c")))
    end

    it "should process old Date parameters" do
      now = Date.new(1901, 1, 1)
      result = plsql.test_date(now)
      expect(result.class).to eq(Time)
      expect(result).to eq(Time.parse((now + 1).strftime("%c")))
    end

    it "should process nil date parameter as NULL" do
      expect(plsql.test_date(nil)).to be_nil
    end

  end

  describe "Function with timestamp parameters" do
    before(:all) do
      plsql.connect! CONNECTION_PARAMS
      plsql.execute <<-SQL
        CREATE OR REPLACE FUNCTION test_timestamp
          ( p_time TIMESTAMP )
          RETURN TIMESTAMP
        IS
        BEGIN
          RETURN p_time + NUMTODSINTERVAL(1, 'DAY');
        END test_timestamp;
      SQL
    end

    after(:all) do
      plsql.execute "DROP FUNCTION test_timestamp"
      plsql.logoff
    end

    it "should process timestamp parameters" do
      # now = Time.now
      now = Time.local(2008, 8, 12, 14, 28, 0)
      expect(plsql.test_timestamp(now)).to eq(now + 60 * 60 * 24)
    end

  end

  describe "Function or procedure with XMLType parameters" do
    before(:all) do
      plsql.connect! CONNECTION_PARAMS
      @oracle12c_or_higher = !! plsql.connection.select_all(
      "select * from product_component_version where product like 'Oracle%' and to_number(substr(version,1,2)) >= 12")
      skip "Skip until furtuer investigation for #114" if @oracle12c_or_higher
      plsql.execute <<-SQL
        CREATE OR REPLACE FUNCTION test_xmltype
          ( p_xml XMLTYPE )
          RETURN XMLTYPE
        IS
        BEGIN
          RETURN p_xml;
        END test_xmltype;
      SQL
      plsql.execute <<-SQL
        CREATE OR REPLACE PROCEDURE test_xmltype2
          ( p_xml XMLTYPE, po_xml OUT XMLTYPE )
        IS
        BEGIN
          po_xml := p_xml;
        END test_xmltype2;
      SQL
    end

    after(:all) do
      plsql.execute "DROP FUNCTION test_xmltype" unless @oracle12c_or_higher
      plsql.execute "DROP PROCEDURE test_xmltype2" unless @oracle12c_or_higher
      plsql.logoff
    end

{'fraction_non_alphanumeric': 0.09464390818128311, 'fraction_numerical': 0.046085932901706886, 'mean_word_length': 2.542743953294412, 'pattern_counts': {'":': 0, '<': 31, '<?xml version=': 0, '>': 4, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 24}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '4911376', 'n_tokens_mistral': 6217, 'n_tokens_neox': 5447, 'n_words': 1565}